mysql left join query with is null

Question

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

Please, provide sample with full specification of passed data and desired result. For now it's unclear what is "that data" you need. — Alma Do
– Alma Do, Commented Oct 9, 2013 at 9:02

Paul Maxwell · Accepted Answer · 2013-10-09 09:10:07Z

1

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

answered Oct 9, 2013 at 9:10

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql left join query with is null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related