SQL Selecting Rows Where One Column's Value Is Common Across Another Criteria Column

SQL selecting rows where one column's value is common across another criteria column

I assume that the natrual key of this table is document_id + subject_id, and that id is a surrogate; IOW, document_id and subject_id are unique. As such, I'm just going to pretend it doesn't exist and that a unique constraint is on the natural key.

Let's start with the obvious.

SELECT document_id, subject_id
  FROM document_subjects
 WHERE subject_id IN (17,76)

That gets you everything you want plus stuff you don't want. So all we need to do is filter out the other stuff. The "other stuff" is groups of rows having a count that is not equal to the count of the desired subjects.

SELECT document_id
  FROM document_subjects
 WHERE subject_id IN (17,76)
 GROUP BY document_id
HAVING COUNT(*) = 2

Note that subject_id is removed because it doesn't participate in grouping. Taking this one step further, i'm going to add an imaginary table called subjects_i_want that contains N rows of subjects you want.

SELECT document_id
  FROM document_subjects
 WHERE subject_id IN (SELECT subject_id FROM subjects_i_want)
 GROUP BY document_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM subjects_i_want)

Obviously subjects_i_want could be swapped out for another subquery, temporary table, or whatever. But, once you have this list of document_id, you can use it within a subselect of a bigger query.

SELECT document_id, subject_id, ...
  FROM document_subjects
 WHERE document_id IN(
        SELECT document_id
          FROM document_subjects
          WHERE subject_id IN (SELECT subject_id FROM subjects_i_want)
          GROUP BY document_id
         HAVING COUNT(*) = (SELECT COUNT(*) FROM subjects_i_want))

Or whatever.

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Select rows having 2 columns equal value

Question 1 query:

SELECT ta.C1
      ,ta.C2
      ,ta.C3
      ,ta.C4
FROM [TableA] ta
WHERE (SELECT COUNT(*)
       FROM [TableA] ta2
       WHERE ta.C2=ta2.C2
       AND ta.C3=ta2.C3
       AND ta.C4=ta2.C4)>1

How can I get specific rows in a table by the value of a specific queried column of a different table?

If I understood your question correctly then you can try this

SELECT * FROM table2 WHERE IC = (SELECT IC FROM table1 WHERE B='B1')

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _:

SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list

(assuming contact_id, flag is unique).

Or use joins:

SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'

If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

Select from one table matching criteria in another?

The simplest solution would be a correlated sub select:

select
    A.*
from
    table_A A
where
    A.id in (
        select B.id from table_B B where B.tag = 'chair'
)

Alternatively you could join the tables and filter the rows you want:

select
    A.*
from
    table_A A
inner join table_B B
    on A.id = B.id
where
    B.tag = 'chair'

You should profile both and see which is faster on your dataset.

Select column from another table based on matching condition

I would just use a correlated subquery:

select t1.*,
       (select t2.limit
        from table2 t2
        where t2.date <= t1.date
        order by t2.date desc
        limit 1
       ) as Limit_At_Time
from table1 t1;

Usually in these types of problems, the comparison is <= rather than <, so I used that. Of course, the exact equivalent to your query is <.

---

Related Topics