Simple SQL JOIN question?

kbc · 6 May 2011 at 13:04

uv · 6 May 2011 at 13:14

Code:

select  b.sample_no,
        a.test_no,
        a.description,
        b.result_description
from    table_A a
left    join table_B b on a.test_no = b.test_no;

kbc · 6 May 2011 at 14:25

That works if I don't pass a filter to Table B.

Am I structuring the whole query wrong? As I need to pass a Sample No to Table B.

Not focusing! - Just needed a NULL in the condition

kbc · 6 May 2011 at 16:29

OK. I still couldn't get it working:

Table_1
[Group No] [Test No] [Description]
123 1 [First Test]
123 2 [Second Test]
123 3 [Third Test]

Table_2
[Sample No] [Test No] [Result Description]
ABC 1 [Some More Result]
ABC 3 [Some Result]
DEF 1 [A Result]
DEF 2 [Results More]
DEF 3 [Bad Results]

SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1 LEFT OUTER JOIN
Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123') AND (Table_2.[Sample No] = 'ABC')

Returns

[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 3 [Third Test] [Some Result]

But what I really want is this:

[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 2 [Second Test] NULL
123 3 [Third Test] [Some Result]

Am I doing this correctly??? Is this structured correct? :confused:

fez · 6 May 2011 at 17:05

You are telling it that table_2 sample number must be 'ABC' which test 2 doesnt have. Look at table 1 and 2 and you can see that the only test_no 2 in table_2 is holding the value 'DEF'. So although you would normally get all the results you wanted with a set of nulls for non corresponding records, you have specified that you only want to take rows in table_2 into account if they have a sample_no of 'ABC'.

topdog@OC · 9 May 2011 at 01:36

If you have to pass in a filter to the where clause to let a sample be selected, you'd need something like uv's query (fixed to display group instead of sample in the first column) with this where clause on it:

Code:

select  a.group_no,
        a.test_no,
        a.description,
        b.result_description
from    table_A a
left    join table_B b on a.test_no = b.test_no
where b.sample_no = 'ABC' or b.sample_no is null

Note that your parenthesis around your own where clause filters are unnecessary/redundant, they're not actually doing anything. If you need to add further clauses and logic to the query above, the way you'd parenthesis the where clause to prevent the logic from breaking is around both the related filters in one pair like:

Code:

...
where (b.sample_no = 'ABC' or b.sample_no is null)
and {... some other clause here ...}

If you're going to allow both the sample AND the group be input as filters, so that it only shows the desired group (but all records from that group regardless of whether they have samples), then you'd do this:

Code:

select  a.group_no,
        a.test_no,
        a.description,
        b.result_description
from    table_A a
left    join table_B b on a.test_no = b.test_no
where (b.sample_no = 'ABC' or b.sample_no is null)
and (a.group_no = '123' or '123' is null)

In a query like this, I would assume the constant values 'ABC' and '123' would be replaced with bind variables, which is how the end part "... '123' is null ..." makes sense, because if no filter was provided at all, you'd want the query to return all groups. No filter generally means passing null as the parameter value, and 'null is null' is a matching criteria (never do 'null = null').

I.e. in a language and SQL API (jdbc, etc.) that supports prepared statements with bind variables, the query would look like this unless you wanted to just detect that no filter was provided for group and so to modify the adhoc sql to exclude that clause.. which way you do it simply is a matter of preference and code readability:

Code:

select  a.group_no,
        a.test_no,
        a.description,
        b.result_description
from    table_A a
left    join table_B b on a.test_no = b.test_no
where (b.sample_no = ? or b.sample_no is null)
and (a.group_no = ? or ? is null)

AshenShugar1873 · 13 May 2011 at 12:12

SELECT dbo.Table_1.GroupNo, dbo.Table_1.TestNo, dbo.Table_1.Description, B.ResultDescription
FROM dbo.Table_1 LEFT OUTER JOIN
(SELECT SampleNo, TestNo, ResultDescription
FROM dbo.Table_2
WHERE (SampleNo = 'ABC')) AS B ON dbo.Table_1.TestNo = B.TestNo
WHERE (dbo.Table_1.GroupNo = N'123')

You need to filter the second table before applying the Left Join.

Dj_Jestar · 13 May 2011 at 13:38

Code:

SELECT table1.*, table2.* 
  FROM table1
  LEFT JOIN table2
    ON table1.foo = table2.bar

Will return everything from table1, and only rows from table2 where the columns table1.foo and table2.bar are equal.

Every row in the result will have columns from table2, but those without rows on table2 will be null. E.g.

Code:

t1.a t1.b t2.a t2.b
====================
1    2    3    4
5    6    null null

As for filtering, avoid nested select, and just use:

Code:

ON table1.foo = table2.bar AND table2.baz = 'aValue'

AshenShugar1873 · 13 May 2011 at 15:13

Dj_Jestar said:
Code:

SELECT table1.*, table2.* FROM table1 LEFT JOIN table2 ON table1.foo = table2.bar

Will return everything from table1, and only rows from table2 where the columns table1.foo and table2.bar are equal.

Every row in the result will have columns from table2, but those without rows on table2 will be null. E.g.

Code:

t1.a t1.b t2.a t2.b ==================== 1 2 3 4 5 6 null null

As for filtering, avoid nested select, and just use:

Code:

ON table1.foo = table2.bar AND table2.baz = 'aValue'

That doesn't work, he is filtering on both tables, if you filter after the Left Join you miss out the rows in Table 1 that don't match in Table 2. See post 4 where he is missing 1 record using SQL similar to your post. When using (AND table2.baz = 'aValue') you lose the NULL records in table 2 that were included because of the left join.

AshenShugar1873 · 13 May 2011 at 15:16

In fact you could even do this and get all your filtering done early before you do the join.

SELECT a.GroupNo, a.TestNo, a.Description, B.ResultDescription
FROM (SELECT GroupNo, TestNo, Description
FROM dbo.Table_1
WHERE (GroupNo = '123')) AS a LEFT OUTER JOIN
(SELECT SampleNo, TestNo, ResultDescription
FROM dbo.Table_2
WHERE (SampleNo = 'ABC')) AS B ON a.TestNo = B.TestNo

Best to reduce the recordsets then do the joins. Get your WHERE clauses in early if you can.

Dj_Jestar · 13 May 2011 at 15:22

AshenShugar1873 said:
That doesn't work, he is filtering on both tables, if you filter after the Left Join you miss out the rows in Table 1 that don't match in Table 2. See post 4 where he is missing 1 record using SQL similar to your post. When using (AND table2.baz = 'aValue') you lose the NULL records in table 2 that were included because of the left join.

No you don't. It's not an inner join, it's a left join. No matter what conditions you place on the join, it will always return everything from the left table (table1 in my example) and only those which match the condition from the right table (table2 in my example).

I use this all the time.

Nested selects over joins? No thanks.

edit:

OP wants:

Code:

SELECT TableA.*, TableB.* 
  FROM TableA 
  LEFT JOIN TableB
    ON TableA.GroupNo = TableB.TestNo AND TableB.SampleNo = 123
  WHERE TableA.GroupNo = 'ABC'

AshenShugar1873 · 13 May 2011 at 15:29

Dj_Jestar said:
No you don't. It's not an inner join, it's a left join. No matter what conditions you place on the join, it will always return everything from the left table (table1 in my example) and only those which match the condition from the right table (table2 in my example).

I use this all the time.

Nested selects over joins? No thanks.

Sorry, didn't see you had appended the filter to the join rather than the WHERE clause, I agree that will work but I have to say it's not my preference; nested selects over filtered joins for me

Do you prefer for readability, performance, other reasons?

Dj_Jestar · 13 May 2011 at 15:30

AshenShugar1873 said:
Sorry, didn't see you had appended the filter to the join rather than the WHERE clause, I agree that will work but I have to say it's not my preference; nested selects over filtered joins for me Do you prefer for readability, performance, other reasons?

Readability and performance. Modern DBMS prefer joins to nested selects. (Even though they translate them to nested selects, they are much better geared to optimising them when presented as joins.)