CODEWITHSUNDEEP
mysqljoinfind-in-set
Oğuz Çelikdemir
Oğuz Çelikdemir

Reputation: 4980

Left Join with Find in set

I have tables as follows :

TABLE A
+-----+---------------+-------------+
| ID  | DNR_DETAIL_ID | DESCRIPTION |
+-----+---------------+-------------+
|  1  |       1       |   DESC A    |
+-----+---------------+-------------+
|  2  |       2       |   DESC B    |
+-----+---------------+-------------+
|  3  |       3       |   DESC C    |
+-----+---------------+-------------+


TABLE B
+--------+---------------+
| DNR_ID | DNR_DETAIL_ID |
+------------------------+
|   1    |     1,2       |
+--------+---------------+
|   2    |       3       |
+--------+---------------+

As you can see, DNR_DETAIL_ID columns are common in both tables. What I want to do, left joining both tables with field values ( null or not )

THE RESULT SHOULD BE (IF DNR_ID = 1) :
+-------------+---------+
| DESCRIPTION | CHECKED |
+-------------+---------+
|    DESC A   |    1    |
+-------------+---------+
|    DESC B   |    1    |
+-------------+---------+
|    DESC C   |    0    |
+-------------+---------+

Upvotes: 1

Views: 11647

Answers (5)

ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

You can write it many ways, but here is the best way:

SELECT 
   MD_DNR_DETAIL.DNR_DETAIL_DESC as DESCRIPTION,
   CASE WHEN DNR_LIST.DNR_ID IS NOT NULL THEN 1 ELSE 0 END AS CHECKED
FROM MD_DNR_DETAIL 
LEFT JOIN DNR_LIST 
ON FIND_IN_SET(MD_DNR_DETAIL.DNR_DETAILT_ID, DNR_LIST.DNR_DETAIL_ID)

Upvotes: 0

Oğuz Çelikdemir
Oğuz Çelikdemir

Reputation: 4980

Thank you so much guys. I have tried all of your suggestions but none of them work. Interesting thing is that code works well in sqlfiddle ( same schema and values ) but not working in local environment! Here is the query that working in local.

/** 
 * DNR_DETAIL_DESC IS TABLE A
 * DNR_LIST IS TABLE B
 */
SELECT A.DNR_DETAIL_DESC,
   CASE WHEN B.DNR_ID IS NOT NULL THEN 1 ELSE 0 END AS CHECKED
FROM MD_DNR_DETAIL A
LEFT JOIN (SELECT * FROM DNR_LIST WHERE DNR_ID = 1) AS B
   ON FIND_IN_SET(A.DNR_DETAILT_ID, B.DNR_DETAIL_ID)

Upvotes: 1

Arthur Halma
Arthur Halma

Reputation: 4001

SELECT a.DESCRIPTION,
       CASE WHEN b.DNR_ID IS NOT NULL THEN 0 ELSE 1 END as CHECKED
FROM table_a a
LEFT JOIN table_b b
       ON FIND_IN_SET(a.DNR_DETAIL_ID, b.DNR_DETAIL_ID)

Demo on sqlfiddle

Upvotes: 1

valex
valex

Reputation: 24144

Try this using FIND_IN_SET()

SELECT 
A.Description,
CASE WHEN B.DNR_ID IS NOT NULL THEN 1 ELSE 0 END as Checked
FROM A
LEFT JOIN B
ON FIND_IN_SET(A.DNR_DETAIL_ID, B.DNR_DETAIL_ID) 
   AND B.DNR_ID=1

SQLFiddle demo

Upvotes: 1

Riad
Riad

Reputation: 3860

try this:

SELECT TA.description AS DESCRIPTION, CASE WHEN TB.checked IS NOT NULL THEN 1 ELSE 0 END AS CHECKED 
FROM
(
    select distinct description from TableA 
) TA left join 
(
    SELECT description, 'checked' FROM TableA where dnt_detail_id in (
               select         dnr_detail_id from TableB where dnr_id = 1 
         )
)TB ON TB.description = TA.description

Upvotes: 1

Related Questions