SQL Help

[jsnull]

Hi... I have one table, payments that contains payment details, another table tripdetails that contains a trips details. I want to list out all the payment details for a trip. I keep getting some records with duplicate listings. AS you can see, in the results, PID's 18, 19 and 20 are listed twice in the results and should only be listed once.Here's the details

Any help to resolve is appreciated!

Data in the tables are as follows:

payments

PID TripID OrgID Payment PayDate
15 284 30 25.5 11-01-2006
13 284 30 675 11-30-2006
16 265 28 75 12-15-2006
17 284 30 4 01-01-2007
18 271 31 1 01-05-2007
19 271 31 1 01-07-2007
20 271 31 1 01-12-2007


tripdetails

ID TripID OrgID Spaces Amount Field
14 284 30 8 800 Chicago
14 265 31 10 1000 Chicago
14 265 28 1 8 Chicago
14 271 31 1 1 Chicago


My SQL is:

SELECT payments.ID, payments.TripID, payments.OrgID, payments.Payment, payments.PayDate FROM payments LEFT JOIN tripdetails ON payments.OrgID = tripdetails.orgID WHERE tripdetails.Field LIKE "Chicago" ORDER BY payments.OrgID;

Results

PID TripID OrgID Payment Date
16 265 28 75.00
15 284 30 25.50
13 284 30 675.00
17 284 30 4.00
20 271 31 1.00
20 271 31 1.00
18 271 31 1.00
18 271 31 1.00
19 271 31 1.00
19 271 31 1.00


Jim Null

http://www.jimnull.com

 

[rac2]

Run the following query to gain some insight into the problem.

SELECT payments.ID, 
       payments.TripID AS TripIDPayments, 
       [COLOR=green]tripdetails.TripID AS TripIDTripDetails,[/color] 
       payments.OrgID, 
       payments.Payment, 
       payments.PayDate 
FROM payments 
LEFT JOIN tripdetails ON payments.OrgID = tripdetails.orgID WHERE tripdetails.Field LIKE "Chicago" 
ORDER BY payments.OrgID;

There are two rows in TripDetails that match OrgID = 31, both match the row in payments with OrgID = 31 so you get two rows in the join.

list out all the payment details for a trip

It is not clear to me why this would require a join; just

SELECT * FROM payments WHERE TripID = 31

What is it in TripDetails that must be considered in order to know something about the payments for a trip?