CODEWITHSUNDEEP
mysqlsql
user3785636
user3785636

Reputation: 9

MySQL JOIN table with return NULL or 0

I am trying to create JOIN some tables. The idea is to display the selected 0 0r 1.name for the same name.if not value should be NULL or 0 else 1.

Sample Data

TABLE 1

ID   NAME
------------
1    Zebra

2    Monkey

3    Lion

4    Rabbit

TABLE 2

ID  ID_table1  Name_table1
--------------------------
1       1        ZEBRA   
2       3        Lion   
3       3        Lion
4       1        Zebra
5       2        Monkey
6       3        Lion  
7       4        Rabbit

if i join table and select value from TABLE 2 where Id is 2

RESULT

ID    NAME   Selected
--------------------- 
1    Zebra      0
2    Monkey     0
3    Lion       1  
4    Rabbit     0

Upvotes: 0

Views: 92

Answers (3)

John Ruddell
John Ruddell

Reputation: 25842

to what i was referring in my comment you should just use a COUNT()

SELECT t.ID, t.NAME, COUNT(t1.id_table1)
FROM Table1 t
LEFT JOIN
(   SELECT ID_table1 
    FROM Table2
    WHERE ID=2
)t1 ON t.ID = t1.ID_table1
GROUP BY t.id

DEMO

Upvotes: 0

radar
radar

Reputation: 13425

Need to use LEFT JOIN and CASE

SELECT T1.ID, NAME , case when T2.ID_Table1 is null then 0 else 1 end as value
FROM
Table1 T1
LEFT JOIN( select ID_table1 
  FROM Table2
  where ID =2
 ) T2
on T1.ID = T2.ID_table1

Upvotes: 1

Rashmi Pandit
Rashmi Pandit

Reputation: 23798

You don't need to join the two tables.

You can do this using only the Table_2

DECLARE @selected INT
SET @selected = 2
SELECT DISTINCT
        id_table1 AS ID ,
        name_table1 AS Name ,
        SUM(CASE WHEN ID = @selected THEN 1
                 ELSE 0
            END) AS selected
FROM    Table_2
GROUP BY ID_Table1 ,
        name_table1
ORDER BY id_table1

Result:

ID  Name    selected
1   ZEBRA   0
2   Monkey  0
3   Lion    1
4   Rabbit  0

Upvotes: 0

Related Questions