February 1, 2005 at 8:30 am
I am having a problem with a join where I have a parent table and 2 child tables which have a 1:M relationship to the parent. I want to show all records from the parent but only show the first record from the child otherwise I get duplicate rows of the child.
My sql statement looks like:
SELECT d.id,termnum,pickupdate,driverName,truckNumber,trailerNumber,Shipper,s.City as pickupcity,s.State as pickupstate,s.PhoneNum as pickupPhoneNumber,delDate,consignee,c.City as delCity,c.State as delState,c.PhoneNum as delPhoneNumber,comments from dispatch d LEFT JOIN shippers s on s.id = d.id LEFT JOIN consignees c on c.id = d.id WHERE pickupdate >= '1/5/2005'
I tried doing a subquery and using top 1 for the 2 joins for the child tables but that only returns me 1 record as I need all the records from the parent table but just the first record in the 2 child tables.
I was thinking about adding a field to the 2 child tables to track how many children go with that parent then just select it and point that I want the 1st one.
Any thoughts?
Matt
February 1, 2005 at 8:36 am
Without the table DDL it's not clear what the best solution is.
Does the child table have a unique identifier where selecting the MIN() of it in a subquery would yield your definition of "first record" ?
February 1, 2005 at 8:45 am
Matt, since you are joining on the ID, is it safe to say that all matching child records would have the same (distinct) fields for:
,s.ID
,s.City
,s.State
,s.PhoneNum
,c.id
,c.City
,c.State
,c.PhoneNum
If so, you could always do a select distinct on these columns and use them as derived toubles for your outer joins.
Ryan
February 1, 2005 at 11:36 am
rhunt: No the shipper and consigneee tables are distinctly different. They would never be duplicated information in either of those tables. Distinct does not work for this anyway since I already tried adding distinct on the ID and it still pulls in all the child rows as seperate records.
As far as the min, I do have a shipid and consigneeid fields which are just auto increment fields. Even if I could get the mininum value I wouldnt know what value would be the mininum to test.
I am going to try to play with the min see if I can get something working.
Matt
February 1, 2005 at 11:48 am
Matt, maybe I am misunderstanding. Your schema is roughly:
Dispatch (1) --> Consignees (Many)
Dispatch (1) --> Shippers (Many)
Shippers and Consignees both have foreign key relationship of Dispatch.ID
Right?
If sprecifically ID,City,State,PhoneNum from shippers are not distinct in some fashion, how are you deciding which City, State, Phone you want to return with the parent Dispatch.ID match - or do you not care?
February 1, 2005 at 11:55 am
You are correct on the relationship. I am displaying all the shipper information and consignee information all in one row. Look at my select statement carefully. You will see that I have information from both the shipper and consignee table being shown as one record. So I need the first row from both the shipper and consignee table as one row. When I do my join I need to find some way only to pull 1 record from teh shipper table and 1 record from the consignee table.
Hope that makes sense.
Matt
February 1, 2005 at 2:40 pm
I have found a solution but I am not sure if this is a bad way of doing it. I sense that I might be pulling too many records for my joins. But if i add the d.id = s.id and d.id =c.id then I only get 1 record total and I am supposed to get 3.
SELECT d.id,termnum,pickupdate,driverName,truckNumber,trailerNumber,Shipper,s.City as pickupcity,s.State as pickupstate,s.PhoneNum as pickupPhoneNumber,delDate,consignee,c.City as delCity,c.State as delState,c.PhoneNum as delPhoneNumber,comments from dispatch d INNER JOIN (SELECT top 1 id,pickupdate,shipper,city,state,phonenum from shippers) s LEFT JOIN (SELECT top 1 id,deldate,consignee,city,state,phonenum from consignees) c ON 1=1 ON 1=1 where pickupdate >= '1/5/2005'
Had to do a left join on the consignees table as it could have no records to join.
Matt
February 2, 2005 at 2:09 am
Matt,
in my opinion, the real problem is the definition of 'first row of Consignees/Shippers table for an id'.
Once you can define what is the 'first row', you can use 'derived' tables in your select query, tables that only contain the first row for an id.
I would create views ( and not derived tables, as the select becomse unreadable ) for the Consignees and the Shippers table, and this views should only contain 'the first row' for each id.
Afterwards, writing the query you need becomes easy ...
Bert
February 2, 2005 at 8:46 am
Thats the problem getting that 1 value from a table thats 1 to many is not possible.
Take the simple query and say SELECT id, shippers, pickupdate, city,state, phonenum from shippers INNER JOIN dispatch on dispatch.id = 5 order by pickupdate.
That would return lets say 3 records. I just want to get the first record. TOP wont work once you put 2 derieved tables together. IT will return one row total. I need to show all records from the dispatch but only show 1 from the shippers and consignees tables and merge them.
So to make things easier I am just going to create another field called stop number. I will just select where the stop number = 1. Much easier and less complicated joins.
Matt
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply