dragone wrote:
> The query (in sql server) is
>
> select distinct …..
> from
> ((( C INNER JOIN D ON C.c1=D.d1) RIGHT JOIN B ON
> C.c2 =B.b1 ) RIGHT JOIN A ON B.b2=A.a1)
> where …;
I tend not to use RIGHT JOIN, in favor of LEFT JOIN (preference, really). You query can be rewritten as:
SELECT DISTINCT
FROM A
LEFT JOIN B
ON A.a1=C.b2
LEFT JOIN C
ON B.b1=C.c2
INNER JOIN D
ON C.c1=D.d1
WHERE …;
Please post an EXPLAIN SELECT of the above. Also, question: was your code auto-generated from a SQL Server tool?
>
> I have tree main table A,B,C
> Every table has a key that corresponds to a key in
> another table(a foreign key)
> For example A.a1=B.b2.
>
> The problem is that if there isn't a value in C
> that corresponds to a value in B the result is
> null and the query doesn't work well.
> I would like that the query returns the lines
> even if there is no corrispondence in B and C
The definition of an outer join (LEFT or RIGHT) is that NULL values will be returned for non-matching rows. I'm a little unclear why the above query (te rewritten one) won't meet your needs. Could you expand a little on your requirements?
thanks!
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com