There are no duplicates in the SQL query

Question

I make a choice 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 as a result. I want to make sure I never get duplicates of tblcaritem.caritemid, but how can I do this? I tried using DISTINCT, but it just checked if the whole line is duplicate, I want to check only tblcaritem.caritemid, is there any way?

Sorry if I didn’t explain it very well, I am not the best SQL query.

Answer 1

GROUP BY tblcaritem.caritemid

Answer 2

The problem here is the same as you describe: you check the uniqueness of the entire row, your data set looks like this:

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

CarItemIds , , CarIds - , 3 CarIds, SQL Server?

, CarIds:

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

Answer 3

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

You will need to run 2 separate queries if you only need unique results for each column.

Answer 4

You can execute the aggregate function on another line ... 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;