August 12, 2007 at 6:32 am
I have 2 tables
FACILITY - facilityID, facilityNum, familyName, etc
1, 3456, Smith
2, 1234, Jones
PARENT - familyID, facilityID, firstname, etc
100, 1, Tom
200, 1, Jane
300, 2, Kim
Basically, I want one record for each family - some families will have 2 parents while others will have only 1 parent.
SELECT a.[FacilityNum], a.facilityID, a.[FamilyName], b.Firstname, c.Firstname AS other1stname
FROM [Facility] a INNER JOIN
[Parent] b ON a.facilityID = b.facilityID LEFT OUTER JOIN
[Parent] c ON b.facilityID = c.facilityID
ORDER BY a.facilityID
The query results were:
3456 1 Smith Tom Jane
3456 1 Smith Tom Tom
3456 1 Smith Jane Jane
3456 1 Smith Jane Tom
1234 2 Jones Kim Kim
The next query I tried was
SELECT a.[FacilityNum], a.facilityID, a.[FamilyName], b.Firstname, c.Firstname AS other1stname
FROM [Facility] a INNER JOIN
[Parent] b ON a.facilityID = b.facilityID LEFT OUTER JOIN
[Parent] c ON b.facilityID = c.facilityID and b.firstname<>c.firstname
ORDER BY a.facilityID
The query results were:
3456 1 Smith Tom Jane
3456 1 Smith Jane Tom
1234 2 Jones Kim null
The records I want to pull are:
3456 1 Smith Tom Jane
1234 2 Jones Kim null
Thank you for any help
August 12, 2007 at 9:28 am
Try sub-ing this into the code... orginal line says
[Parent] c ON b.facilityID = c.facilityID and b.firstname<>c.firstname
... try changing to ...
[Parent] c ON b.facilityID = c.facilityID and b.firstname< c.firstname
--Jeff Moden
August 12, 2007 at 10:47 am
Thanks Jeff, when I changed the line, my results were
3456 1 Smith Tom Jane
3456 1 Smith Jane null
1234 2 Jones Kim null
It's kinda frustrating since originally the table design (not mine) had the parent info in the same record as the family record. The client wanted more information stored for each parent so I thought it was a good time to normalize the data. I can program a work around but I really wanted just one SQL statement to pull both parents since my client likes to write his own reports using access and is more limited in his SQL abilitlies.
Vicki
August 12, 2007 at 8:36 pm
If you are not worried in what order the parent's names appear and they do not have the same name then this should work 🙂
SELECT
a.[FacilityNum]
,a.facilityID
,a.[FamilyName]
,b.Firstname
,c.Firstname AS other1stname
FROM [Facility] a
INNER JOIN
(
SELECT facilityID,
MAX(firstname) as Firstname from [Parent]
group by facilityID
) b ON a.facilityID = b.facilityID
LEFT OUTER JOIN [Parent] c ON b.facilityID = c.facilityID and b.firstname<>c.firstname
ORDER BY a.facilityID
August 13, 2007 at 6:22 am
Wow, that worked - thanks. And, I was able to 'order by familyname, facilityID' and it seem to worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply