Left Join with Find in set

Question

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    |
+-------------+---------+

score 1 · Answer 1 · answered Dec 25 '13 at 08:25

1

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

answered Dec 25 '13 at 08:25

Riad

3,822
5
28
39

Dear Riad, there is no `CHECKED` column in the database. I would like to compare field values then put check value. – Oğuz Çelikdemir Dec 25 '13 at 09:23
i know. 'checked' is not a column but a fixed value. it will be null for the third row of joining table. – Riad Dec 25 '13 at 09:35

score 1 · Answer 2 · answered Dec 25 '13 at 08:25

1

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

answered Dec 25 '13 at 08:25

valex

23,966
7
43
60

There is a bir mistake! I tried over `sqlfiddle` which is working properly. But, when I tried on my server didn't work. MySQL server version is 5.5.34. Do you have any idea? – Oğuz Çelikdemir Dec 25 '13 at 09:17

score 1 · Answer 3 · answered Dec 25 '13 at 08:33

1

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

answered Dec 25 '13 at 08:33

Arthur Halma

3,943
3
23
44

score 1 · Accepted Answer · answered Dec 25 '13 at 09:46

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)

score 0 · Answer 5 · answered Dec 25 '13 at 10:11

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)

Left Join with Find in set

5 Answers5