CODEWITHSUNDEEP
mysqlsqlleft-joinisnull
Renjith R
Renjith R

Reputation: 891

mysql left join query with is null

I have the following tables and data

Table D
d_id
-----------
1   
2   
3

Table V
v_id            d_id        
----------- -----------
1           1
2           2

I am getting d_id which is not used in Table V and my query as follows

SELECT distinct(d.d_id) AS d
     , v.v_id  AS v
FROM
        D  LEFT  JOIN  V
  ON   ( d.d_id     =    v.v_id  )
where   v.v_id IS  NULL

d_id
-------------
3

When i pass the value(eg : d.d_id = 2 ) to the query, i want a result in the following format

d_id
-------------
    2
    3

Upvotes: 0

Views: 99

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35553

distinct(d.d_id)

select distinct is NOT a function, and while use of those parentheses will not cause a syntax error they have absolutely no influence on the behaviour of select distinct; so ALL columns will be evaluated to produce a distinct set of values.

SELECT /* DISTINCT is this really needed? Don't use it unless needed */
       d.d_id AS d
     , v.v_id AS v
FROM D
LEFT JOIN V ON (d.d_id = v.v_id)
WHERE v.v_id IS NULL
OR d.d_id = 1

-- OR v.d_id = 1 -- not clear on which d_id you want to pass

Upvotes: 1

Related Questions