Page 1 of 1

Join with 1 table but join with another if there's a fail

Posted: Wed Jun 20, 2007 4:59 am
by impulse()
I have 4 tables in total, the first two (A & B) will always join. I'm looking for a statement that will join A & B and then try and join those to C but if no records are returned then try and join to table D.

So far I have the statement:

Code: Select all

SELECT A.*, B.*, c.FirstName, c.Surname, c.CompanyName, D.name, D.compName
FROM A as A, B as B, C as C, D as D
WHERE A.uid = B.id
AND A.accmngr = 'name'
AND (A.sageCode = C.cs3
        OR A.sageCode = D.cs3)
But this makes the query hang for a long time although it wouldn't be returning more than a few hundred records.

Am I structuring my statement wrong or is this not possible in 1 single query?

Posted: Wed Jun 20, 2007 6:19 am
by superdezign
Try putting a LIMIT on the query and see if it's working.

Posted: Wed Jun 20, 2007 10:15 am
by ReverendDexter
Is there any reason you can't/shouldn't join all four tables, and just take the information from C.whatever if it's there, and D.whatever if it's not? I know that's a little more overhead, but it should get you the data you need.

Re: Join with 1 table but join with another if there's a fai

Posted: Wed Jun 20, 2007 10:19 am
by ReverendDexter
impulse() wrote:

Code: Select all

SELECT A.*, B.*, c.FirstName, c.Surname, c.CompanyName, D.name, D.compName
FROM A as A, B as B, C as C, D as D
WHERE A.uid = B.id
AND A.accmngr = 'name'
AND (A.sageCode = C.cs3
        OR A.sageCode = D.cs3)
Sorry, I looked at this again - the way this is written you're generating a HUGE cross product, which would explain why it's creating so many rows.

You need to have join conditions for each table past the first in your where clause, i.e.

Code: Select all

SELECT A.*, B.*, c.FirstName, c.Surname, c.CompanyName, D.name, D.compName
FROM A as A, B as B, C as C, D as D
WHERE A.uid = B.id
AND B.compName = C.CompanyName
AND C.CompanyName = D.compName
AND A.accmngr = 'name'
AND (A.sageCode = C.cs3
        OR A.sageCode = D.cs3)