Select from table having constraints from related table

[menwn]

Hello all,

 

currently I have 3 tables. Users, Sessions and SessionsUsers. The SessionsUsers is a table to accommodate the many-to-many relationship that the tables Users and Sessions have. It has two columns sessions_id and users_id

 

However, I have one constrain when I select from the table Sessions. This table has id, session_names, session_capacity and registered. What I want to do is to retrieve only the session_names and ids that have session_capacity greater than the count of each session_id in the SessionsUsers table (i.e., I don't want to fetch sessions that their capacity is already full with users).

 

What I am currently doing is to calculate the capacity via a grouping and counting on the SessionsUsers table by session_id and updating the column registered in the Sessions table at the beginning of the application.

 

This works good enough but I was wondering whether I could select the not full sessions without doing all these updates with a single select statement. I am pretty sure it can be done but I cannot seem to figure out how. Maybe some combination of Joins, groupby and having but still no success.

 

Any help will be greatly appreciated.

 

Cheers,

Andreas

[Barand]

SELECT s.idsession, s.session_name, s.capacity, COUNT(u.iduser) as ucount
FROM session s
LEFT JOIN sessionuser u USING (idsession)
GROUP BY s.idsession
HAVING s.capacity > ucount

[menwn]

Thanks... works perfectly with the only change in the left join instead of having regular on s.id = u.session_id.

 

Don't know why though the original, without the change was not working ...

 

Cheers and thank you again!!!

[menwn]

Thanks... works perfectly with the only change in the left join instead of having regular on s.id = u.session_id.

 

Don't know why though the original, without the change was not working ...

 

Cheers and thank you again!!!

 

However it works only if I have at least on record in the table. If no record is there it does not return any results. Why is that?

[Barand]

That's why I used  LEFT JOIN, so you get the sessions with no users as well as those with matching sessionuser records.

With INNER JOIN you get matching rows only.

[menwn]

 

Hi,

 

You mean to say that if I write LEFT JOIN on s.id = u.session_id I have essentially an INNER JOIN? Because I do use LEFT JOIN. The way you have written the query (with the USING clause) I get an error.

 

Is something I didn't get? Can you please elaborate a bit more why the query fails when using the USING clause?

 

Thanks

 

[Barand]

USING  only works when (as in my test tables) the column names used for the join are the same in both tables. If not you have to use something like

 

session s LEFT JOIN sessionuser u ON s.id = u.sessionId

 

 

[fenway]

In general, USING isn't that helpful -- you can't combine it with ON().

[Barand]

I agree. I've had issues where it worked fine on my dev system but when implementing on a server with an older MySQL the queries failed. The newer version seemed smarter in resolving the joins whereas the older version had to have the tables joined a particular order for USING to work.

[menwn]

Thank you for your help!!

 

By the way the glitch I was talking about ("However it works only if I have at least on record in the table. If no record is there it does not return any results. Why is that?" ) it just the outcome of my not paying attention to detail.

 

I am using doctrine so I was not adding ->select('Sessions s')->leftJoin('s.SessionParticipant'). Doctrine internal if it doesn't see the s in the leftJoin it just translates into select from Sessions,Participants where....

 

Anyway. Just for future reference to others.

 

Thanks again.