MYSQL QUERY - if another column is true in the query

Steve Tinsley
Steve Tinsley used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
if a user has booked a topic (topicBooked), isn't it that mean a topic is booked (topicBooked) too?

is the table: bookingGroup stores the topic info?
Top Expert 2013

Commented:
I think you can add this column:

, EXISTS ( SELECT * FROM user_bookingOption ubo 
           WHERE bo.bookingOptionID <>  ubo.bookingOptionID AND 
           bo.topicID =  ubo.topicID AND ubo.userId = 2112) as topicBooked 

Open in new window


This will have a true value when there is the same TopicID exists for a different booking
Ryan ChongSoftware Team Lead

Commented:
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?
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
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..

Author

Commented:
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

Author

Commented:
Heres a screenshot of the db:database
Top Expert 2013
Commented:
Thanks for the table design screenshot. I think this will work:
, EXISTS ( SELECT 1 FROM user_bookingOption ubo INNER JOIN bookingOption bo1
           ON ubo.bookingGroupId = bo1.bookingGroupId
           WHERE bo.bookingOptionID <>  ubo.bookingOptionID AND 
           bo.topicID =  bo1.topicID AND ubo.userId = 2112) as topicBooked 

Open in new window

Author

Commented:
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:
, 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 

Open in new window

Top Expert 2013

Commented:
Yes, should be fine, if it does the job.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial