CODEWITHSUNDEEP
mysql
user1448031
user1448031

Reputation: 2166

MySQL INNER JOIN on same table not working

This is my table:

nID | tID | rID | code | name
------------------------------
10  | 6   | 74  | 4254 | Argos
11  | 7   | 74  | 4254 | Okrash
12  | 8   | 74  | 4254 | Pyro
13  | 8   | 0   | 4254 | Maja
14  | 9   | 74  | 4254 | Caiu
15  | 10  | 74  | 4254 | Sobre
16  | 10  | 0   | 4254 | Inye

This is how I need the result:

nID | name
-------------
10  | Argos
11  | Okrash
13  | Maja
14  | Caiu
16  | Inye

Below is the MySQL query I am using. Obviously it's not working (It returns no output). I also need nID and name as 2 columns only in the output. Can't figure it out.

SELECT a.nID, a.name, b.nID, b.name FROM codeTable AS a
INNER JOIN codeTable AS b
ON a.rID = b.rID
WHERE a.rID='74' AND a.code='4254' AND a.tID NOT IN (10, 8)
AND b.rID='0' AND b.code='4254' AND b.tID IN (10, 8)

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Why are you doing a join for a simple selection query?

SELECT ct.nID, ct.name, ct.nID, ct.name
FROM codeTable ct
WHERE (ct.rID = '74' AND ct.code = '4254' AND ct.tID NOT IN (10, 8)) or
      (ct.rID = '0' AND ct.code = '4254' AND ct.tID IN (10, 8));

If your ids and codes are numbers, then don't use the single quotes. Reserve them for string (and date) constants.

The reason your query is returning no rows is because the join condition conflicts with the where clause. The join condition specifies tht the rIDs are the same for a and b. The where clause is looking for two different values (`a.rID = '74' and b.rid = '0').

Upvotes: 3

Related Questions