Link to home
Start Free TrialLog in
Avatar of TadSter
TadSter

asked on

How to join queries

I can't figure this out. What I have is

Folder      Client      Name      PrimarySpouse
1002      1002      John Delight      1
1002      1002      Jane Delight      0
1024      1024      Peter Sheffen      1
2056      2056      Rock Smith      1
2056      2056      Rachel Smith      0
2056      4056      Child Smith      1

What I want is 3 columns with
Client      PrimarySpouseName      SecondSpouseName

To exclude chidren I use Where L.Client = L.Folder


This join doesn't work
SELECT TOP 100 L.client
FROM [NamAddr].[dbo].[tbClients] as L
Where L.Client = L.Folder and L.PrimarySpouse = 1
Join [NamAddr].[dbo].[tbClients] as R
On L.Client = R.Client
Where R.Client = R.Folder and R.PrimarySpouse = 0
Order by Client

How do I create a join with the wife on the same record as the husband?
Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

Wait..  What about UNION?  I think that is what you are looking for..

If memory serves, MS SQL wants to use a INNER JOIN..  You also have LEFT and RIGHT joins as well.

HTH,

Kent
SOLUTION
Avatar of DcpKing
DcpKing
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TadSter
TadSter

ASKER

Wow! that's nice. But I also wanted the single Peter Sheffen in there. I am not sure how to modify the query to include him.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TadSter

ASKER

Thank you so much for your help. I am grateful!