Can''t retrieve the records I want

Question

Post reply

Can''t retrieve the records I want

Vicki Ross

Newbie

Points: 8
More actions
August 12, 2007 at 6:32 am

#373152

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003906 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vicki Ross Newbie Points: 8 More actions · Answer 2

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

cantor Say Hey Kid Points: 667 More actions · Answer 3

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

Vicki Ross Newbie Points: 8 More actions · Answer 4

Wow, that worked - thanks. And, I was able to 'order by familyname, facilityID' and it seem to worked.