Issues with Selecting a COUNT on a blank set

[cooldude832]

I have a multiple table database with tables that are

Admin Table (Primary key AdminID)

Events Table (Primary Key EventID links to Admin via Creator/Modifier ID and to  Pics by EventID)

Pictures Table (Primary Key PicID links to Events via EventID)

 

and I have a query that is

Select Events.EventID as Event_ID , Events.EventName as Name , Events.Start_Date as Start_date , Events.End_Date as End_date , 

Events.ModifiedDate as Date_mod , Events.CreatedDate as Date_created , Events.Summary as Summary , Admins.Email as Creator , 

COUNT(Pictures.PicID) as Pic_Count from `Events`, `Admins`, `Pictures` Where Events.Start_Date >= '2008-01-19 15:05:23' and Events.Active = '1' 

and Pictures.EventID = Events.EventID and Admins.AdminID = Events.CreatorID Group By Events.EventID Order By Start_Date DESC

To basically splash out a list of all events that started/will start in the past 60 days to never ending.

When I added the part about

COUNT(PICTURES.PicID) as Pic_Count 

and

Pictures.EventID = Events.EventID

the set returned was emptied till I populated pictures table with rows that had EventIDs of events.

 

Is there a way to do this with out using a JOIN to get the Pic COUNT and not have to restrict it to Pictures.EventID = Events.EventID???

 

I assumed this was the easiest way to restrict the COUNT to be only relative to that rows data and not the whole table's count.  I'd prefer not to have to place a dummy row in there to place hold each time a new event is added.

[fenway]

Yes... switch to LEFT JOIN.

[cooldude832]

I never have really used joins can u show me how to do this for this query of three tables

[fenway]

from `Events`, `Admins`, `Pictures` Where Events.Start_Date >= '2008-01-19 15:05:23' and Events.Active = '1' 
and Pictures.EventID = Events.EventID and Admins.AdminID = Events.CreatorID Group By Events.EventID Order By Start_Date DESC

becomes:

from `Events` 
left join `Admins` on ( Admins.AdminID = Events.CreatorID )
left join `Pictures` on ( Pictures.EventID = Events.EventID  )
Where Events.Start_Date >= '2008-01-19 15:05:23' and Events.Active = '1' 
Group By Events.EventID Order By Start_Date DESC

[cooldude832]

Perfect I was just going to ask the structure cause I got a funny error.

 

Is it okay to use the same textkey EventID in both tables or is that not mysql acceptable.

 

I tried it on an empty/filled pictures table and it returned what I expected.

[fenway]

Is it okay to use the same textkey EventID in both tables or is that not mysql acceptable.

Please explain...

[cooldude832]

The primary key of Events is EventID

In the table pictures I use the field called EventID (i.e Pictures.EventID) to link the pics to their event in the table i.e (Events.EventID)

is it okay practice to use the same text for the key's description in 2 different tables when 1 isn't primary and one is linking?

[fenway]

One is primary, and the other is the FK... that's exactly what you want.  In fact, you can replace

 

left join `Pictures` on ( Pictures.EventID = Events.EventID  )

 

with

 

left join `Pictures` using ( EventID)

 

for that very reason.

[cooldude832]

ahh okay