• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

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.
0
Steve Tinsley
Asked:
Steve Tinsley
  • 4
  • 3
  • 2
1 Solution
 
Ryan ChongCommented:
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?
0
 
chaauCommented:
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
0
 
Ryan ChongCommented:
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Steve TinsleyAuthor 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..
0
 
Steve TinsleyAuthor 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
0
 
Steve TinsleyAuthor Commented:
Heres a screenshot of the db:database
0
 
chaauCommented:
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

0
 
Steve TinsleyAuthor 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

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now