Counting rows in one table that match something in another 1

[Foamcow]

I have 2 tables, events and photos

The photos table contains references to uploaded photos as well as the event id that those files refer to.

I am writing some code to output a list of events and want to say whether or not there are any photos associated with that event.

I have a query like this

SELECT e.id,e.eventName,e.eventLocation,e.publish FROM shareEvents e,sharePhotos p ORDER BY id DESC

Note the inclusion of sharePhotos p

Basically I want to count the number of photos that have an eid that matches the id field in shareEvents as well as retrieve the event info from the shareEvents table.

SQL isn't my strong point so can someone help?



Foamcow Heavy Industries - Web design and ranting
Target Marketing Communications - Advertising, Direct Marketing and Public Relations
I wonder what possesses people to make those animated gifs. Do you just get up in the morning and think, "You know what web design r

 

[Foamcow]

I thikn I've cracked it with some help from MySQL QueryBrowser

SELECT e.id, e.eventName,e.eventLocation,e.publish,count(*) FROM shareEvents e LEFT OUTER JOIN sharePhotos s ON s.eid=e.id GROUP BY s.eid

does that stand up to the expert's eye?

Foamcow Heavy Industries - Web design and ranting
Target Marketing Communications - Advertising, Direct Marketing and Public Relations
I wonder what possesses people to make those animated gifs. Do you just get up in the morning and think, "You know what web design r

 

[Foamcow]

correction to make life easier

SELECT e.id, e.eventName,e.eventLocation,e.publish,count(*) as numPhotos FROM shareEvents e LEFT OUTER JOIN sharePhotos s ON s.eid=e.id GROUP BY s.eid

Foamcow Heavy Industries - Web design and ranting
Target Marketing Communications - Advertising, Direct Marketing and Public Relations
I wonder what possesses people to make those animated gifs. Do you just get up in the morning and think, "You know what web design r

 

[r937]

even though mysql will execute it anyway, your GROUP BY is wrong, it should be

group by e.id, e.eventName, e.eventLocation, e.publish

r937.com | rudy.ca

 

[Foamcow]

Thank you.
I'll try that out, though as you said it seems to work ok anyway.

Can you offer an explanation as to why I should group that way so I know for next time?

Foamcow Heavy Industries - Web design and ranting
Target Marketing Communications - Advertising, Direct Marketing and Public Relations
I wonder what possesses people to make those animated gifs. Do you just get up in the morning and think, "You know what web design r

 

[r937]

because that's the way you have to do it -- all non-aggregate expressions in the SELECT must be in the GROUP BY

like i said, mysql lets you get away with writing non-standard GROUP BY queries, but you would not be able to even run them in any other database

plus, mysql do warn you that you could get unpredictable results -- search their site for "group by with hidden fields"

r937.com | rudy.ca

 

[Foamcow]

Thanks for the answer!
I've implemented your advice.

Manuals... hmmm... you might be on to something there ;-)

Foamcow Heavy Industries - Web design and ranting
Target Marketing Communications - Advertising, Direct Marketing and Public Relations
I wonder what possesses people to make those animated gifs. Do you just get up in the morning and think, "You know what web design r