Selecet DISTINCT plus one more
Selecet DISTINCT plus one more
(OP)
The following is working great, but I need to get one more row from the second table (ppics). However, when I try to add this in, my query stops working. Any help will be much appreciated.
SELECT ppre.cID, ppre.yr, ppre.make, ppre.model, ppre.vin, ppre.display FROM ppre
JOIN (SELECT DISTINCT pcID FROM ppics) AS TR
ON TR.pcID = ppre.cID
SELECT ppre.cID, ppre.yr, ppre.make, ppre.model, ppre.vin, ppre.display FROM ppre
JOIN (SELECT DISTINCT pcID FROM ppics) AS TR
ON TR.pcID = ppre.cID
RE: Selecet DISTINCT plus one more
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Selecet DISTINCT plus one more
RE: Selecet DISTINCT plus one more
CODE
, ppre.yr
, ppre.make
, ppre.model
, ppre.vin
, ppre.display
, TR.purl
FROM ppre
INNER
JOIN ( SELECT DISTINCT
pcID
, purl
FROM ppics ) AS TR
ON TR.pcID = ppre.cID
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Selecet DISTINCT plus one more
However, the SQL you provided shows all the results (not right). My original statement showed just the distinct TR.pcID's (right), but I am just trying to pull in the associated value from the ppics.purl in the same row as the TR.pcID. Sorry if I am not being clear.
RE: Selecet DISTINCT plus one more
SELECT ppre.cID, ppre.yr, ppre.make, ppre.model, ppre.vin, ppre.display, TR.purl FROM ppre INNER JOIN ( SELECT DISTINCT pcID, purl FROM ppics ) AS TR ON TR.pcID = ppre.cID GROUP BY ppre.cID
Thank you
RE: Selecet DISTINCT plus one more
if there was only one purl for each pcID in ppics, then you wouldn't have to do the GROUP BY in the outer query
and since you do have to, that means that there are multiple purl values for each ppre.cID
which means when you GROUP BY BY ppre.cID in the outer query, without applying an aggregate function to purl, you get an indeterminate value for purl -- it could be any one of the values associated with that cID
this is explained here: GROUP BY and HAVING with Hidden Columns
does that help?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Selecet DISTINCT plus one more
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Selecet DISTINCT plus one more
Again, thanks for your help.