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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

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

Post 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?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Try putting a LIMIT on the query and see if it's working.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

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

Post 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)
Post Reply