If statment for join's?

[sym0ng]

Hello!

I've been trying to search for a resolution to this but can't see anything, so i must be being thick!

What I want to do is do a different join based on the result something like this:

SELECT * FROM associates
 
    IF associates = 1 
           INNER JOIN table1 ON (table1.id = associates.jointblID )
    ELSE 
           INNER JOIN table2 ON (table2.id = associates.jointtblID )
 
WHERE associatesID = X

Is that possible to do in one query?

Thanks :)

[sierra7]

I'm a Newbie to MySQL but I would not attempt an 'IF' in any SQL statement (so shall watch to see if I can learn something!)

I think you need a 'UNION' query where you combine the results of two seperate SELECT statement, each with their own conditions.

Both queries must return the same number of columns, so you may need to add dummy columns to one query.

Your example does not call for any fields (columns) from Table1 or Table2, so is pointless as it stands.

I shall watch with interest for an Expert response.

[sym0ng]

Yeh, to give more details is always helpful, here goes :)

I have 3 tables like this:

table1

ID, type, typeID
 
1   1    27
2   1    13 
3   2    89
 

table2

ID, name, x, x, x, x, x, x, x, x, x
1   xyz, x, x, x, x, x, x, x, x, x
2   zyx, x, x, x, x, x, x, x, x, x

table3

ID, name, x
1   xyz, x, 
2   zyx, x,

My actual select would be like this

SELECT table1.ID, table1.type, table1.typeID, tableX.name FROM table1, (then either join table2 or table3 as tableX matching table2/3 ID with table1.typeID)

I currently have the above working via PHP, but doing 2 queries, my aim of this is more trying to cut the queries of my whole website down by doing things like the above.

[sym0ng]

SELECT table1.type, table1.typeID,
CASE table1.type
    WHEN '1' THEN (SELECT name FROM table2 WHERE table2.ID=table1.typeID)
    WHEN '2' THEN (SELECT name FROM table3 WHERE table3.ID=table1.typeID)
END    AS name
 
FROM table1
 

Ok, solved it I think, using the CASE statement above.

Is that a good way to do it?

Thanks

[amitpatel66]

SELECT table1.type, table1.typeID,
CASE table1.type
    WHEN '1' THEN (SELECT name FROM table2 WHERE table2.ID=table1.typeID)
    WHEN '2' THEN (SELECT name FROM table3 WHERE table3.ID=table1.typeID)
END    AS name
 
FROM table1
 

Ok, solved it I think, using the CASE statement above.

Is that a good way to do it?

Thanks

yes, if it is giving you the desired result and the query performance is good then your solution is good.