SQL statment for muliplie tables including the count function .

[Dyslexic Fingars]

Warning I'm new to anything other than basic SQL so this 'might' seem like a
daft question.
My problem is - I've have two tables one hold's photo details and is called
'PhotoTable' it is linked via it's key field 'PhotoID' to another table
'CommentsTable' that can hold multiple comments for each photo in the photo
table. For each comment in the comment table there is a flag field
'CommentVerified' that is changed to a '2' once that particular comment has
been assessed/verified.
What I would like is a SELECT statement that will pull all fields from the
photo table along with a count of the number of verified comments for that
particular photo (the actual photo records pulled should come from arguments
based on the photo table). So far the best I have is this -

SELECT * FROM PhotoTable , CommentsTable
WHERE PhotoTable.PhotoID = CommentsTable.PhotoID

But this has problems, The WHERE statement I want would be more like -
WHERE PhotoTable.PhotoCat = 'VariableValue' the one I have only pulls
records of photos that have at least one comment (I understand why, just not
how to change it)

Also instead of it just supplying the values of the comments verified fields
I would like a count of the number of relevant comments for each photo (only
those which are containing a '2')

OK I hope this makes some sense to someone out there.

Cheers
DyslexicFingars

[Phil Stanton]

Try SELECT PhotoTable.*, CommentsTable.*,
FROM PhotoTable INNER JOIN CommentsTable ON PhotoTable.PhotoID =
CommentsTable.PhotoID
WHERE CommentsTable.CommentVerified = 2;

This will only select photos where there are comments, and in addition the
CommentVerified = 2

The JOIN bit defines how the tables relate to each other, the WHERE bit
applies constrains on which of the "combined" records are shown

Phil

"Dyslexic Fingars" <co*@yourattitudedyslexicfingars.net> wrote in message
news:xp******************@news-text.cableinet.net...

Warning I'm new to anything other than basic SQL so this 'might' seem like a daft question.
My problem is - I've have two tables one hold's photo details and is called 'PhotoTable' it is linked via it's key field 'PhotoID' to another table
'CommentsTable' that can hold multiple comments for each photo in the photo table. For each comment in the comment table there is a flag field
'CommentVerified' that is changed to a '2' once that particular comment has been assessed/verified.
What I would like is a SELECT statement that will pull all fields from the
photo table along with a count of the number of verified comments for that
particular photo (the actual photo records pulled should come from arguments based on the photo table). So far the best I have is this -

SELECT * FROM PhotoTable , CommentsTable
WHERE PhotoTable.PhotoID = CommentsTable.PhotoID

But this has problems, The WHERE statement I want would be more like -
WHERE PhotoTable.PhotoCat = 'VariableValue' the one I have only pulls
records of photos that have at least one comment (I understand why, just not how to change it)

Also instead of it just supplying the values of the comments verified fields I would like a count of the number of relevant comments for each photo (only those which are containing a '2')

OK I hope this makes some sense to someone out there.

Cheers
DyslexicFingars

[Ian Hinson]

Is this what you mean ?

SELECT * FROM PhotoTable , (SELECT Count(*) FROM CommentsTable
WHERE CommentsTable.PhotoID=PhotoTable.PhotoID
AND CommentsTable.CommentVerified=2) AS CountOfComments;

No join is used, so it will return 0 in the CountOfComments field where
appropriate.

HTH,
Ian.

"Dyslexic Fingars" <co*@yourattitudedyslexicfingars.net> wrote in message
news:xp******************@news-text.cableinet.net...

Warning I'm new to anything other than basic SQL so this 'might' seem like a daft question.
My problem is - I've have two tables one hold's photo details and is called 'PhotoTable' it is linked via it's key field 'PhotoID' to another table
'CommentsTable' that can hold multiple comments for each photo in the photo table. For each comment in the comment table there is a flag field
'CommentVerified' that is changed to a '2' once that particular comment has been assessed/verified.
What I would like is a SELECT statement that will pull all fields from the
photo table along with a count of the number of verified comments for that
particular photo (the actual photo records pulled should come from arguments based on the photo table). So far the best I have is this -

SELECT * FROM PhotoTable , CommentsTable
WHERE PhotoTable.PhotoID = CommentsTable.PhotoID

But this has problems, The WHERE statement I want would be more like -
WHERE PhotoTable.PhotoCat = 'VariableValue' the one I have only pulls
records of photos that have at least one comment (I understand why, just not how to change it)

Also instead of it just supplying the values of the comments verified fields I would like a count of the number of relevant comments for each photo (only those which are containing a '2')

OK I hope this makes some sense to someone out there.

Cheers
DyslexicFingars