CODEWITHSUNDEEP
mysql
SK2017
SK2017

Reputation: 773

SQL Select data from second table if it doesn't exist in the first table

I am trying to maniuplate the JSON data for a stacked bar chart in NVD3, it required a key to be 0 if it doesn't exist for it to function properly.

I have used the following SQL query to get the data for TABLE1

select POSITION, DOE, ID, COUNT(ID) AS COUNTER FROM TABLE1 WHERE
   DOE BETWEEN '2014-12-02 00:00:01' AND '2014-12-02 23:59:59' AND TYPE = 'P' AND 
   POSITION LIKE 'POS%'

TABLE1

POSITION      DOE           ID  COUNTER
POS1      02/12/2014 07:02  12  49
POS2      02/12/2014 07:17  104 17
POS3      02/12/2014 07:07  34  34
POS4      02/12/2014 07:07  21  54
POS5      02/12/2014 07:09  10  51
POS6      02/12/2014 08:42  3   24
POS7      02/12/2014 07:07  10  51
POS8      02/12/2014 07:01  41  46
POS9      02/12/2014 07:08  24  40

I created another table called TABLE2 with the columns POSITION and COUNT and made the count 0.

Here is the contents of TABLE2

POSITION COUNTER
    POS1    0
    POS2    0
    POS3    0
    POS4    0
    POS5    0
    POS6    0
    POS7    0
    POS8    0
    POS9    0

What i need to do is if the position POS1 doesn't exist in TABLE1 then show position POS1 and counter 0 from TABLE2.

So consequently I would get a table like this - 

POSITION      DOE           ID  COUNTER
POS1                            0
POS2      02/12/2014 07:17  104 17
POS3      02/12/2014 07:07  34  34
POS4      02/12/2014 07:07  21  54
POS5      02/12/2014 07:09  10  51
POS6      02/12/2014 08:42  3   24
POS7      02/12/2014 07:07  10  51
POS8      02/12/2014 07:01  41  46
POS9      02/12/2014 07:08  24  40

I am brand new to SQL, So I don't even know if this is possible, I have searched and searched and can't find a solution.

Any advice will be greatly appreciated.

Upvotes: 0

Views: 1125

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I think you want a left outer join:

select coalesce(t1.position, t2.position) as position,
       t1.DOE, t1.ID, coalesce(t1.COUNTER, t2.COUNTER) as COUNTER
from table2 t2 left join
     (select POSITION, DOE, ID, COUNT(ID) AS COUNTER
      FROM TABLE1
      WHERE DOE BETWEEN '2014-12-02 00:00:01' AND '2014-12-02 23:59:59' AND TYPE = 'P' AND 
            POSITION LIKE 'POS%'
     ) t1
     on t2.position = t1.position;

Upvotes: 1

Related Questions