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
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
SELECT tagId, Min(PhotoId)
FROM tags_photos
GROUP BY tagId
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL to return unique results based on 2 fields
select distinct tag_id, photoid from tags_photos
Ian
RE: SQL to return unique results based on 2 fields
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
Any other suggestions?
RE: SQL to return unique results based on 2 fields
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
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
RE: SQL to return unique results based on 2 fields
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
RE: SQL to return unique results based on 2 fields
select distinct char(tag_id) concat char(photoid) from ...
i.e. concatenate both id's together ?