CODEWITHSUNDEEP
mysqlsqlselectjoin
Awais Qarni
Awais Qarni

Reputation: 18006

mysql join for two columns of one table

Hello I have a table tbl_relations which looks like

 -----------------------------------
 | id  |  source_id  |  target_id  |
 -----------------------------------
 | 2   |   2         |   4         |
 -----------------------------------
 | 3   |   5         |   7         | 
 -----------------------------------
 | 4   |   7         |   4         |
 -----------------------------------

And other table tbl_looksup which looks like

------------------------------
| id   |  language  |  value  |
------------------------------
| 1    |  1         |   abc   |
------------------------------
| 1    |  2         |   abc   |
------------------------------
| 2    |  1         |   abc   |
-------------------------------
| 2    |  2         |   abc   |
-------------------------------
| 5    |  1         |   abc   |
-------------------------------
| 5    |  2         |   abc   |
-------------------------------
| 7    |  1         |   abc   |
-------------------------------
| 7    |  1         |   abc   |
-------------------------------

tbl_relations is mapped to tbl_looksup in such a way that tbl_relations.source_id and tbl_relations.target_id is the id of tbl_looksup

My Problem I need to find out those records in tbl_relations whoes source_id or target_id is not present in tbl_looksup. It means there is no id exists in tbl_looksup. In more details the first record of tbl_relations has target_id = 4 which doesn't exist in tbl_looksup. This is faulty record. I need to find out these records.

What I have done so far

 SELECT 
  tbl_relations.source_id,
  tbl_relations.target_id,
  tbl_relations.id,
  tbl_looksup.`id` AS tblid 
FROM
  tbl_relations
  LEFT JOIN tbl_looksup 
   ON tbl_relations.`source_id` != tbl_looksup.`id` 
   OR tbl_relations.`target_id` != tbl_looksup.`id` 
GROUP BY tbl_relations.id

Upvotes: 1

Views: 134

Answers (4)

Ilya Gruzinov
Ilya Gruzinov

Reputation: 31

SELECT tbl_relations.id FROM tbl_relations 
  LEFT JOIN tbl_looksup 
    ON tbl_looksup.id = tbl_relations.source_id OR tbl_looksup.id = tbl_relations.target_id 
  WHERE tbl_looksup.id IS NULL

Upvotes: 0

rubensworks
rubensworks

Reputation: 1

Try adding this:

WHERE tbl_relations.target_id IS NULL

Upvotes: 0

Cris
Cris

Reputation: 13341

SELECT 
  tbl_relations.source_id,
  tbl_relations.target_id,
  tbl_relations.id 
FROM
  tbl_relations 
   WHERE tbl_relations.source_id not in (select id from tbl_looksup)
      OR tbl_relations.target_id not in (select id from tbl_looksup)

Upvotes: 0

John Woo
John Woo

Reputation: 263683

In order to get your desired result, you need to join tbl_looksup twice since there are two columns which depends on that table.

SELECT  DISTINCT a.*
FROM    tbl_relations a
        LEFT JOIN tbl_looksup  b
            ON a.source_id  = b.id
        LEFT JOIN tbl_looksup  c
            ON a.target_id = c.id
WHERE   b.id IS NULL OR 
        c.id IS NULL

To further gain more knowledge about joins, kindly visit the link below:

OUTPUT

╔════╦═══════════╦═══════════╗
║ ID ║ SOURCE_ID ║ TARGET_ID ║
╠════╬═══════════╬═══════════╣
║  2 ║         2 ║         4 ║
║  4 ║         7 ║         4 ║
╚════╩═══════════╩═══════════╝

Upvotes: 2

Related Questions