asked on MYSQL QUERY - if another column is true in the query
I have a query which pulls out data from a booking database.
It lists all the possible booking options as well and if the user has booked it.
The booking options have a topicID column, some options have the same topicID and cannot be booked twice.
I want to be able to add a column to my query which shows if the user has booked each topic... Perhaps by adding a topicBooked True/False column.
Current query example:
SELECT gb.* , bo.*, gb.bookingGroupId,
EXISTS ( SELECT * FROM user_bookingOption ubo WHERE bo.bookingOptionID = ubo.bookingOptionID AND ubo.userId = 2112) as userHasBooked
FROM bookingGroup gb
LEFT JOIN bookingOption bo ON (gb.bookingGroupId = bo.bookingGroupId)
For each row I need to ask has there been an option booked with the same topic number in this query.
Any help would be much appreciated.
It lists all the possible booking options as well and if the user has booked it.
The booking options have a topicID column, some options have the same topicID and cannot be booked twice.
I want to be able to add a column to my query which shows if the user has booked each topic... Perhaps by adding a topicBooked True/False column.
Current query example:
SELECT gb.* , bo.*, gb.bookingGroupId,
EXISTS ( SELECT * FROM user_bookingOption ubo WHERE bo.bookingOptionID = ubo.bookingOptionID AND ubo.userId = 2112) as userHasBooked
FROM bookingGroup gb
LEFT JOIN bookingOption bo ON (gb.bookingGroupId = bo.bookingGroupId)
For each row I need to ask has there been an option booked with the same topic number in this query.
Any help would be much appreciated.
MySQL ServerSQL
Last Comment
chaau
I think you can add this column:
This will have a true value when there is the same TopicID exists for a different booking
, EXISTS ( SELECT * FROM user_bookingOption ubo
WHERE bo.bookingOptionID <> ubo.bookingOptionID AND
bo.topicID = ubo.topicID AND ubo.userId = 2112) as topicBooked
This will have a true value when there is the same TopicID exists for a different booking
just realized I got a typo, what i mean is:
if a user has booked a topic (userHasBooked), isn't it that mean a topic is booked (topicBooked) too?
if a user has booked a topic (userHasBooked), isn't it that mean a topic is booked (topicBooked) too?
ASKER
Hi Guys,
Thanks for trying to help... I was worried i wasn't explaining the issue.
RYAN - Yes if a user has booked a topic (userHasBooked), then they HAVE booked (topicBooked) too.
- There can be multiple options of the same topic, so I want a flag next to all the topics when 1 of them has been booked. eg:
option | topicId | userHasBooked | topicBooked
option1 1 0 1
option2 2 1 1
option3 3 0 0
option4 1 1 1
option5 1 0 1
option6 3 0 0
option7 2 0 1
In this mock up you can see they have booked option2 and option4 which are in toipcId 2&1. Means I want topicBooked to be true for all the topics 2 and 1... but topicId 3 hasnt been booked yet so topicBooked is false for that..
Thanks for trying to help... I was worried i wasn't explaining the issue.
RYAN - Yes if a user has booked a topic (userHasBooked), then they HAVE booked (topicBooked) too.
- There can be multiple options of the same topic, so I want a flag next to all the topics when 1 of them has been booked. eg:
option | topicId | userHasBooked | topicBooked
option1 1 0 1
option2 2 1 1
option3 3 0 0
option4 1 1 1
option5 1 0 1
option6 3 0 0
option7 2 0 1
In this mock up you can see they have booked option2 and option4 which are in toipcId 2&1. Means I want topicBooked to be true for all the topics 2 and 1... but topicId 3 hasnt been booked yet so topicBooked is false for that..
ASKER
Chaau,
Thanks for your suggestion.... I think you understand what I want.... BUT:
The confusing thing is that the topicID is only stored in bookingOption... so I somehow need to cross reference if a booking has been made with bookingOption.topicID?????
Any other thoughts
Thanks for your suggestion.... I think you understand what I want.... BUT:
The confusing thing is that the topicID is only stored in bookingOption... so I somehow need to cross reference if a booking has been made with bookingOption.topicID?????
Any other thoughts
ASKER
Heres a screenshot of the db:![database]()
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Chaau,
YES! This is 99% there for what I want.
I changed ON ubo.bookingGroupId = bo1.bookingGroupId to ON ubo.bookingOptionId = bo1.bookingOptionId
And then I decided to remove: WHERE bo.bookingOptionID <> ubo.bookingOptionID
as I wanted booked items to also show as topicBooked=1 as well.
Would you say that is OK??
Heres the altered query:
YES! This is 99% there for what I want.
I changed ON ubo.bookingGroupId = bo1.bookingGroupId to ON ubo.bookingOptionId = bo1.bookingOptionId
And then I decided to remove: WHERE bo.bookingOptionID <> ubo.bookingOptionID
as I wanted booked items to also show as topicBooked=1 as well.
Would you say that is OK??
Heres the altered query:
, EXISTS ( SELECT 1 FROM user_bookingOption ubo INNER JOIN bookingOption bo1
ON ubo.bookingOptionId = bo1.bookingOptionId
WHERE bo.bookingOptionId <> ubo.bookingOptionId AND
bo.bookingOptionTopic = bo1.bookingOptionTopic AND ubo.userId = 2112) as topicBooked
Yes, should be fine, if it does the job.
is the table: bookingGroup stores the topic info?