SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-05-2008, 05:51 AM
| ||||
| ||||
 query problem Hi all, I have a users table and every user has many registrations - Also a registration has 1 event. I want to get all the users of which I have the id and their registration for an event. I also want to have the user when she doesn't has registered for that event. I have a hard time solving this query. regards, Stijn      |
|
05-05-2008, 05:51 AM
| |||
| |||
| Re: query problem Tarscher: > I have a users table and every user has many registrations - Also a > registration has 1 event. > > I want to get all the users of which I have the id and their > registration for an event. I also want to have the user when she > doesn't has registered for that event. So, you have the id of a User, and you want the User's attribute values, regardless whether or not this User has a relationship to Registration? -- Erick |
|
05-05-2008, 05:51 AM
| |||
| |||
| Re: query problem Tarscher schreef: > Hi all, > > I have a users table and every user has many registrations - Also a > registration has 1 event. > > I want to get all the users of which I have the id and their > registration for an event. I also want to have the user when she > doesn't has registered for that event. > > I have a hard time solving this query. > > regards, > Stijn start reading here: http://dev.mysql.com/doc/refman/5.0/en/join.html if you have any further questions, please post the definitions of you table(s), and what you tried so far.... -- Luuk |
|
05-05-2008, 05:51 AM
| |||
| |||
| Re: query problem On May 3, 10:57*am, Luuk <L...@invalid.lan> wrote: > Tarscher schreef: > > > Hi all, > > > I have a users table and every user has many registrations - Also a > > registration has 1 event. > > > I want to get all the users of which I have the id and their > > registration for an event. I also want to have the user when she > > doesn't has registered for that event. > > > I have a hard time solving this query. > > > regards, > > Stijn > > start reading here:http://dev.mysql.com/doc/refman/5.0/en/join.html > > if you have any further questions, please post the definitions of you > table(s), and what you tried so far.... > > -- > Luuk Erick: indeed I want the user regardless of his relationship with registrations registrations table: id - event_id - user_id - present users table: id - name What I tried: SELECT * LEFT OUTER JOIN `registrations` ON registrations.user_id = users.id WHERE (`users`.`id` IN (1,3,2) AND (registrations.event_id = 3 OR registrations.id = null)) I want to get users with id 1,2,3 with registrations where event = 3 or that don't have registrations yet I hope this makes things more clear. regards, Stijn |
|
05-05-2008, 05:51 AM
| |||
| |||
| Re: query problem Tarscher: > On May 3, 10:57*am, Luuk <L...@invalid.lan> wrote: > > Tarscher schreef: > > > > > Hi all, > > > > > I have a users table and every user has many registrations - Also a > > > registration has 1 event. > > > > > I want to get all the users of which I have the id and their > > > registration for an event. I also want to have the user when she > > > doesn't has registered for that event. > > > > > I have a hard time solving this query. > > > > > regards, > > > Stijn > > > > start reading here:http://dev.mysql.com/doc/refman/5.0/en/join.html > Erick: You're replying to Luuk. Please, note where you are in the thread. > indeed I want the user regardless of his relationship with > registrations Then why not get the user?: select * from users where uid in (1,2,3); What exactly are you doing with those reservations and events, if the relationship to them is not relevant for this query? -- Erick |
|
05-05-2008, 05:51 AM
| |||
| |||
| Re: query problem On May 3, 12:29*pm, Erick T. Barkhuis <erick.use-...@ardane.c-o-m> wrote: > Tarscher: > > > > > On May 3, 10:57*am, Luuk <L...@invalid.lan> wrote: > > > Tarscher schreef: > > > > > Hi all, > > > > > I have a users table and every user has many registrations - Also a > > > > registration has 1 event. > > > > > I want to get all the users of which I have the id and their > > > > registration for an event. I also want to have the user when she > > > > doesn't has registered for that event. > > > > > I have a hard time solving this query. > > > > > regards, > > > > Stijn > > > > start reading here:http://dev.mysql.com/doc/refman/5.0/en/join.html > > Erick: > > You're replying to Luuk. Please, note where you are in the thread. > > > indeed I want the user regardless of his relationship with > > registrations > > Then why not get the user?: select * from users where uid in (1,2,3); > What exactly are you doing with those reservations and events, if the > relationship to them is not relevant for this query? > > -- > Erick Something like user 1 : attends user 2 : does not attend user 3: not yet registered I want to show the users with the ids (here 1,2,3) and whether they will attend the event or not. If the user hasn't got a registration for that event then she is considered not registered for that event. That's why I need to know if there is a registration for that user and event. Regards, Stijn In some cases there is no registration for a user for a certain event. |
|
05-05-2008, 05:51 AM
| ||||
| ||||
| Re: query problem On Sat, 03 May 2008 12:53:21 +0200, Tarscher <tarscher@gmail.com> wrote: > On May 3, 12:29*pm, Erick T. Barkhuis <erick.use-...@ardane.c-o-m> > wrote: >> Tarscher: >> >> >> >> > On May 3, 10:57*am, Luuk <L...@invalid.lan> wrote: >> > > Tarscher schreef: >> >> > > > Hi all, >> >> > > > I have a users table and every user has many registrations - Also >> a >> > > > registration has 1 event. >> >> > > > I want to get all the users of which I have the id and their >> > > > registration for an event. I also want to have the user when she >> > > > doesn't has registered for that event. >> >> > > > I have a hard time solving this query. >> >> > > > regards, >> > > > Stijn >> >> > > start reading here:http://dev.mysql.com/doc/refman/5.0/en/join.html >> > Erick: >> >> You're replying to Luuk. Please, note where you are in the thread. >> >> > indeed I want the user regardless of his relationship with >> > registrations >> >> Then why not get the user?: select * from users where uid in (1,2,3); >> What exactly are you doing with those reservations and events, if the >> relationship to them is not relevant for this query? > Something like > user 1 : attends > user 2 : does not attend > user 3: not yet registered > > I want to show the users with the ids (here 1,2,3) and whether they > will attend the event or not. If the user hasn't got a registration > for that event then she is considered not registered for that event. > That's why I need to know if there is a registration for that user and > event. SELECT u.id, u.name, IF(e.id IS NULL,'not registered',IF(e.present,'attends','does not attend')) as 'attends' FROM users u LEFT JOIN events a ON e.id = <event id> AND e.user_id = u.id WHERE u.id IN (1,2,3); -- Rik Wasmus |
Linear Mode
