SQL Server - Combining OUTER and INNER joins

Question

I have 3 tables and I have to inner join Table A with Table B but a left outer between Table A and Table C.

Can I combine outer and inner join in the same query? I can nest queries and attain the desired result but I am unable to do both the joins in the same query. It appears that in other SQL languages, the order of joining is important. Is this the case in SQL Server as well?

Answer 1

You can use both inner and outer joins in the same query, but their order is important. See this question:
http://stackoverflow.com/questions/187146/inner-join-outer-join-is-the-order-of-tables-in-from-important

Answer 2

Sure you can do the join in the same query:-

FROM TableA a
INNER JOIN Table b ON a.TableA_ID = b.TableA_ID
LEFT OUTER JOIN Table c ON a.TableA_ID = c.TableA_ID

Answer 3

Yes you can do both is the same query, and yes the order is important.

Answer 4

In this example:

FROM TableA a INNER JOIN Table b ON a.TableA_ID = b.TableA_ID LEFT OUTER JOIN Table c ON a.TableA_ID = b.TableA_ID

It does the INNER JOIN first before doing a LEFT OUTER JOIN. But I want it the other way around, I need it to first do a LEFT OUTER and then a INNER. If switch the join conditions, the result set is the same.

Answer 5

If I understand correctly you want something like this:

select
    *
from
    a 
    left outer join c
     inner join b on c.bID = b.ID
    on a.cID = c.ID

Answer 6

From your follow-up, it sounds like you want a 'conditional' inner join.

Essentially, an "If A and B have a record, INNER JOIN to C".

However, you are likely running into the problem where the INNER JOIN in your query is not showing records where A has no records associated to B or C. If they are at the same 'scope', the INNERS will always run, you can't conditionally have them run based on their order.

You either have to use two LEFT joins and filter out the records you don't want, or alternatively use a View to scope the INNER JOIN.

Ex. A LEFT JOIN vw_MyView ON A.ID=vw_MyView.A_ID

Where MyView has tables B and C with your INNER JOIN. This will allow the INNER JOIN to be run inside of the view, and then you can LEFT JOIN to the results.

Answer 7

The problem may not specifically be the join (Anthony showed you how to do wht you described to us).Remember that people often have problems using left joins becasue they try to put something in the where clause referncing the table on the right side of the join thus converting it from an outer join to an inner join (unless you are looknig for those records where the second table field is null which gives you the records in the first table and not the secodn one).

We could help you better if we say the actual code you were using that wasn;t producting the desired results as well as some sample data and sample results.

Answer 8

Ok, here's the scenario.

Consider 3 tables. Table A, Table F, Table D.

I will need the recordset to contain all rows in D irrespective of whether it exists in F (after it's inner joined with A). So, a outer join comes to mind. What I would need is:

1. First do a inner join between A and F to get a set (this may be a null set)
2. Then do a outer join with the recordset in (1) with D