OUTER JOIN SQL QUERY ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

OUTER JOIN SQL QUERY ISSUE

Author

Topic

jason_moutia
Starting Member

3 Posts

Posted - 2010-05-22 : 09:07:09

I have the following tables:

EXPERT TABLE
ExpertID E_name E_surname E_phone
1 Geoff Millar 92141111
5 Sue Davies 92142222
8 Hillary Cork 92144444
12 Penny Folds 92143333

EVENT TABLE
TourID EventDate ExpertID GiftID Price Meal
B1 18/02/2010 1 G01 $85.00 No
B1 18/02/2010 1 G03 $85.00 No
B1 18/02/2010 1 G05 $85.00 No
B1 18/02/2010 1 G07 $85.00 No
R2 18/02/2010 12 G01 $125.00 No
R2 18/02/2010 12 G03 $125.00 No
R2 18/02/2010 12 G07 $125.00 No
R2 18/02/2010 12 G09 $125.00 No
R2 25/02/2010 1 G02 $170.00 Yes
R2 25/02/2010 1 G05 $170.00 Yes
R2 25/02/2010 1 G08 $170.00 Yes
S1 3/06/2010 5 G02 $130.00 No
S1 3/06/2010 5 G04 $130.00 No
S1 3/06/2010 5 G09 $130.00 No
W1 3/06/2010 5 G01 $105.00 No
W1 3/06/2010 5 G04 $105.00 No
W1 3/06/2010 5 G06 $105.00 No

BOOKING_CUST TABLE
CustID TourID EventDate Paid
C078 R2 18/02/2010 Yes
C078 W1 3/06/2010 No
C123 B1 18/02/2010 Yes
C123 W1 3/06/2010 No
C178 B1 18/02/2010 Yes
C191 B1 18/02/2010 Yes
C191 R2 25/02/2010 Yes
C218 B1 18/02/2010 Yes
C218 R2 25/02/2010 Yes
C225 R2 18/02/2010 Yes
C267 R2 18/02/2010 Yes
C334 B1 18/02/2010 Yes
C367 B1 18/02/2010 Yes
C555 B1 18/02/2010 Yes

I need to list Tour Code, Event Date, Expert name and Total Payment made for each tour event.The list must be in Tour Code / Event Date sequence.

The result should be:

TourCode EventDate ExpertFirstName ExpertSurname Total
B1 18/02/2010 Geoff Millar 510
R2 18/02/2010 Penny Folds 375
R2 25/02/2010 Geoff Millar 340

My statement is as follow:
SELECT BOOKING_CUST.TourID, BOOKING_CUST.EventDate, EXPERT.E_name, EXPERT.E_surname, (count(BOOKING_CUST.TourID) * EVENT.price) AS [Total]
FROM (BOOKING_CUST LEFT OUTER JOIN EVENT ON BOOKING_CUST.TourID = EVENT.TourID)
LEFT OUTER JOIN EXPERT
ON EVENT.ExpertID = EXPERT.ExpertID
WHERE EVENT.EventDate = BOOKING_CUST.EventDate
AND PAID = YES
GROUP BY BOOKING_CUST.TourID, BOOKING_CUST.EventDate, EXPERT.E_name, EXPERT.E_surname, EVENT.price;

AND I HAVE THE FOLLOWING RESULT:
TourID EventDate E_name E_surname Total
B1 18/02/2010 Geoff Millar 2,380
R2 18/02/2010 Penny Folds 1,500
R2 25/02/2010 Geoff Millar 1,020

My total is not correct. Can anyone help me please?

Sachin.Nand

2937 Posts

Posted - 2010-05-23 : 15:21:48

SELECT BOOKING_CUST.TourID, BOOKING_CUST.EventDate, EXPERT.E_name, EXPERT.E_surname, (count(distinct BOOKING_CUST.TourID) * EVENT.price) AS [Total]
FROM (BOOKING_CUST LEFT OUTER JOIN EVENT ON BOOKING_CUST.TourID = EVENT.TourID)
LEFT OUTER JOIN EXPERT
ON EVENT.ExpertID = EXPERT.ExpertID
WHERE EVENT.EventDate = BOOKING_CUST.EventDate
AND PAID = YES
GROUP BY BOOKING_CUST.TourID, BOOKING_CUST.EventDate, EXPERT.E_name, EXPERT.E_surname, EVENT.price;

PBUH

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-05-23 : 15:53:02

Database design doesn't seem right. Why there is no PK for Event and Booking_Cust table? Also Where is junction table for tours and Customers?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources