CODEWITHSUNDEEP
sqlmysqlduplicatesdistinct
Martin
Martin

Reputation: 1655

No duplicates in SQL query

I'm doing a select in MySQL with an inner join:

SELECT DISTINCT tblcaritem.caritemid, tblcar.icarid 
FROM tblcaritem 
INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid 
INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid 
WHERE tblcaritem.userid=72;

Sometimes I get duplicates of tblcaritem.caritemid in the result. I want to make sure to never get duplicates of tblcaritem.caritemid, but how can I do that? I tried to use DISTINCT but it just checked if the whole row is a duplicate, I want to check for only tblcaritem.caritemid, is there a way?

Sorry if I didn't explain it very well, I'm not the best of SQL queries.

Upvotes: 6

Views: 47180

Answers (4)

jle
jle

Reputation: 9489

You could do an aggregate function on the other row... something like max or min

SELECT tblcaritem.caritemid, max(tblcar.icarid) 
FROM tblcaritem 
INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid 
INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid 
WHERE tblcaritem.userid=72
group by tblcaritem.caritemid;

Upvotes: 0

Juliet
Juliet

Reputation: 81516

The problem here is just as you describe: you're checking for the uniqueness of the whole row, your dataset ends up looking like this:

CarItemId    CarId
---------    -----
1            1
1            2
1            3
2            1
2            2
3            3

You want unique CarItemIds with no duplicates, meaning that you also want unique CarIds ---- alright, you have 3 CarIds, which one should SQL Server pick?

You don't have much choice here except to aggregate those extra CarIds away:

SELECT tblcaritem.caritemid, max(tblcar.icarid)
FROM tblcaritem
INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid
INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid
WHERE tblcaritem.userid=72
GROUP BY tblcaritem.caritemid

Upvotes: 5

Bork Blatt
Bork Blatt

Reputation: 3368

If you put 2 fields in a SELECT DISTINCT query, it will return rows where the combination of the 2 fields is unique, not just where each field is unique.

You are going to have to run 2 separate queries if you want only unique results for each column.

Upvotes: 0

chaos
chaos

Reputation: 124267

GROUP BY tblcaritem.caritemid

Upvotes: 13

Related Questions