I need some help in child & parent table. how do i display the 2 results for 2 different child tables but they are both from the same parent table?

Question

0.00/5 (No votes)

See more:

1. 2 child tables: Driver & Commuter
2. Parent table: User
3. My code and result and expected output(in link):

SQL

SELECT  Booking_ID
       ,Book_Time
       ,a.Name as Driver
       ,b.Name as Commuter
       ,Distance as Estimated_Travel_Distance
       ,Price_Min
       ,Price_Max
       ,book.Distance_Travelled as Actual_Distance
       ,(Distance_Travelled *Basic_Fare) as Actual_Fare
FROM    Booking_t book, user_t a, user_t b, locations_info_t loc, cartype_t ct
WHERE   book.Commuter = b.Phone_Num
    AND book.Driver = a.Phone_Num
    AND book.Distance_Travelled = loc.Distance
    AND book.Car_Type = ct.Car_Type_Name
    AND extract(month from Book_time) = 2 
    AND extract(year from Book_time) = 2014
    AND extract(day from Book_time) < 16 
GROUP BY Booking_ID;

http://i.stack.imgur.com/RC4ll.jpg

4. Driver & commuter have Phone_Num that is foreign key & referenced to user table Phone_Num
5. My code results show the commuter names in both driver & commuter columns but actual output should display drivers names and commuters names in driver and commuter column respectively.
6. When i add "book.Driver = u.Phone_Num" in the where clause to my code mentioned above, the output shows no data.

7. Table structure of User,Driver,Commuter & booking (Ignore the other tables in link)
http://i.stack.imgur.com/f6vRe.jpg

What I have tried:

I have already tried to add "book.Driver = u.Phone_Num" in the where clause that is in my code which is mentioned above, but the output still shows no data instead.

Posted 22-Feb-16 20:20pm

Member 12345181

Updated 23-Feb-16 21:51pm

Jörgen Andersson

v2

Add a Solution

Comments

Jörgen Andersson 24-Feb-16 2:47am

Just look at the first row of your query, you're using the same field for both the Driver and Commuter.

Member 12345181 24-Feb-16 3:09am

Hi, thanks i have made some changes with my code, Driver and commuters names are shown in each column respectively but the code is not showing records that is null, which is the driver's column containing null values.

my code:
select Booking_ID, Book_Time, a.Name as Driver, b.Name as Commuter, Distance as Estimated_Travel_Distance,Price_Min, Price_Max, book.Distance_Travelled as Actual_Distance, (Distance_Travelled *Basic_Fare) as Actual_Fare
from Booking_t book, user_t a, user_t b, locations_info_t loc, cartype_t ct
where book.Commuter = b.Phone_Num and book.Driver = a.Phone_Num and book.Distance_Travelled = loc.Distance and book.Car_Type = ct.Car_Type_Name and extract(month from Book_time) = 2
and extract(year from Book_time) = 2014 and extract(day from Book_time) < 16
group by Booking_ID;

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2016-02-23T21:51:00

Solution 1

Then you need to use an outer join
And to make it more readable you should avoid implicit joins, spell them out properly using ANSI joins.

Try this:

SQL

SELECT  Booking_ID
       ,Book_Time
       ,a.Name as Driver
       ,b.Name as Commuter
       ,Distance as Estimated_Travel_Distance
       ,Price_Min
       ,Price_Max
       ,book.Distance_Travelled as Actual_Distance
       ,(Distance_Travelled *Basic_Fare) as Actual_Fare
FROM    Booking_t book
JOIN    locations_info_t loc
    ON  book.Distance_Travelled = loc.Distance
JOIN    cartype_t ct
    ON  book.Car_Type = ct.Car_Type_Name
JOIN    user_t b
    ON  book.Commuter = b.Phone_Num
left OUTER JOIN user_t a
    ON  book.Driver = a.Phone_Num
WHERE   extract(month from Book_time) = 2 
    AND extract(year from Book_time) = 2014
    AND extract(day from Book_time) < 16;

By the way, you need to use proper keys. If a driver changes his phone number his records in the booking table will have disappeared

Posted 23-Feb-16 21:51pm

Jörgen Andersson

Updated 23-Feb-16 22:29pm

v4

Comments

Member 12345181 24-Feb-16 3:59am

Hi, let's assume that in my project the driver will not change his phone number.
Your code's output is still the same as mine, it does not display the null records.

Jörgen Andersson 24-Feb-16 4:33am

Remove the group by

Member 12345181 24-Feb-16 4:55am

The output is still the same, its not showing the booking records that have null value for Driver

Jörgen Andersson 24-Feb-16 5:33am

Can you create an example fiddle with some of your data here: http://www.sqlfiddle.com/
and post the link to the fiddle?

Member 12345181 24-Feb-16 11:22am

Hi, the group by needs to be there if not there will be duplication of booking ids and after making some changes and following what you mention on outer join to get the null records, i had gotten the correct results! Thank you!

Jörgen Andersson 24-Feb-16 15:11pm

Glad it worked, I'm just a bit curious to know which other changes were necessary?

Member 12345181 24-Feb-16 20:23pm

This was my code, I made changes to left outer join the driver and commuter table. The other tables added were to answer the whole question fully.

Code:
select book.Booking_ID, Book_Time, dd.Name as Driver,cc.Name as Commuter, locinfo.Distance as Estimated_Travel_Distance ,Price_Min ,Price_Max ,book.Distance_Travelled as Actual_Distance ,(Distance_Travelled *Basic_Fare) as Actual_Fare
from Booking_t book
left outer join commuter_t c on book.Commuter = c.Phone_Num
left outer join user_t cc on c.Phone_Num = cc.Phone_Num
left outer join driver_t d on book.Driver = d.Phone_Num
left outer join user_t dd on d.Phone_Num = dd.Phone_Num
join booking_destinations_t bkdest on book.Booking_ID = bkdest.Booking_ID
join locations_info_t locinfo ON (locinfo.End_loc = bkdest.Destination and locinfo.Start_loc = book.Pickup_Location )
join cartype_t ct ON book.Car_Type = ct.Car_Type_Name
where extract(month from Book_time) = 2 and extract(year from Book_time) = 2014 and extract(day from Book_time) < 16
group by Booking_ID;