×
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!

*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

SQL to return unique results based on 2 fields

SQL to return unique results based on 2 fields

SQL to return unique results based on 2 fields

(OP)
Hi all

Problem is I want to return unique results based on 2 fields:

I have a table tags_photos which is a join table and has 2 fields: tagId and PhotoId.

I want to create a page which has a list of all tags and a thumbnail against each tag. However I only want to show each tag once and I want a diff picture for each tag (so the same pic isn't repeated).

Any help would be appreciated

Peter

RE: SQL to return unique results based on 2 fields

just use distinct

select distinct tag_id, photoid from tags_photos

Ian

RE: SQL to return unique results based on 2 fields

(OP)
Thanks for the responses but the problems are:

PHV - this solution will return the same image if it has the same tags and I would like different images

IanWaterman - this solution will return the same tag more than once as each photo can have multiple tags

RE: SQL to return unique results based on 2 fields

(OP)
I still haven't managed to solve this yet

Any other suggestions?

RE: SQL to return unique results based on 2 fields

I think this is because what you want is not doable (as far as I can tell) via SQL alone.  If I understand your request correctly, what you are asking for is a page with the tag shown once and all of the photos associated with that tag:
 Tag                PhotoTag
  5                    23
                       34
                       54
                       83

Your page is going to have to accept a Tag and independently get all of the photoTags associated with that tag.  Another possiblity is a separate program (such as Oracle's SQL*Plus) that can filter out the repeating Tag values.  But SQL is going to give you a repeating Tag value - that's just the way it works.

RE: SQL to return unique results based on 2 fields

carp, you're right, this is not doable with SQL alone

but what eakinpeter wanted was only one thumbnail per tag

something like...

    tag     thumbnail
     42       frapple
     44       snorble
     47       glomble
     49       brumble

where all the thumbnails are unique, i.e. no two tags show the same thumbnail, even though thumbnails may share the same tags  

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

RE: SQL to return unique results based on 2 fields

Ah!  Got it.  Will see if I can come up with something now that I understand the problem.  Thanks, r937!

RE: SQL to return unique results based on 2 fields

eakinpeter -
Given the description of your situation, I can't think of any way to do this in pure SQL.  One thing that makes this extremely difficult is that there don't seem to be any rules concerning how images may overlap between tags.  If there were any conditions that you could guarantee (beyond "each tag will have at least one image associated with it") there might be a way to wing this.  But as currently outlined, it is way beyond me - I think you are going to have to leverage a procedural language to feed your page.
But I'd love to be wrong and see a working solution!

RE: SQL to return unique results based on 2 fields

(OP)
I will be calling the SQL from inside an asp page. Any suggestions of how I could combine SQL and asp would help.  

RE: SQL to return unique results based on 2 fields

Can you do something like  

select distinct char(tag_id) concat char(photoid) from ...

i.e. concatenate both id's together ?

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