Solved

Simple SQL Query Help Needed @ 500 Urgent

Posted on 2013-11-02
19
413 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 43

Expert Comment

by:Chris Stanyon
ID: 39619184
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
ID: 39619205
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 43

Expert Comment

by:Chris Stanyon
ID: 39619213
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dorarishome
ID: 39619217
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 43

Expert Comment

by:Chris Stanyon
ID: 39619375
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
ID: 39619633
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 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39619733
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
ID: 39620033
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 43

Expert Comment

by:Chris Stanyon
ID: 39620048
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
 

Author Comment

by:dorarishome
ID: 39620052
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 43

Expert Comment

by:Chris Stanyon
ID: 39620059
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
ID: 39620062
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
ID: 39620070
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 43

Expert Comment

by:Chris Stanyon
ID: 39620080
Excellent. Pleased you got it working.

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

Expert Comment

by:Chris Stanyon
ID: 39620087
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
ID: 39620107
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 43

Expert Comment

by:Chris Stanyon
ID: 39620110
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
ID: 39631657
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
ID: 39632359
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

777 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