CODEWITHSUNDEEP
mysql
S113jerry
S113jerry

Reputation: 57

inner join on multiple columns return duplicate records

I have table say abc

id smownerid    smcreatorid
1  33997         1
2  33997         1
3  33997         1
4  33998         1
5  33998         1
6  33998         33998
7  33998         33998
8  33998         33998
9  33998         33998
10 33940         33998
11 33940         33998
12 3             33998
13 33940         1
14 33940         33998
15 33997         33997
16 3             33997
17 33940         33998
18 4             33940
19 4             33997
20 33980        33940

Another table non_admin_users

id
3
33940
33997
33998
33999

My Sql query is

SELECT abc.* from abc
INNER JOIN non_admin_users vt_tmp_u33998 ON vt_tmp_u33998.id = abc.smownerid
OR (
vt_tmp_u33998.id = abc.smcreatorid
AND vt_tmp_u33998.id =33998
)

This query return duplicate records.

What I want is to fetch all the records from abc table whose smownerid equal to the non_admin_users' id or whose smcreatorid is 33998 without using where clause or distinct id column or group by statement.

I want this following output

id smownerid    smcreatorid
1  33997         1
2  33997         1
3  33997         1
4  33998         1
5  33998         1
6  33998         33998
7  33998         33998
8  33998         33998
9  33998         33998
10 33940         33998
11 33940         33998
12 3             33998
13 33940         1
14 33940         33998
15 33997         33997
16 3             33997
17 33940         33998

Upvotes: 0

Views: 273

Answers (4)

Learning
Learning

Reputation: 163

Could you try?

SELECT a.*
FROM abc a LEFT JOIN non_admin_users na ON a.smownerid = na.id
WHERE a.smcreatorid = 33998 OR IFNULL(na.id,'') != '';

[OR] Just to make sure that 33998 records won't be joined twice.

select * from 
(SELECT a.*
FROM abc a INNER JOIN non_admin_users na ON a.smownerid = na.id AND a.smcreatorid!=33998
UNION ALL
SELECT b.* FROM abc b INNER JOIN abc b1 ON b.id=b1.id AND b.smcreatorid=33998) a
order by id;

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17615

Perhaps an implicit distinct in a union is ok

select  * from
(
SELECT a.id, A.smownerid, A.smcreatorid 
FROM ABC  a
INNER JOIN non_admin_users n on n.id = a.smownerid and a.smcreatorid <> 33998
union 
SELECT a.id, A.smownerid, A.smcreatorid 
FROM ABC  a
inner JOIN non_admin_users n on n.id = a.smownerid 
) s 
order by id
;

+------+-----------+-------------+
| id   | smownerid | smcreatorid |
+------+-----------+-------------+
|    1 |     33997 |           1 |
|    2 |     33997 |           1 |
|    3 |     33997 |           1 |
|    4 |     33998 |           1 |
|    5 |     33998 |           1 |
|    6 |     33998 |       33998 |
|    7 |     33998 |       33998 |
|    8 |     33998 |       33998 |
|    9 |     33998 |       33998 |
|   10 |     33940 |       33998 |
|   11 |     33940 |       33998 |
|   12 |         3 |       33998 |
|   13 |     33940 |           1 |
|   14 |     33940 |       33998 |
|   15 |     33997 |       33997 |
|   16 |         3 |       33997 |
|   17 |     33940 |       33998 |
+------+-----------+-------------+

Upvotes: 0

S113jerry
S113jerry

Reputation: 57

INNER JOIN non_admin_users vt_tmp_u33998 ON ( vt_tmp_u33998.id = abc.smownerid AND abc.smcreatorid !=33998 ) OR (vt_tmp_u33998.id = abc.smcreatorid AND vt_tmp_u33998.id =33998)

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

Try this:

SELECT a.smownerid, a.smcreatorid
FROM abc a JOIN non_admin_users na ON a.smownerid = na.id
WHERE a.smownerid = 33998;

You can comment out WHERE condition if you want all the records.

Upvotes: 0

Related Questions