How can I select distinct IDs of table that has not specific value in its sub table?

Question

I have two tables with 1-n relationship. One of them is auto services table and the other one has their specifications like;

 ID ServiceId Name --------------------------- 1 FJ12 Fletcher Jones 2 HS35 Hardy's 3 YK65 Yorker SpecialityID ServiceID Name --------------------------------- 1 FJ12 AUTH 2 FJ12 PRIV 3 FJ12 GRS 4 HS35 PRIV 5 HS35 AUTH 6 HS35 CRS 7 YK65 PRIV 8 HS35 GRS

I tried with some left outer join queries and where clauses but I couldn't handle. How can I get all auto services from first table which doesn't have 'AUTH' specification in second table ? (second table is first table's sub table)

Why have you tagged 2 different RDBMS? Please only tag the RDBMS you are actually using. — Thom A
– Thom A ♦, Commented Feb 16, 2019 at 13:17
Could you also post your attempts please? You say you've tried, so it's helpful to the volunteers if you provide us with those attempts. Expected results would also be great (but WHERE NOT EXISTS, as Dan said, or a LEFT JOIN seem to be the right solutions). Thanks. — Thom A
– Thom A ♦, Commented Feb 16, 2019 at 13:18

Gordon Linoff · Accepted Answer · 2019-02-16 13:28:19Z

Use NOT EXISTS:

select aus.* from auto_services aus where not exists (select 1 from specifications s where s.serviceId = aus.serviceId and s.name = 'AUTH' );

For performance, you want in index on specifications(serviceId, name).

Vijiy · Accepted Answer · 2019-02-16 13:30:00Z

Below would be the query using joins -

SELECT F1.ServiceId, F2.Name from table1 t1 LEFT OUTER JOIN table2 t2 ON t1.serviceid=t2.serviceid WHERE t2.NAME <> 'AUTH';

using Exists

SELECT t1.serviceid, t2.NAME FROM table1 t1 WHERE EXISTS (SELECT serviceid, NAME FROM table2 t2 WHERE t1.serviceid = t2.serviceid AND t2.NAME <> 'AUTH')

Hope this helps

forpas · Accepted Answer · 2019-02-16 13:43:41Z

This query:

select distinct serviceid from specialities where name = 'AUTH'

returns all the distinct serviceids from the table specialities that have 'AUTH' in the name column.
So all you have is to exclude these serviceids from the auto_services table,
with a left join like this:

select * from auto_services a left join ( select distinct serviceid from specialities where name = 'AUTH' ) s on s.serviceid = a.serviceid where s.serviceid is null

or with not in:

select * from auto_services where serviceid not in ( select distinct serviceid from specialities where name = 'AUTH' )

Collectives™ on Stack Overflow

How can I select distinct IDs of table that has not specific value in its sub table?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related