Using a Where By in a JOIN that ins't exact

[cooldude832]

I have a table Pictures that store the gallery the picture belongs to via a single field

Gallery_ID

which has values of

G_123

E_421

G_123

E_41

etc.

I have a query of

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` left join `Admins` on (Admins.AdminID = Events.CreatorID) left join `Pictures` on (Pictures.Gallery_ID = Events.EventID) Where Events.Start_Date >= '2008-01-27 7:02:43' and Events.Active = '1' Group By Events.EventID Order By Start_Date DESC

Now my issue is the part in the joining of pictures

(Pictures.Gallery_ID = Events.EventID)

It needs to really be

(Pictures.Gallery_ID = "E_".Events.EventID)

However I am not sure how to do the connotation in mysql like that. 

[cooldude832]

I tried to do

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 , 

CONCAT('E_',Events.EVENTID) as E_search , COUNT(Pictures.PicID) as Pic_Count from `Events` left join `Admins` on (Admins.AdminID = 

Events.CreatorID) left join `Pictures` on (Pictures.Gallery_ID = E_search) Where Events.Start_Date >= '2008-01-27 7:09:45' and Events.Active = '1' 

Group By Events.EventID Order By Start_Date DESC

but get an unknown field E_Search in the clause

 

 

 

When I take the join out in phpmyadmin the field E_Search is E_EVENTID as I want, but how do I use it as criteria for the join?

[cooldude832]

solved it I just had to CONCAT in the JOIN caluse

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` left join `Admins` on (Admins.AdminID = Events.CreatorID) left join `Pictures` on (Pictures.Gallery_ID 
= CONCAT('E_',Events.EventID)) Where Events.Start_Date >= '2008-01-27 7:26:28' and Events.Active = '1' Group By Events.EventID Order By 
Start_Date DESC