phpmyadmin Question posted in
The official documentation can be found here.

how to substract sql tables (but keep 'null' ) – Phpmyadmin

have a table where i run two commands with this:

SELECT DISTINCT(DésignationBDC2) FROM pdc where BDC3 like '%01|06|02%'
SELECT DISTINCT(DésignationBDC2) FROM pdc where Id=60

then i get this:

DésignationBDC2
018 Rose
047 Noir
507 Poudre
545 Rouge
556 Navy
F72 Caramel
280 Saphir

and this 

DésignationBDC2
047 Noir
545 Rouge
556 Navy
F72 Caramel

i’d like to substract these tables to get this:

DésignationBDC2
null
047 Noir
null
545 Rouge
556 Navy
F72 Caramel
null

how can i do ?
thanks

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    using the method you proposed, i tried to join those two tables like this:

    select 
    T2.* 
from 
    (SELECT DISTINCT DésignationBDC2 FROM pdc2 where BDC3 like '%01|06|02%') AS T1
    LEFT JOIN (select DésignationBDC2, COULEUR from pdc2 where Id=60) AS T2 ON T1.DésignationBDC2 = T2.DésignationBDC2

    these tables are : 1 and 2

    however, i get this :3 and as you can see, the order of "DésignationBDC2" is lost from the first table. How can i avoid this ?


  2. 0

    You can achieve it using the left join

    select 
    T2.* 
from 
    (SELECT DISTINCT DesignationBDC2 FROM pdc where BDC3 like '%01|06|02%') AS T1
    LEFT JOIN (SELECT DISTINCT DesignationBDC2 FROM pdc where Id=60) AS T2 ON T1.DesignationBDC2 = T2.DesignationBDC2
    Login or Signup to reply.
  3. 0

    I recommend aggregation:

    select (case when id = 60 then null else DésignationBDC2 end)
from pdc
where DésignationBDC2 like '%01|06|02%'
group by DésignationBDC2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search