Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecet DISTINCT plus one more

Status
Not open for further replies.
flyadvertising

flyadvertising

Programmer
Joined
Feb 5, 2011
Messages
8
Location
US
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
 
Sort by date Sort by votes
oh, you mean an extra column, not row :-)
Code: 
SELECT ppre.cID
     , ppre.yr
     , ppre.make
     , ppre.model
     , ppre.vin
     , ppre.display 
     , [red]TR.purl[/red]
  FROM ppre
INNER
  JOIN ( SELECT DISTINCT 
                pcID 
              , [red]purl[/red]
           FROM ppics ) AS TR 
    ON TR.pcID = ppre.cID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Right, an extra column.

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.
 
Upvote 0 Downvote
This did it:

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
 
Upvote 0 Downvote
but I am just trying to pull in the associated value from the ppics.purl
Click to expand...

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
 
Upvote 0 Downvote
and of course in ANSI SQL you are not allowed to omit columns from the GROUP BY clause like you can in mysql

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Yes, that helps, thanks. Fortunately, pulling any of the purl values associated with that cID satisfies what I need to do. However, I will look at the aggregate function so I can make the query more accurate.

Again, thanks for your help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question Question
SELECT COUNT distinct
Replies
5
Views
719
sal21
sal21
szeiss
  • Locked
  • Question Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
622
szeiss
szeiss
xwb
  • Locked
  • Question Question
One level recursive search
Replies
2
Views
347
xwb
xwb
soushi01
  • Locked
  • Question Question
One column become multiple column display record
Replies
2
Views
397
soushi01
soushi01
Almie
  • Locked
  • Question Question
Find overlaping dates for one specific Code
Replies
2
Views
451
Almie
Almie

Part and Inventory Search

Sponsor

Back
Top