I'd like to receive a table from my database but I am unable to form the query.
This is what I like to achieve: Think of a group of users who shall be invited to an event. To find a date A list of dates are provided by the host.
So far we have these tables:
Users:
| Id | Name |
|---|---|
| 7 | Sally |
| 2 | Bob |
| 3 | John |
| 4 | Lisa |
| 5 | Joe |
| 6 | Jane |
Events
| Id | Name |
|---|---|
| 1 | Birthdayparty |
| 2 | Barbecue |
| 3 | Dinner |
Event Users:
| Id | UserId | EventId | |
|---|---|---|---|
| 1 | 7 | 1 | (Sally is invited to bp) |
| 2 | 2 | 1 | (Bob, too) |
| 3 | 4 | 1 | (and Lisa) |
| 4 | 1 | 2 | (Sally is invited. to Bbe) |
| 5 | 5 | 2 | (also Joe) |
| 6 | 4 | 2 | (and Lisa) |
So far for the structure of main parts of the db.
Now lets put some possible dates for the birthday party
EventProposal:
| Id | Event | Date | |
|---|---|---|---|
| 1 | 1 | 5. March | Birthday dates |
| 2 | 1 | 6. March | |
| 3 | 1 | 8. March | |
| 4 | 1 | 10. March | |
| 5 | 3 | 4. April | Dinner |
| 6 | 3 | 5. April |
Now the last table holds which user selected which dates for an event.
EventProposalSelection:
| Id | EventId | UserId | DateId | |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | Sally selected 5. March for birthday |
| 2 | 1 | 1 | 2 | Sally selected 6. March for birthday |
| 3 | 1 | 1 | 3 | Sally selected 8. March for birthday |
| 4 | 1 | 2 | 2 | Bob selected 6. March for birthday |
| 5 | 1 | 2 | 3 | Bob selected 8. March for birthday |
| 6 | 1 | 4 | 1 | Lisa selected 5. March for birthday |
| 7 | 1 | 4 | 2 | Lisa selected 6. March for birthday |
| 8 | 1 | 4 | 4 | Lisa selected 10. March for birthday |
What I like to know is if a user has picked a date for an event. I want to see all dates of a specific event for a specific user
(where clause contains userId and eventId)
If I ask for Sally in combination of Birthday (where userId = 1 and eventId = 1)
I'd like to receive
| DateId | Checked |
|---|---|
| 1 | true |
| 2 | true |
| 3 | false |
| 4 | true |
The tables are properly constrained and related to each other
How can I achieve this in MySQL?
EDIT:
select
e.Name EventName,
e.id,
dp.DateProposal DateOfEvent,
coalesce( u.Name, '' ) GuestName,
-- due to left-join, see if the date is chosen or NULL (not chosen)
case when dps.dateid IS NULL then false else true end DateWasPicked
from
-- start with events
Event e
-- Now, what dates were birthday parties POSSIBLE to attend
JOIN EventProposal dp
on e.id = dp.EventId
-- NOW, what dates by given users were selected
-- BUT, since not all dates may be selected, do a LEFT JOIN
LEFT JOIN EventProposalSelection dps
on dp.id = dps.dateid
-- and finally who selected the given date
-- and again LEFT-JOIN since a user may not pick all dates
LEFT JOIN User u
on dps.userid = u.id
-- prevent getting ALL users for an event
AND u.Id = 7 --Sally
where
-- but only birthday parties
e.Id = 1
-- Tricky here because you want ALL POSSIBLE event dates,
-- but ONLY those for Sally
AND ( dps.dateid IS NULL OR u.Id = 7 )
order by
-- and suggest you actually use datetime based column
-- as you can use functions to get the format of the date.
dp.DateProposal
Lead to
which seems to be fine, but when running for Bob (UserId = 2)
there is a date missing
And running for John (UserId = 3)
CodePudding user response：
Ok, so lets take this one step at a time. You are interested in a SPECIFIC event, and all POSSIBLE dates FOR said event. Then, based on a specific user, if they had (or not) picked any of the possible dates. And by the user ID, get the name too.
Sample data. Your version of data had the sample selections with Sally's ID of 1, not 7. So this is a sample set I ran with using 7 as the basis for Sally
create table users ( id integer, name varchar(10))
insert into users ( id, name ) values
( 7, 'Sally' ),
( 2, 'Bob' ),
( 3, 'John' ),
( 4, 'Lisa' ),
( 5, 'Joe' ),
( 6, 'Jane' )
create table Events ( id int, name varchar(15))
insert into Events (id, name ) values
( 1, 'Birthdayparty' ),
( 2, 'BBQ' ),
( 3, 'Dinner' )
create table EventUsers ( id int, userid int, eventid int )
insert into EventUsers ( id, userid, eventid ) values
( 1, 7, 1 ),
( 2, 2, 1 ),
( 3, 4, 1 ),
( 4, 1, 2 ),
( 5, 5, 2 ),
( 6, 4, 2 )
create table EventProposal (id int, event int, date datetime )
insert into EventProposal ( id, event, date ) values
( 1, 1, '2022-03-05' ),
( 2, 1, '2022-03-06' ),
( 3, 1, '2022-03-08' ),
( 4, 1, '2022-03-10' ),
( 5, 3, '2022-04-04' ),
( 6, 3, '2022-04-05' )
create table EventProposalSelection ( id int, eventid int, userid int, DateID int )
insert into EventProposalSelection ( id, eventid, userid, dateid ) values
( 1, 1, 7, 1 ),
( 2, 1, 7, 2 ),
( 3, 1, 7, 3 ),
( 4, 1, 2, 2 ),
( 5, 1, 2, 3 ),
( 6, 1, 4, 1 ),
( 7, 1, 4, 2 ),
( 8, 1, 4, 4 )
select
AllEventDates.id,
AllEventDates.EventName,
AllEventDates.DateOfEvent,
u.id UserID,
coalesce( u.Name, '' ) GuestName,
-- due to left-join, see if the date is chosen or NULL (not chosen)
case when eps.dateid IS NULL
then 'false' else 'true' end DateWasPicked
from
Users u
-- this query get all events and all possible dates regardless
-- of who may have supplied a selection to attend
JOIN
( select
e.id,
e.Name EventName,
ep.id EventProposalID,
ep.date DateOfEvent
from
Events e
JOIN EventProposal ep
on e.id = ep.Event
where
-- but only birthday parties
e.Name = 'Birthdayparty' ) AllEventDates
on 1=1
-- NOW, left join for a given one person
LEFT JOIN EventProposalSelection eps
on eps.userid = u.id
AND AllEventDates.EventProposalID = eps.dateid
-- and finally who selected the given date
-- and again LEFT-JOIN since a user may not pick all dates
where
u.id = 7
order by
-- and suggest you actually use datetime based column
-- as you can use functions to get the format of the date.
AllEventDates.DateOfEvent