MySQL Forums
Forum List  »  Microsoft SQL Server

Re: Right join in mysql and sqlserver
Posted by: Jay Pipes
Date: February 08, 2006 03:56PM

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

Options: ReplyQuote


Subject
Written By
Posted
February 07, 2006 10:02AM
Re: Right join in mysql and sqlserver
February 08, 2006 03:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.