JOIN same table GROUP BY

[dr.pepper]

Ive got a table called events which contains events (obviously!). An event can have multiple child events associated to them using a "parent_id" field. If an event is a parent then the parent_id is 0, otherwise the parent_id will be the event_id of its parent.

 

I have another table called users and each user can register for an event. When they do so they go into the event_registrations table and the event_id from the above table is stored as well as the user_id from the users table. A user can have a category_id associated to them also.

 

I want to search by this category_id (using category_id 16 as an example). I have tried doing it like below, however I need to ensure an event is only returned once. E.g if an event has 10 users who have registered for it and 5 of them are associated to category_id 16 then I only want to return the event once not five times. Im not sure how to group it because it will be either e.event_id or child_event_id which it will need grouping by.

 

SELECT 
e.event_id,
e1.event_id as child_event_id
FROM events as e

LEFT OUTER JOIN
events AS e1 ON e.event_id = e1.parent_id
LEFT OUTER JOIN event_registration er
ON e.event_id = er.event_id
LEFT OUTER JOIN users u
ON er.user_id = u.user_id
LEFT OUTER JOIN event_registration er1
ON e1.event_id = er1.event_id
LEFT OUTER JOIN users u1
ON er1.user_id = u1.user_id

WHERE (u.category_id = '16' || u1.category_id = '16')

 

Any suggestions welcome.

 

Thanks

[cb154]

could you post the table structure. I will then try and replicate and fix.

[dr.pepper]

Basically I have the following tables:

 

events

event_id | parent_id | name

 

users

user_id | name | membership_organisation_id

 

event_registration

event_registration_id | event_id | user_id

 

membership_organisations

membership_organisation_id | name

 

[cb154]

probably a type but none of the tables have category_id in them ?

[dr.pepper]

Sorry wrong table. Ignore the membership_organisation_id and membership_organisation table

 

Remove membership_organisation_id from the users table and replace it with category_id and instead of the membership_organisation table I have categories which has the following:

 

category_id | name

 

Sorry about that!

[cb154]

ok so simply I have 4 users with id 1 - 4 all have category 16. I also have them all attending an event (id 1). I get 16 returned rows (4 users all attending the event hence the 16) do you just want 1 row and number of attendees ?

[dr.pepper]

ok so simply I have 4 users with id 1 - 4 all have category 16. I also have them all attending an event (id 1). I get 16 returned rows (4 users all attending the event hence the 16) do you just want 1 row and number of attendees ?

 

Yes but you could also have another 4 users with id 5-8 (or event the same users) who have attended an event (id 2 - parent_id 1)

 

I only want the events to be returned so I dont want the event returned 8 times, just twice. Make sense?

[cb154]

hate to say it and I might be way off can you not just do Select Distinct instead then ?

[dr.pepper]

hate to say it and I might be way off can you not just do Select Distinct instead then ?

 

Select Distinct on e.event_id or child_event_id though? Thats my problem, I need to ensure these are both distinct. I am joining the table on itself because the event can be from table e or from e1 so how do I group by e.event_id and/or child_event_id? 

[cb154]

would a UNION query not suffice then. if we break down each part into 2 seperate queries ie. what you need from table 1 and table 2 then union them up ?

[dr.pepper]

would a UNION query not suffice then. if we break down each part into 2 seperate queries ie. what you need from table 1 and table 2 then union them up ?

 

Yes your right this would be the best way. I started implementing a UNION query this morning and it seems to have resolved my problem so thanks for your help.