Sql: How to Select a Single Id ("Row") That Meets Multiple Criteria from a Single Column

SQL: how to select a single id that meets multiple criteria from multiple rows

You need to group by the package_id and then use having to perform an aggregate function over the grouped data

select package_id
from package_content
where content_number = 22
or
(
    content_number = 11 and content_quality = 1
)
group by package_id
having count(distinct content_number) = 2

SQL - Return all rows for ID where one row meets condition A,B,or C

You can use a subquery and IN for this.

Select *
From YourTable
where ID in (select ID from YourTable where # in ('X','Y','Z'))

Just a note, there is no 12 * 4 * C * in your data but I think it's just a type-o in your results and should be 12 * 4 * Y *

Select single row based on multiple criteria in same column

You can use conditional aggregation in order to selectively count each of the possible kw values:

 SELECT first, last,
        COUNT(CASE WHEN kw = 'P.E.' THEN 1 END) AS PE,
        COUNT(CASE WHEN kw = 'M E' THEN 1 END) AS ME,
        COUNT(CASE WHEN kw = 'HVAC' THEN 1 END) AS HVAC
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3

Edit:

If you want to filter records based on, say, HVAC occurrences, then you can add the conditional aggregate in HAVING clause, e.g.:

 SELECT first, last            
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3 AND 
        COUNT(CASE WHEN kw = 'HVAC' THEN 1 END) > 5

Edit2:

If you want to filter records based on the value of hits field, then you can use:

 SELECT first, last            
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3 AND 
        COUNT(CASE WHEN hits > 4 THEN 1 END) > 0

NOT EXISTS with multiple criteria and indirect unique id in SQL Server

In your query, the EXISTS is a correlated subquery: in other words, it has one or more outer references to the rest of the query.

Looking just at the relevant parts:

from 
    work wkq
inner join 
    pick pkd......

So wkq and pkd are the outer table references

---

where 
....
    not exists (select 1

What you actually select in an EXISTS query is irrelevant, so 1 is fine, NOT inverts the EXISTS obviously

---

 from t_allocation alc WITH (NOLOCK)

Now alc is the inner reference

---

                    WHERE pkd.wh_id = alc.wh_id 
                AND pkd.pick_id = alc.pick_id

These two lines are correlations between the inside reference and the outside reference, so the EXISTS will only pick out rows that match these conditions

---

                AND pkd.work_q_id = wkq.work_q_id)

This is interesting. It is not a correlation, because there are no inner references, only outer.

It is a startup filter: the EXISTS will only return rows if this outer-reference condition is true

mySQL Join Tables but exclude ALL rows with a certain ID/value based on matching criteria from another column (criteria contained in only some rows)

SELECT s.SampleNo, s.SampleType, s.BatchNo, s.Status, b.Info FROM sample_table s 
INNER JOIN batch_table b on s.BatchNo = b.BatchNo
WHERE s.BatchNo NOT IN (
           SELECT DISTINCT(BatchNo) FROM sample_table WHERE Status != 'pass' AND 
           SampleType IN ('Batch_QC_2', 'Batch_QC_1')
          );

How to do join on multiple criteria, returning all combinations of both criteria

select one.*, two.meal
from table1 as one
left join table2 as two
on (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)

---

Related Topics