×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Selecet DISTINCT plus one more

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

RE: Selecet DISTINCT plus one more

which row did you want?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Selecet DISTINCT plus one more

(OP)
I'd like to return ppics.purl

RE: Selecet DISTINCT plus one more

oh, you mean an extra column, not row smile

CODE

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  

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Selecet DISTINCT plus one more

(OP)
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.

RE: Selecet DISTINCT plus one more

(OP)
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

RE: Selecet DISTINCT plus one more

Quote:

but I am just trying to pull in the associated value from the ppics.purl

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

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

RE: Selecet DISTINCT plus one more

(OP)
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.   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close