Solved

Simple SQL Query Help Needed @ 500 Urgent

Posted on 2013-11-02
19
408 Views
Last Modified: 2013-11-07
I have this Table:

BookingAnd

Companion Table
I am trying to count how many airlines tickets sold in a booking system I am developing.
Some fixed values are:
booking.status='Confirmed'
booking.packageid='1'
companion_table.title<>'Infant'
Booking leader is stored in booking table so after counting how many companion in a single booking we need to add 1, i.e. 1+sum(companion names) where packageID=1 and booking.status=Confirmed and companion_table.title<>'Infant'

I tried to solve it and I got this query but Its not what I want
 
SELECT
booking.packageid AS soldSeats,
companion_table.name,
companion_table.title,
booking.id
FROM
booking
Inner Join companion_table ON booking.companion_id = companion_table.companion_id
WHERE
booking.packageid =  '1'
and booking.status='Confirmed'

Open in new window


The result I am expecting is to get 5 Sold

Which means 5 Airlines Tickets sold

And here is a print screen of the original booking:
HTML PRINT
Thank you in Advanced
booking.png
0
Comment
Question by:dorarishome
  • 9
  • 9
19 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
I can't see from your data how you get the name of the booking leader from the booking table. If we take packageid = 1 and status = confirmed there are only 4 matching records in the companion table and none of them are the booking leader.
0
 

Author Comment

by:dorarishome
Comment Utility
What is the alternative?
Can I save companion data into booking table?
How to save and retrieve?
is there better than json_encode php
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Not sure what you mean - you don't seem to be storing the booking leader name anywhere.

You're probably better of having a Booking table and a Passengers table. When a booking is made you store information in the Booking table (such as a Booking ID and Status), and in the passenger table, you store all of the passenger details (including the Booking Leader). For each passenger you store their Title, Name, DoB and Booking ID. You could also store a boolean value (true/false) to identify who is the booking leader.

You then have 1 booking record linked to x number of passengers. This is the standard way to do it.

Booking Table:
Booking ID: 1234    | Status: Confirmed

Passengers Table:
Booking ID: 1234    | Name: Passenger 1    | Leader: true
Booking ID: 1234    | Name: Passenger 2    | Leader: false
Booking ID: 1234    | Name: Passenger 3    | Leader: false
Booking ID: 1234    | Name: Passenger 4    | Leader: false
Booking ID: 1234    | Name: Passenger 5    | Leader: false

As to how to save and retrieve - there are so many different ways. If you're doing this through a web interface, then a server-side script such as PHP or ASP. That's a whole different question though.

Not sure what json_encode() has to do with this? That's a PHP function to allow you to pass data betwen scripts in JSON format!

Hope that all makes sense :)
0
 

Author Comment

by:dorarishome
Comment Utility
I am saving leader name in booking table and companion_id filed to link to passengers table
instead is it ok to store passengers in booking table in json format...i guess that is much easy
hope my post is clear
regards :)
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
The booking table you posted earlier has no leader name in it.

I wouldn't store the passengers in the booking table - that leads to bad database design, and I can't see a reason to store the data in json format. You'd have to do a lot more coding just to get at the passenger names.

The idea of realtional database design is to normalize it. The model I outlined earlier is the right way to go - a booking table to hold booking information and a passenger table to hold passenger information. The two are then linked on a common field - such as a booking ID. Anything other than this wil cause you problems later on.
0
 

Author Comment

by:dorarishome
Comment Utility
Hi,
Would you please help me design the table for the solution you provided?
I am not familiar with foreign key.
How the query look like?
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
Sure. Something like this:

CREATE TABLE `bookings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bookingid` VARCHAR(25) DEFAULT NULL,
  `status` VARCHAR(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Open in new window

CREATE TABLE `passengers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bookingid` VARCHAR(25) DEFAULT NULL,
  `title` VARCHAR(10) DEFAULT NULL,
  `name` VARCHAR(50) DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  `leader` TINYINT(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Open in new window

You may need to add your own fields to meet your needs, but remember - if the data is related to the booking, put it in the booking table - if it's related to the passengers, put it in the passengers table.

A query then might look something like this:

SELECT b.bookingid, b.status, p.title, p.name, p.dob, p.leader
FROM bookings b, passengers p
WHERE b.bookingid = p.bookingid
AND b.bookingid = '1234'
AND b.status = 'Confirmed'
AND p.title <> 'Infant';

Open in new window

This will select the booking details and all the passenger info for booking number '1234'

This one will give you the booking details and leader name for all confirmed bookings:

SELECT b.bookingid, b.status, p.title, p.name
FROM bookings b, passengers p
WHERE b.bookingid = p.bookingid
AND p.leader = 1
AND b.status = 'Confirmed'
GROUP BY b.bookingid;

Open in new window

0
 

Author Comment

by:dorarishome
Comment Utility
Hi,
I ended with this query.
Its what I need.
But Its giving me duplicate result (4 times )
How to fix it please ?
Then I guess its done!

SELECT distinct  b.id, b.status, p.title, p.name, p.dob
FROM booking b, companion_table p,summary s
WHERE p.companion_id = p.companion_id
AND b.status = 'Pending'
AND p.title= 'infant';
AND s.id='2'

Open in new window


Summary table is the table that hold quota for booking like how much available seats and tickets
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Without seeing some sample data, and some detail about the extra table (summary) and how it relates to the other tables I can't really help. I can't see how the summary table relates to your original question.

Also, what data are you trying to retrieve - that looks like all infant bookings that are pending which seems contrary to your original question.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dorarishome
Comment Utility
This query gives also not correct result!
Weather I chose pending or confirmed it gives same name list!!!
SELECT distinct  b.status, p.title, p.name, p.dob
FROM booking b, companion_table p
WHERE p.companion_id = p.companion_id
AND b.status = 'Pending'
AND p.title<> 'infant';
AND b.packageid='2'

Open in new window

0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
The first WHERE clause of your query looks wrong. You are comparing the same data against itself!! It should be:

WHERE b.companion_id = p.companion_id
0
 

Author Comment

by:dorarishome
Comment Utility
No need to summary table
Summary table contain data about package
I need to get name list related to one package (Package ID)
I did save package id in booking table
This is the scenario summarized
I have Package or offer for customers to travel to XYZ destination.
This offer includes Ticket and Hotel
When we do booking we keep leader name and passenger names
As you suggested I am saving leader and passengers in passenger name
what I finally need is to get a name list for all customers who will travel to that same destination or package or offer
i.e.
Package ->Do Booking-> Save Passenger List->Generate it->Send it for Agency to confirm
This is my question from start and sorry if in some point I am not clear

Your help much appreciated.
0
 

Author Comment

by:dorarishome
Comment Utility
GREAT!!!!!!!!!!
It worked out.
The first WHERE clause of your query looks wrong. You are comparing the same data against itself!! It should be:

WHERE b.companion_id = p.companion_id
This is what I need
Thank you so much
I need another related help.
Shall I ask here or post another Question.
Its too much related for this question.
Actually its about how to use this final query you helped with in retreving the Passengers Names List and how to form it
Really great
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Excellent. Pleased you got it working.

Probably best to ask another question, and link to this question in the new one.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Also, because these question require specific answer, it's often helpful to post some sample data that also shows the table structure :)
0
 

Author Comment

by:dorarishome
Comment Utility
How can I invited you to my second question?
I am new here!
Do you have ID or Email.
Appreciate if you teach me
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Because I'm involved in this question, I'll be notified of any comment here, so once you've posted your new question, post a comment here with a link to the new question, and I'll receive a notification.
0
 

Author Comment

by:dorarishome
Comment Utility
hi,
can you please help in this question?
http://www.experts-exchange.com/Database/MySQL/Q_28288478.html
regards
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
GREAT!!!!!!!!!!
It worked out.
The first WHERE clause of your query looks wrong. You are comparing the same data against itself!! It should be:

WHERE b.companion_id = p.companion_id
This is what I need
I would strongly advise you to not put join conditions into the where clause,
instead use ANSI join syntax like this:
SELECT distinct  b.status, p.title, p.name, p.dob

FROM booking b
INNER JOIN companion_table p ON b.companion_id = p.companion_id

WHERE b.status = 'Pending'
AND p.title<> 'infant';
AND b.packageid='2'
Note the bold above, my suggestion here is always reference the prior table first
(i.e. b has been listed prior to p)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now