SQL Query Join/Combine rows
I am using MS SQL Server 2005 and would like to join/combine ALL the rows in Table A to be in the output query along with all rows from Table B joined with data from Table A.
Table A
Addr ID Address Data
101 101 Main
103 103 Main
104 104 Main
105 105 Main
Table B
Apt ID Addr ID Apt Data
xxA 103 apt A
xxB 103 apt B
xxC 103 apt C
Desired Query Output
101 Main
103 Main <----- (how do I get this row?)
103 Main apt A
103 Main apt B
103 Main apt C
104 Main
105 Main
Table A
Addr ID Address Data
101 101 Main
103 103 Main
104 104 Main
105 105 Main
Table B
Apt ID Addr ID Apt Data
xxA 103 apt A
xxB 103 apt B
xxC 103 apt C
Desired Query Output
101 Main
103 Main <----- (how do I get this row?)
103 Main apt A
103 Main apt B
103 Main apt C
104 Main
105 Main
Are you wanting all the records from tableA and all the records from tableA with data from table B?
what your desired output seems to suggest is
select [Address Data] , [Apt Data]
from tableA
left join tableB on tableA.[Addr ID]=tableB.[Addr ID]
union
select [Address Data] , "" as [Apt Data]
from tableA
order by [Address Data]
what your desired output seems to suggest is
select [Address Data] , [Apt Data]
from tableA
left join tableB on tableA.[Addr ID]=tableB.[Addr ID]
union
select [Address Data] , "" as [Apt Data]
from tableA
order by [Address Data]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked as requested. Thanks!!!!
grs
grs
left join tableB on tableA.[Addr ID]=tableB.[Addr ID]
HuyBD;