CODEWITHSUNDEEP
mysqlsql
JaGaurav
JaGaurav

Reputation: 25

find records in a table which is not associated in to other records in different table

I have 3 tables :

list_routes
pk_route_id  route_code  route_name route_description
3              J1          IND           IND-DPS
4              J4          ADT           ADT_DPS

tbl_stu_route
pk_stu_route_id    fk_stu_cls_id   fk_route_id
2                    13               3

tbl_stu_class
pk_stu_cls_id  fk_stu_id  
 13                56

Now what I want to achieve is write query in MYSQL that it will fetch me the records from list_routes which is not associated or attached in tbl_stu_class so for e.g in the above scenario it should give me the output :

pk_route_id  route_code  route_name route_description
4              J4          ADT           ADT_DPS

I wrote the below query using left outer join as

SELECT
    a.pk_route_id,
    a.route_code,
    a.route_name,
    a.route_description
FROM
    list_routes a
left outer join tbl_stu_route b on a.pk_route_id=b.fk_route_id
left outer join tbl_stu_class c on b.fk_stu_cls_id=c.pk_stu_cls_id
where c.fk_stu_id ='56'

but the output was 

pk_route_id  route_code  route_name route_description
3              J1          IND           IND-DPS

I am not sure where I am going wrong. Can you please enlighten me how to acieve this ?

Upvotes: 1

Views: 30

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Given the nature of your question, I would use not exists:

SELECT lr.*
FROM list_routes lr
WHERE NOT EXISTS (SELECT 1
                  FROM tbl_stu_route sr JOIN
                       tbl_stu_class c 
                       ON sr.fk_stu_cls_id = c.pk_stu_cls_id
                  WHERE lr.pk_route_id = sr.fk_route_id AND
                        c.fk_stu_id = 56
                 );

Notes:

  • When using table aliases, use abbreviations for the table names. Abbreviations make the query much easier to follow than random letters.
  • Do not use single quotes for numeric constants. I assume that your ids are numbers, hence 56 rather than '56'.
  • I find that NOT EXISTS is a better fit to your problem description than LEFT JOIN, although both work.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If you want to get the not exists record then just filter out with IS NULL

SELECT a.*
FROM list_routes a
left outer join tbl_stu_route b on a.pk_route_id=b.fk_route_id
left outer join tbl_stu_class c on b.fk_stu_cls_id=c.pk_stu_cls_id
where b.fk_route_id is null;

Upvotes: 2

Related Questions