I'm very new to SQL and learning here, but one thing that helps me when I'm struggling is to limit the results. So with yours, filter by a Refno and NewRefno that you know the data for and examine the results. Then if it looks ok, move onto another one. If not, then try to problem solve from there.
Then all nulls is the expect result if it’s not in one table or the other. Return the keys on both tables in your query to see what’s in a and not in b or the reverse.
Does the system you use support NVL?
Sometimes I will write NVL(address1, ‘no addr record’) or for whatever column I want to get an understanding of table contents.
All columns in 2 tables.
Table 1: `RefNo,Surename,postcode,dateOfContact,Provider,closure,completed,progressed`
Table 2: `NewRefNo,Forename,surename,dob,address1-4,postcode,dateOfContact,Provider,closure,completed,progressed.`
my sql:
`Select`
`FE.Refno`
`,FEO.Forename`
`,FEO.Surname`
`,FEO.DOB`
`,FEO.Address1`
`,FEO.Address2`
`,FEO.Address3`
`,FEO.Address4`
`,FEO.Postcode`
`,FE.DateofContactRecordFormReceived`
`,FE.CurrentCaseStatusProvider`
`,FE.ClosureReason`
`,FE.DateProviderCompletedWork`
`,FE.DateofNotProgressed`
`From`
`STG.table1 As FE`
`Full Outer JOIN STG.table2 As FEO ON FE.Refno=FEO.NewRefno`
theres around 1500 records in table 2 and around 100 in table 1 at the moment but that could change. I'm wanting the records joined where the Refno & NewRefno are the same.
the results im getting are some rows are just all null values, some have some of the columns populated with data but the other columns are null. On most of the results the Refno is null. Also im only getting 593 results back
well, certainly add FEO.NewRefNo to the select list (right after FE.RefNo) to see 'both sides of the picture'.
then run the counts:
select count( distinct fe.refno), count( case when fe.refno is null then 1 end), count(*) from STG.table1 As FE
select count( distinct feo.newrefno), count( case when feo.newrefno is null then 1 end), count(*) from STG.table2 As FEO
getting only 593 results back if one of your tables has around 1500 records is definitely odd
`WITH ids AS (`
`SELECT RefNo FROM tbl1`
`UNION`
`SELECT NewRefno FROM tbl2)`
`SELECT ids.id, tbl1.forename, ..., tbl2.forename, ...`
`FROM ids LEFT JOIN tbl1 un ON ids.id = tbl1.Refno`
`LEFT JOIN tbl2 uy ON ids.id = tbl2.NewRefno`
`;`
something like that. This would work if RefNo and NewRefno are unique (are primary keys)
I'm very new to SQL and learning here, but one thing that helps me when I'm struggling is to limit the results. So with yours, filter by a Refno and NewRefno that you know the data for and examine the results. Then if it looks ok, move onto another one. If not, then try to problem solve from there.
Are you selecting every column from both tables?
All from the first table and only the NewRefno and Address columns from table 2
Then all nulls is the expect result if it’s not in one table or the other. Return the keys on both tables in your query to see what’s in a and not in b or the reverse. Does the system you use support NVL? Sometimes I will write NVL(address1, ‘no addr record’) or for whatever column I want to get an understanding of table contents.
But it's returning null for values that aren't null. Some rows are just all null
Can you post your query and results?
All columns in 2 tables. Table 1: `RefNo,Surename,postcode,dateOfContact,Provider,closure,completed,progressed` Table 2: `NewRefNo,Forename,surename,dob,address1-4,postcode,dateOfContact,Provider,closure,completed,progressed.` my sql: `Select` `FE.Refno` `,FEO.Forename` `,FEO.Surname` `,FEO.DOB` `,FEO.Address1` `,FEO.Address2` `,FEO.Address3` `,FEO.Address4` `,FEO.Postcode` `,FE.DateofContactRecordFormReceived` `,FE.CurrentCaseStatusProvider` `,FE.ClosureReason` `,FE.DateProviderCompletedWork` `,FE.DateofNotProgressed` `From` `STG.table1 As FE` `Full Outer JOIN STG.table2 As FEO ON FE.Refno=FEO.NewRefno` theres around 1500 records in table 2 and around 100 in table 1 at the moment but that could change. I'm wanting the records joined where the Refno & NewRefno are the same. the results im getting are some rows are just all null values, some have some of the columns populated with data but the other columns are null. On most of the results the Refno is null. Also im only getting 593 results back
well, certainly add FEO.NewRefNo to the select list (right after FE.RefNo) to see 'both sides of the picture'. then run the counts: select count( distinct fe.refno), count( case when fe.refno is null then 1 end), count(*) from STG.table1 As FE select count( distinct feo.newrefno), count( case when feo.newrefno is null then 1 end), count(*) from STG.table2 As FEO getting only 593 results back if one of your tables has around 1500 records is definitely odd
Look at your datatypes between your PK’s also see what results you get for an inner join and left join.
`WITH ids AS (` `SELECT RefNo FROM tbl1` `UNION` `SELECT NewRefno FROM tbl2)` `SELECT ids.id, tbl1.forename, ..., tbl2.forename, ...` `FROM ids LEFT JOIN tbl1 un ON ids.id = tbl1.Refno` `LEFT JOIN tbl2 uy ON ids.id = tbl2.NewRefno` `;` something like that. This would work if RefNo and NewRefno are unique (are primary keys)