PRABA PRABA -4 years ago 88
SQL Question

select query with where clause and group by in ms access query

I'm having table which contains columns Id and Pcode. where ID column is foreign key. I'm trying to get the ID, which Pcode does not contains the value 'FT' with group by 'ID'.

Sample Data:

enter image description here

Desired Result:

ID

3

the desired result is 3. the ID column 3 does not have the Pcode FT. Can anyone help me to write a query to get the desired result. Thanks in Advance.

Answer Source

One option would be to use aggregation:

SELECT ID
FROM yourTable
GROUP BY ID
HAVING SUM(IIF(PCODE = 'FA', 1, 0)) = 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download