Mysql get data not present in another table

Question

I am having four mysql tables

Table 1, t_source:

+--------+-------+
| cindex | cname |
+--------+-------+

Table 2, t_language:

+--------+-------+
| cindex | cname |
+--------+-------+

Table 3, r_src_cat:

+-----------+-----------+-------+
| src_index | lan_index | clink |
+-----------+-----------+-------+

Table 4, r_source:

+-----------+-----------+-------+
| src_index | lan_index | clink |
+-----------+-----------+-------+

I want to retrieve clink, source and language indexes or name from table 3 - r_src_cat which are not present in table 4 - r_source. For this I am using following query:

SELECT
  r_src_cat.clink,
  t_source.cname,
  t_language.cname
FROM r_src_cat, r_source, t_source, t_language
WHERE r_src_cat.c_src_index != r_source.c_src_index
      AND r_src_cat.c_lan_index != r_source.c_lan_index
      AND t_source.cindex = r_src_cat.c_src_index
      AND t_language.cindex = r_src_cat.c_lan_index
ORDER BY r_src_cat.clink;

This is giving me wrong results. There are about 12 records missing from r_source, but result is giving me like infinite number of any record displaying same record multiple times and that record is present in table.

Please rectify my query.

Thanks

EDIT---

Foreign Key Relations

Table 3, r_src_cat.src_index=>t_source.cindex Table 3, r_src_cat.lan_index=>t_language.cindex

Table 4, r_source.src_index=>t_source.cindex Table 4, r_source.lan_index=>t_language.cindex

EDIT 2----

By different suggestions, i run this query

SELECT
  r_src_cat.c_src_index,
  r_src_cat.c_lan_index
FROM r_src_cat
  LEFT JOIN r_source ON r_src_cat.c_src_index = r_source.c_src_index
WHERE r_source.c_src_index IS NULL;

Result

This result is correct, but not complete. For e.g.,I run following query on table 3:

SELECT
  r_src_cat.c_src_index,
  r_src_cat.c_lan_index
FROM r_src_cat
WHERE r_src_cat.c_src_index = 535 AND r_src_cat.c_lan_index = 11;

It is showing a result set with one row.

Now the same query on table 4 - r_source:

SELECT
  r_source.c_src_index,
  r_source.c_lan_index
FROM r_source
WHERE r_source.c_src_index = 535 AND r_source.c_lan_index = 11;

It returns and empty return set.

So combination 535,11 is present in table-3, but not in table 4. But the same combination is not present in running the Left Join query on table 3 & 4 mentioned above.

You really could have searched SO with the expression in the title of your question before asking... — Shadow, May 18 '17 at 09:06
@HimanshuAgrawal I'm sorry, but I cannot believe you, since I found the duplicate topic (and several others besides with the same solutions) using the words in your title. — Shadow, May 18 '17 at 09:16
@Shadow I' sorry, but framing the same title doesn't mean the problem level about same. There are some similar questions with almost same problem level and i have tried their solutions, but I'm not getting the required result hence I have written the question — Himanshu Agrawal, May 18 '17 at 09:30
@HimanshuAgrawal yet you have not shown how you have tried to incorporate what has been suggested in those other topics. Pretty much all those topics include the type of solution suggested by Vivek below. The duplicate topic I linked also lists this solution, along with two other possibilities. — Shadow, May 18 '17 at 09:33
@Shadow, please have a look at my second edit in my question — Himanshu Agrawal, May 18 '17 at 10:01
@knowledge.... please have a look at my first Edit in question — Himanshu Agrawal, May 18 '17 at 10:15
If your are looking for a combination of two fields being present in another table, then you should join the two tables using the combination of the two fields, not just one of them... — Shadow, May 18 '17 at 10:18

score 0 · Accepted Answer · answered May 18 '17 at 09:13

0

As far as I can understand your question, your problem is e.g. Table A(Table 3) and B(Table 4) contains some similar data but you want those data which is not present in table B.

e.g. A contains 1,2,3,4,5 B contains 1,2,3

So you want result to be missing ones i.e., 4,5.

If this is your query, then try this sample and modify as per your requirement:

SELECT A.col1, A.col2 FROM A LEFT JOIN B ON A.col1=B.col1 WHERE B.col1 IS NULL

Hopefully this will help.

answered May 18 '17 at 09:13

Vivek Pandita

69
9

I am working on it – Himanshu Agrawal May 18 '17 at 09:30
Hi Vivek, please have a look at my Second Edit in my question – Himanshu Agrawal May 18 '17 at 10:00
Modify your query by adding equality check for **c_lan_index** So after `LEFT JOIN r_source ON r_src_cat.c_src_index = r_source.c_src_index` in your query add `AND r_src_cat.c_lan_index=r_source.c_lan_index` followed by `WHERE r_source.c_src_index IS NULL;`. So try this – Vivek Pandita May 19 '17 at 07:07

Mysql get data not present in another table

1 Answers1