MySQL : LEFT JOIN not fetching required data

Question

I'm using left join in MySQL as mentioned below:

SELECT Q.QUESTION_ID,
 school_id
FROM Q_MASTER AS Q
LEFT JOIN (
 SELECT keyword,
 school_id
 FROM E_HEADER
 WHERE low_q = 1
 AND school_id = 6
 ) AS X
 ON X.SCHOOL_ID = Q.SCHOOL_ID
WHERE Q.QUESTION_ID IN ( X.KEYWORD ) 

Here Q_MASTER is having question_id as primary key (INT). E_HEADER is having keyword as comma seperated values of question id. (VARCHAR100) Ex: 1,2,3,4

I want to exclude all question id's in select query from Q_MASTER which are present in "KEYWORD" column of E_HEADER

But above mentioned query is only execluding first value in comma seperated string. I tried , NOT EXISTS clause also, but every time only first comma seperated value is getting execluded.

Can you please let me know , how to solve this issue.

Answer 1

Storing a string of comma-separated values in a column is not an ideal design; ideally you would have a table of keywords and a 2-column map table with a row for every (E_HEADER.id,keyword_id).

However, given your current structure, the FIND_IN_SET() function should work, although this will not be the best possible performance, since a function in the where clause that takes a column as its argument will generally prevent the use of indexes for evaluating that condition -- but given your structure, you don't have an alternative.

FIND_IN_SET() returns the ordinal position of the first argument within the comma-separated string that is the second argument, or in this case "something true" if a match is found; no match returns 0 ("false") and if either argument is null, the function returns NULL.

Also, it's difficult to say without a better understanding of your structure exactly what you want the query to do, but the derived table seems unnecessary.

SELECT Q.question_id, Q.school_id
 FROM Q_MASTER Q
 LEFT JOIN E_HEADER X on X.school_id = Q.school_id AND X.low_q = 1 AND X.school_id = 6
 WHERE NOT FIND_IN_SET(Q.question_id, X.keyword);

This would select every question_id and school_id from Q_MASTER where there exists a row in E_HEADER with a matching school_id and low_q is equal to 1 and school_id is equal to 6 and where the value of Q.question_id is not found in the comma-separated string X.keyword.

Based on my interpretation of the original query, only Q_MASTER records with school_id = 6 will be returned in spite of the left join, because the join requires school_id to be identical, and will project a NULL for X.keyword otherwise; FIND_IN_SET() returns NULL if either argument is null, and the expression "NOT (NULL)" returns evaluates to NULL which will be considered untrue by the WHERE clause. The query will perform much better as an inner JOIN than a LEFT [outer] JOIN because fewer rows will need to be considered, but the ultimately correct approach requires a valid logical expression of what you actually need the server to do.