CODEWITHSUNDEEP
mysqljoinsubqueryleft-join
Lricsi
Lricsi

Reputation: 36

LEFT JOIN to show NULL rows + WHERE

I have two tables, one which contains unique names (bill_datatypes), and another one which expresses that these names are connected to some subcategories (one name can be connected to more categories as well).

The first table (relevant part):
The first table (relevant part)

The second table (relevant part):
The second table (relevant part)

I would like to select all the names which are not connected to a specific subcategory yet. This means that the names might be mentioned in the connecting table but connected to a different subcategory.

I have solved the problem with the following subquery:

SELECT T1.name
FROM bill_datatype T1
WHERE NOT EXISTS (

SELECT T2.bill_datatype_id
FROM obligatory_field T2
WHERE T2.bill_datatype_id = T1.id AND T2.bill_sub_category_id = 1

)

This solution seems to work well, but this way I can not have any data from the second table, which might be needed in the future.

Do you have any suggestions on how to solve the problem with a LEFT JOIN? (The WHERE clause on the subcategory made it too challenging for me.)

Thank you for your help also in advance!

Upvotes: 2

Views: 57

Answers (2)

Nick
Nick

Reputation: 147246

From how I understand your question, I think this query will achieve what you want:

SELECT T1.name
FROM bill_datatype T1
LEFT JOIN obligatory_field T2
ON T2.bill_datatype_id = T1.id AND T2.bill_sub_category_id = 1
WHERE T2.bill_datatype_id IS NULL

The WHERE condition on the JOIN'ed table will give you only the names from T1 which have no bill_sub_category_id or their bill_sub_category_id is not 1.

SQLFiddle

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

SELECT distinct T1.name
FROM 
bill_datatype T1 
right join
(

SELECT T2.bill_datatype_id
FROM obligatory_field T2
WHERE  T2.bill_sub_category_id = 1

) 
as T3
on T1.id != T3.bill_datatype_id

http://sqlfiddle.com/#!9/d4f616/18

Upvotes: 0

Related Questions