SQL to return unique results based on 2 fields

[eakinpeter]

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

 

[PHV]

Something like this ?
SELECT tagId, Min(PhotoId)
FROM tags_photos
GROUP BY tagId

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IanWaterman]

just use distinct

select distinct tag_id, photoid from tags_photos

Ian

 

[eakinpeter]

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

 

[eakinpeter]

I still haven't managed to solve this yet

Any other suggestions?

 

[carp]

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.

 

[r937]

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

 

[carp]

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

 

[carp]

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!

 

[eakinpeter]

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

 

[Mercury2]

Can you do something like

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

i.e. concatenate both id's together ?