SQL Server Join Problem 3

[MKVAB]

I am fairly new to SQL Server and have run into a problem building some joins. I come from an Oracle background so my first example here is what I want to do if I could use Oracle syntax. My problem is, how do I do this in the FROM clause in SQL Server?

SELECT *
FROM A,
B,
C,
D,
E,
F
WHERE A.x = C.x(+)
AND B.y = C.y(+)
AND A.x = D.x(+)
AND B.y = D.y(+)
AND A.x = E.x(+)
AND B.y = E.y(+)
AND A.x = F.x(+)
AND B.y = F.y(+)

What I'm trying to get is everything in A & B regardless if it there is joining values in C,D,E,F. BUT, if it is in C,D,E or F those tables must join on both A & B.

If it each table (C,D,E,F) was only linking to one other table (such as everything linking back to A) then I believe the joins would be this:

SELECT *
FROM A LEFT JOIN C ON A.x = C.x
LEFT JOIN D ON A.x = D.x
LEFT JOIN E ON A.x = E.x
LEFT JOIN F ON A.x = F.x

But when each table (C,D,E,F) have to left join back to A & B both, I'm not sure how to do that. Something similar to this maybe:

SELECT *
FROM A LEFT JOIN C ON A.x = C.x
AND B LEFT JOIN C ON B.y = C.y
LEFT JOIN D ON A.x = D.x
AND LEFT JOIN D ON B.y = D.y
LEFT JOIN E ON A.x = E.x
AND LEFT JOIN D ON B.y = D.y
LEFT JOIN F ON A.x = F.x
AND LEFT JOIN D ON B.y = D.y


This gets a little hairy because I am actually applying this to a SQL statement built off of several inline views. Here is a skeleton of the actual way I will be using the joins:


/**********************************************
** SELECT FIELDS FROM INLINE VIEWS
**********************************************/
SELECT a.processor,
b.date,
c.processor
c.date,
c.total,
d.processor,
d.date,
d.total,
e.processor,
e.date,
e.total,
f.processor,
f.date,
f.total
FROM
/*******************************************
** -------- INLINE VIEW A --------
*******************************************/
( SELECT distinct processor
FROM processors
) A LEFT JOIN
/*******************************************
** -------- INLINE VIEW B --------
*******************************************/
( SELECT date
FROM dates_table
) B LEFT JOIN
/*******************************************
** -------- INLINE VIEW C --------
*******************************************/
( SELECT COUNT(total) total,
processor,
date
FROM table1
GROUP BY processor,
date
) C
ON B.date = C.date
AND A.processor = C.processor
LEFT JOIN
/*******************************************
** -------- INLINE VIEW D --------
*******************************************/
( SELECT COUNT(total) total,
processor,
date
FROM table2
GROUP BY processor,
date
) D
ON B.date = D.date
AND A.processor = D.processor
LEFT JOIN
/*******************************************
** -------- INLINE VIEW E --------
*******************************************/
( SELECT COUNT(total) total,
processor,
date
FROM table3
GROUP BY processor,
date
) E
ON B.date = E.date
AND A.processor = E.processor
LEFT JOIN
/*******************************************
** -------- INLINE VIEW F --------
*******************************************/
( SELECT COUNT(total) total,
processor,
date
FROM table4
GROUP BY processor,
date
) F
ON B.date = F.date
AND A.processor = F.processor


Any ideas how to get these joins to left join two tables???

Any help is MUCH appreciated!

 

[mwolf00]

Do you join A to B?

If this works in Oracle...

SELECT *
FROM A,
B,
C,
D,
E,
F
WHERE A.x = C.x(+)
AND B.y = C.y(+)
AND A.x = D.x(+)
AND B.y = D.y(+)
AND A.x = E.x(+)
AND B.y = E.y(+)
AND A.x = F.x(+)
AND B.y = F.y(+)

This should work here...
SELECT *
FROM A,
B,
C,
D,
E,
F
WHERE A.x =* C.x
AND B.y =* C.y
AND A.x =* D.x
AND B.y =* D.y
AND A.x =* E.x
AND B.y =* E.y
AND A.x =* F.x
AND B.y =* F.y


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[MKVAB]

Thanks mwolf00, that got me on the right track (didn't know you could do =* in the where clause).

Thanks!
-Mary Klaire

 

[Mdavis123]

This is news to me also.
Thanks
MikeD

 

[MKVAB]

Oh my goodness how my life has been simplified! I do wonder though, is there an performance advantage to doing the joins in the FROM clause as opposed to the WHERE?

All I could really find about it is on MSDN saying:

It is possible to specify outer joins by using the old nonstandard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement.

But I haven't found anything saying which is a better practice, joins in the FROM clause or joins in the WHERE clause?

 

[mwolf00]

There are gurus here that know far more than I do, but the "=*" notation used to be standard SQL (before you could say "LEFT JOIN&quot. I think it may be just as efficient...

You could use the query analyzer to see...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[JohnDTampaBay]

The =* is legacy syntax left over from days gone by for an OUTER JOIN.

From BOL: Using Outer Joins -
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.

Rumor has it that the legacy syntax will not be supported in future versions of SQL.

From BOL: Join Fundamentals -
Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins.

Does A not JOIN to B directly or did I miss something? How about this:

SELECT *
FROM A, B
  LEFT JOIN C ON A.x = C.x
             AND B.y = C.y
  LEFT JOIN D ON A.x = D.x
             AND B.y = D.y
  LEFT JOIN E ON A.x = E.x
            AND B.y = E.y
  LEFT JOIN F ON A.x = F.x
             AND B.y = F.y

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[MKVAB]

Thanks Angel. Although I am sad to hear that the future of the WHERE clause joins is uncertain.

No, A doesn't join to B. For this statement I think I'm going to stick with the *=. But I am going to see if I can get yours to work with all my inline views anyway.


Thanks to all.

-Mary Klaire

 

[r937]

if A does not join B then i assume you want a cross join between A and B? all possible A-B combinations?

[tt]select *
from A
cross
join B
left outer
join C
on A.x = C.x
and B.y = C.y
left outer
join D
on A.x = D.x
and B.y = D.y
left outer
join E
on A.x = E.x
and B.y = E.y
left outer
join F
on A.x = F.x
and B.y = F.y[/tt]

rudy

http://r937.com/

 

[MKVAB]

Angel:
I applied your example to my inline views. For some reason when I join the tables that way table A is not recognized. When compiling the stored procedure I get an error for each ON statement where I am referencing table A:

Server: Msg 107, Level 16, State 2, Procedure sp_DailySmr, Line 76
The column prefix 'processor' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 2, Procedure sp_DailySmr, Line 76
The column prefix 'processor' does not match with a table name or alias name used in the query.
.
.
.

I have checked spelling and syntax, it just does not recognize the first inline view for some reason.

Rudy:
I added your CROSS JOIN to A and B and the procedure compiled! The results with your example are the same as the results with this slightly modified code from mwolf00s example:

SELECT *
FROM A,
B,
C,
D,
E,
F
WHERE A.x *= C.x
AND B.y *= C.y
AND A.x *= D.x
AND B.y *= D.y
AND A.x *= E.x
AND B.y *= E.y
AND A.x *= F.x
AND B.y *= F.y

Problem 100% solved!

I went from not being able to join the views to having 2 ways to do it. Wonderful! Having options make me happy.

Thanks to each of you!
-Mary Klaire