CODEWITHSUNDEEP
mysql
PHP Ferrari
PHP Ferrari

Reputation: 15616

Fetch DISTINCT records with JOIN of two tables

I have two tables:

builders

b_id fk_c_id
 1   1
 2   1
 3   1
 4   1
 5   1
 6   2
 7   2

subbuilders

fk_b_id sb_id
   1      2
   1      3
   1      4
   2      5
   6      7

and i want Distinct b_id which are not exist in subbuilders table and must have same fk_c_id

I create:

SELECT DISTINCT b.id FROM pms_builder_to_subbuilders bsb 
LEFT JOIN pms_builders b ON b.id = bsb.sb_id WHERE b.fk_c_id = '1'

but it show Distinct records from subbuilders.

Upvotes: 0

Views: 1737

Answers (2)

John Woo
John Woo

Reputation: 263723

i think you want this query:

SELECT DISTINCT b_ID
FROM builders
WHERE b_ID NOT IN 
    (SELECT DISTINCT fk_b_id FROM subbuilders)

but it returns

 b_ID
 ========
 3
 4
 5
 7

but i didn't understand your statement: must have same fk_c_id. What do you mean by that?

b_id = fk_c_id

if that's the case then there will be no rows returned because only record 1 has the same b_ID and fk_c_id but exists in table subbuilders

Upvotes: 0

Nilesh
Nilesh

Reputation: 1159

You can get the desired results with the following query:

SELECT DISTINCT b.b_id FROM builders b LEFT JOIN subbuilders sb ON sb.fk_b_id = b.b_id WHERE b.fk_c_id = '1' AND ISNULL(fk_b_id);

Upvotes: 1

Related Questions