SQL (T-SQL) JOIN Question - All records from one side
Here are the 2 tables I want to join:
Table A - Cross reference table for ID_X. I know the ID_X column isn't consecutive numbers.
ID_X Prompt
1 Type Classification
2 Land Type
3 Property Code
4 RRSP Status
12 Performing
13 Tax Payment
14 Income Producing
15 Interest Adjust
16 Prepayment Option
17 Prepayment Status
18 Loan Name
TABLE B
AA Id_X Value
1 4 false
1 12 false
1 13 0.00
1 16 Open w/ 30 days notice
1 17 Open
2 4 false
2 12 false
2 13 0.00
2 16 Open w/ 30 days notice
2 17 Open
3 4 false
3 12 false
3 13 0.00
3 16 Closed. YM will apply
3 17 Closed
4 4 false
4 12 false
4 13 0.00
4 16 blahblah
Here's what I want it to look like:
AA ID_X Prompt Value
1 1 Type Classification NULL
1 2 Land Type NULL
1 3 Property Code NULL
1 4 RRSP Status False
1 12 Performing False
1 13 Tax Payment 0.00
1 14 Income Producing NULL
1 15 Interest Adjust NULL
1 16 Prepayment Option Open w/ 30 days notice
1 17 Prepayment Status Open
1 18 Loan Name NULL
2 1 Type Classification NULL
2 2 Land Type NULL
2 3 Property Code NULL
2 4 RRSP Status False
2 12 Performing False
2 13 Tax Payment 0.00
2 14 Income Producing NULL
2 15 Interest Adjust NULL
2 16 Prepayment Option Open w/ 30 days notice
2 17 Prepayment Status Open
2 18 Loan Name NULL
I basically want to have all the recordes from Table A and join B to it with NULL or blank filled into the Value field where there's no match on ID_X.
Table A - Cross reference table for ID_X. I know the ID_X column isn't consecutive numbers.
ID_X Prompt
1 Type Classification
2 Land Type
3 Property Code
4 RRSP Status
12 Performing
13 Tax Payment
14 Income Producing
15 Interest Adjust
16 Prepayment Option
17 Prepayment Status
18 Loan Name
TABLE B
AA Id_X Value
1 4 false
1 12 false
1 13 0.00
1 16 Open w/ 30 days notice
1 17 Open
2 4 false
2 12 false
2 13 0.00
2 16 Open w/ 30 days notice
2 17 Open
3 4 false
3 12 false
3 13 0.00
3 16 Closed. YM will apply
3 17 Closed
4 4 false
4 12 false
4 13 0.00
4 16 blahblah
Here's what I want it to look like:
AA ID_X Prompt Value
1 1 Type Classification NULL
1 2 Land Type NULL
1 3 Property Code NULL
1 4 RRSP Status False
1 12 Performing False
1 13 Tax Payment 0.00
1 14 Income Producing NULL
1 15 Interest Adjust NULL
1 16 Prepayment Option Open w/ 30 days notice
1 17 Prepayment Status Open
1 18 Loan Name NULL
2 1 Type Classification NULL
2 2 Land Type NULL
2 3 Property Code NULL
2 4 RRSP Status False
2 12 Performing False
2 13 Tax Payment 0.00
2 14 Income Producing NULL
2 15 Interest Adjust NULL
2 16 Prepayment Option Open w/ 30 days notice
2 17 Prepayment Status Open
2 18 Loan Name NULL
I basically want to have all the recordes from Table A and join B to it with NULL or blank filled into the Value field where there's no match on ID_X.
ASKER
Thanks for the quick reply. Unfortunately that query only gets me the records with values :(
AA ID_X Prompt Value
1 4 RRSP Status false
1 12 Performing false
1 13 Tax Payment 0.00
1 16 Prepayment Option Open w/ 30 days notice
1 17 Prepayment Status Open
2 4 RRSP Status false
2 12 Performing false
2 13 Tax Payment 0.00
2 16 Prepayment Option Open w/ 30 days notice
2 17 Prepayment Status Open
3 4 RRSP Status false
3 12 Performing false
3 13 Tax Payment 0.00
3 16 Prepayment Option Closed. YM will apply
3 17 Prepayment Status Closed
4 4 RRSP Status false
4 12 Performing false
4 13 Tax Payment 0.00
4 16 Prepayment Option Open after 6 months w/ 30 days notice
AA ID_X Prompt Value
1 4 RRSP Status false
1 12 Performing false
1 13 Tax Payment 0.00
1 16 Prepayment Option Open w/ 30 days notice
1 17 Prepayment Status Open
2 4 RRSP Status false
2 12 Performing false
2 13 Tax Payment 0.00
2 16 Prepayment Option Open w/ 30 days notice
2 17 Prepayment Status Open
3 4 RRSP Status false
3 12 Performing false
3 13 Tax Payment 0.00
3 16 Prepayment Option Closed. YM will apply
3 17 Prepayment Status Closed
4 4 RRSP Status false
4 12 Performing false
4 13 Tax Payment 0.00
4 16 Prepayment Option Open after 6 months w/ 30 days notice
try this
Select A.AA , A.ID_X, [Prompt], [Value]
from
(Select * from TableA cross join (Select distinct AA from TableB)) A left outer join TableB on A.ID_X = TableB.ID_X and A.AA = TableB.AA
Select A.AA , A.ID_X, [Prompt], [Value]
from
(Select * from TableA cross join (Select distinct AA from TableB)) A left outer join TableB on A.ID_X = TableB.ID_X and A.AA = TableB.AA
try? full outer join
Select AA , TableA.ID_X, [Prompt], [Value]
from TableA
full outer join TableB on TableA.ID_X = TableB.ID_X
small mistake in my last post, you need to add alias
Select A.AA , A.ID_X, [Prompt], [Value]
from
(Select * from TableA cross join (Select distinct AA from TableB) B ) A left outer join TableB on A.ID_X = TableB.ID_X and A.AA = TableB.AA
Select A.AA , A.ID_X, [Prompt], [Value]
from
(Select * from TableA cross join (Select distinct AA from TableB) B ) A left outer join TableB on A.ID_X = TableB.ID_X and A.AA = TableB.AA
ASKER
none of these work guys :(
try swapping "order of tables" in either maybe, a cross join should produce "everything"
what is the output of the triels?
& can you paste into a "code" (see small ribbon above text box)
I can't see through this window
what is the output of the triels?
& can you paste into a "code" (see small ribbon above text box)
I can't see through this window
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
this is not yet right, I believe the answer is a left join from B to A (duh) - I'm working on it
with sample as (
select
table_b.AA, table_a.ID_X, table_a.Prompt, table_b.Value_str
from (
select
inr_a.ID_X, inr_a.Prompt
from (
select 1 as ID_X, 'Type Classification' as Prompt union all
select 2 as ID_X, 'Land Type' as Prompt union all
select 3 as ID_X, 'Property Code' as Prompt union all
select 4 as ID_X, 'RRSP Status' as Prompt union all
select 12 as ID_X, 'Performing' as Prompt union all
select 13 as ID_X, 'Tax Payment' as Prompt union all
select 14 as ID_X, 'Income Producing' as Prompt union all
select 15 as ID_X, 'Interest Adjust' as Prompt union all
select 16 as ID_X, 'Prepayment Option' as Prompt union all
select 17 as ID_X, 'Prepayment Status' as Prompt union all
select 18 as ID_X, 'Loan Name' as Prompt
) inr_a
) as table_A
left outer join (
select
inr_b.AA, inr_b.Id_X, inr_b.Value_str
from (
select 1 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 1 as AA, 13 as Id_X, '0' as Value_str union all
select 1 as AA, 16 as Id_X, 'Open w/ 30 days notice' as Value_str union all
select 1 as AA, 17 as Id_X, 'Open' as Value_str union all
select 2 as AA, 4 as Id_X, 'FALSE' as Value_str union all
select 2 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 2 as AA, 13 as Id_X, '0' as Value_str union all
select 2 as AA, 16 as Id_X, 'Open w/ 30 days notice' as Value_str union all
select 2 as AA, 17 as Id_X, 'Open' as Value_str union all
select 3 as AA, 4 as Id_X, 'FALSE' as Value_str union all
select 3 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 3 as AA, 13 as Id_X, '0' as Value_str union all
select 3 as AA, 16 as Id_X, 'Closed. YM will apply' as Value_str union all
select 3 as AA, 17 as Id_X, 'Closed' as Value_str union all
select 4 as AA, 4 as Id_X, 'FALSE' as Value_str union all
select 4 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 4 as AA, 13 as Id_X, '0' as Value_str union all
select 4 as AA, 16 as Id_X, 'blahblah' as Value_str
) inr_b
) as table_B on table_A.id_x = table_b.id_x
)
select
*
from sample
where AA is not null
order by aa, id_x
you need a "left outer join" from larger table to smaller table...
select
table_b.AA, table_b.prompt, table_a.*
from table_b
left outer join on table_a ON table_b.id_x = table_b.id_xwith sample as (
select
table_b.AA, table_b.ID_X, table_a.Prompt, table_b.Value_str
from (
select
inr_b.AA, inr_b.Id_X, inr_b.Value_str
from (
select 1 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 1 as AA, 13 as Id_X, '0' as Value_str union all
select 1 as AA, 16 as Id_X, 'Open w/ 30 days notice' as Value_str union all
select 1 as AA, 17 as Id_X, 'Open' as Value_str union all
select 2 as AA, 4 as Id_X, 'FALSE' as Value_str union all
select 2 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 2 as AA, 13 as Id_X, '0' as Value_str union all
select 2 as AA, 16 as Id_X, 'Open w/ 30 days notice' as Value_str union all
select 2 as AA, 17 as Id_X, 'Open' as Value_str union all
select 3 as AA, 4 as Id_X, 'FALSE' as Value_str union all
select 3 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 3 as AA, 13 as Id_X, '0' as Value_str union all
select 3 as AA, 16 as Id_X, 'Closed. YM will apply' as Value_str union all
select 3 as AA, 17 as Id_X, 'Closed' as Value_str union all
select 4 as AA, 4 as Id_X, 'FALSE' as Value_str union all
select 4 as AA, 12 as Id_X, 'FALSE' as Value_str union all
select 4 as AA, 13 as Id_X, '0' as Value_str union all
select 4 as AA, 16 as Id_X, 'blahblah' as Value_str
) inr_b
) as table_b
left outer join (
select
inr_a.ID_X, inr_a.Prompt
from (
select 1 as ID_X, 'Type Classification' as Prompt union all
select 2 as ID_X, 'Land Type' as Prompt union all
select 3 as ID_X, 'Property Code' as Prompt union all
select 4 as ID_X, 'RRSP Status' as Prompt union all
select 12 as ID_X, 'Performing' as Prompt union all
select 13 as ID_X, 'Tax Payment' as Prompt union all
select 14 as ID_X, 'Income Producing' as Prompt union all
select 15 as ID_X, 'Interest Adjust' as Prompt union all
select 16 as ID_X, 'Prepayment Option' as Prompt union all
select 17 as ID_X, 'Prepayment Status' as Prompt union all
select 18 as ID_X, 'Loan Name' as Prompt
) inr_a
) as table_a on table_b.id_x = table_a.id_x
)
select
*
from sample
order by aa, id_x
output is
AA ID_X Prompt Value_str
----- ------- ----------------- ----------------------
1 12 Performing FALSE
1 13 Tax Payment 0
1 16 Prepayment Option Open w/ 30 days notice
1 17 Prepayment Status Open
2 4 RRSP Status FALSE
2 12 Performing FALSE
2 13 Tax Payment 0
2 16 Prepayment Option Open w/ 30 days notice
2 17 Prepayment Status Open
3 4 RRSP Status FALSE
3 12 Performing FALSE
3 13 Tax Payment 0
3 16 Prepayment Option Closed. YM will apply
3 17 Prepayment Status Closed
4 4 RRSP Status FALSE
4 12 Performing FALSE
4 13 Tax Payment 0
4 16 Prepayment Option blahblah
PortletPaul: that gives similar results as the query i posted in my first post. OP wants the results as in my comment https://www.experts-exchange.com/questions/28031402/SQL-T-SQL-JOIN-Question-All-records-from-one-side.html?anchorAnswerId=38887955#a38887955
Closest I can get is using full outer join, should be like below
Select
TableB.AA
, TableB.ID_X
, [Prompt]
, [Value]
from TableB
full outer join TableA on TableB.ID_X = TableA.ID_X
order by
TableB.AA
, TableB.ID_X AA ID_X Prompt Value_str
------ ------- ------------------- ----------------------
(null) 18 Loan Name (null)
(null) 15 Interest Adjust (null)
(null) 14 Income Producing (null)
(null) 3 Property Code (null)
(null) 2 Land Type (null)
(null) 1 Type Classification (null)
1 17 Prepayment Status Open
1 16 Prepayment Option Open w/ 30 days notice
1 13 Tax Payment 0
1 12 Performing FALSE
2 17 Prepayment Status Open
2 16 Prepayment Option Open w/ 30 days notice
2 13 Tax Payment 0
2 12 Performing FALSE
2 4 RRSP Status FALSE
3 17 Prepayment Status Closed
3 16 Prepayment Option Closed. YM will apply
3 13 Tax Payment 0
3 12 Performing FALSE
3 4 RRSP Status FALSE
4 16 Prepayment Option blahblah
4 13 Tax Payment 0
4 12 Performing FALSE
4 4 RRSP Status FALSE ASKER
Spendid. Can you suggest some good resources you've used to learn SQL?
Select AA , TableA.ID_X, [Prompt], [Value]
from TableA left outer join TableB on TableA.ID_X = TableB.ID_X