SQL query syntax

[johngordon12]

Hope someone can help with this.

I have a database structure :

table : photos (Photo_ID (PK), Title, Supplier etc)
table : keywords (Keyword_ID, Keyword, Category)
table : photokeywords (Photo_ID, Keyword_ID)

I have a search form that I'm using where users can check a checkbox for any number of keywords to find all photos with any of those keywords assigned to it.

My results page has the following SQL as it's query :

SELECT *
FROM photos INNER JOIN photokeywords ON photos.Photo_ID = photokeywords.Photo_ID

What I want to disply is one result for each photo, regardless of how many matching keywords are found, but what this is doing is displaying one record for each matching keyword, ie if a Photo_ID has three of the selected keywords, I'm getting three records displayed for that photo - one for each matching keyword.

What should it be just to display a unique record for each photo?

Cheers,
Iain

[Newsgroup_User]

Iain,

try

SELECT DISTINCT *
FROM photos INNER JOIN photokeywords ON photos.Photo_ID =
photokeywords.Photo_ID

--

Dave Buchholz
I-CRE8
www.i-cre8.co.uk
Skype ID: I-CRE8


"Iain71" <webforumsuser@macromedia.com> wrote in message
news:emtrbu$99f$1@forums.macromedia.com...
> Hope someone can help with this.
>
> I have a database structure :
>
> table : photos (Photo_ID (PK), Title, Supplier etc)
> table : keywords (Keyword_ID, Keyword, Category)
> table : photokeywords (Photo_ID, Keyword_ID)
>
> I have a search form that I'm using where users can check a checkbox for
> any
> number of keywords to find all photos with any of those keywords assigned
> to it.
>
> My results page has the following SQL as it's query :
>
> SELECT *
> FROM photos INNER JOIN photokeywords ON photos.Photo_ID =
> photokeywords.Photo_ID
>
> What I want to disply is one result for each photo, regardless of how many
> matching keywords are found, but what this is doing is displaying one
> record
> for each matching keyword, ie if a Photo_ID has three of the selected
> keywords,
> I'm getting three records displayed for that photo - one for each matching
> keyword.
>
> What should it be just to display a unique record for each photo?
>
> Cheers,
> Iain
>

[johngordon12]

Just the same I'm afraid. The annoying thing is I was sure this was working when I left it on Friday, and now doesn't seem to be.

[johngordon12 · Accepted solution]

D'oh! It was GROUP BY I needed.

[Newsgroup_User]

.oO(Iain71)

>D'oh! It was GROUP BY I needed.

Not really. Just list all the columns you want to retrieve _explicitly_
instead of using SELECT *, then the DISTINCT will work as expected:

SELECT DISTINCT p.Photo_ID
FROM photos p
...

Micha

[johngordon12]

OK - thanks Micha.