Dynamic query

[Biser_Stoilov]

I have a 3 tables

table1 - id, name, type, typeid
table2 - id, name
table 3 - id, name

I want select from (table1 and table2 - if table1.type = 1) or (table1 and table3 - if table1.type = 2) in one query

select table1.id,
if table1.type = 1 => table2.id, table2.name
elseif table1.type = 2 => table3.id, table3.name
from table1,if table1.type = 1 => table2
elseif table1.type = 2 => table3

Any suggestions?
Many tanks in advamce!

[oddz]

In the application language use a conditional to change the query.

[r937]

try this –

SELECT table1.id , table2.id , table2.name FROM table1 CROSS JOIN table2 WHERE table1.type = 1 UNION ALL SELECT table1.id , table3.id , table3.name FROM table1 CROSS JOIN table3 WHERE table1.type = 2
notice that i’m using CROSS join, which is probably ~not~ what you want… however, you did not specify how table1 should be joined to table2 or table3, so change CROSS JOIN to INNER JOIN and add an ON clause if that’s what you want instead

[Biser_Stoilov]

Thanks for reply, r937!

But I want select only one row from table1 (criteria - current url). This row have table1.type = 1 or 2. And now -

1. if table1.type = 1 → I want join to table2
2. if table1.type = 2 → I want join to table3

[r937]

@Biser_Stoilov

But I want select only one row from table1 (criteria - current url).[/quote]
okay, but you didn’t say which column “current url” will be found in

according to you, table1 has only these columns – id, name, type, typeid

[quote] And now -

1. if table1.type = 1 → I want join to table2
2. if table1.type = 2 → I want join to table3

okay, fine… join on which columns?

[Biser_Stoilov]

table1 - id, name, type, typeid, url
table2 - id, name
table 3 - id, name

My scenario:

SELECT *
FROM table1
WHERE url = ?

When i get id($ID), name($Name), type($Type), typeid($TypeID), url($URL):

if($Type == 1) {
SELECT *
FROM table2
WHERE ID = $TypeID
}
else if($Type == 2) {
SELECT *
FROM table3
WHERE ID = $TypeID
}

This is my scenario, but i want get result with one query. Thanks

[r937]

okay, this will do it –

SELECT table1.id , table1.name , table1.type , table1.typeid , table2.id , table2.name FROM table1 INNER JOIN table2 ON table2.id = table1.typeid WHERE table1.url = ? AND table1.type = 1 UNION ALL SELECT table1.id , table1.name , table1.type , table1.typeid , table3.id , table3.name FROM table1 INNER JOIN table3 ON table3.id = table1.typeid WHERE table1.url = ? AND table1.type = 2

[Biser_Stoilov]

Many thanks, r937!
Excellent.
Have a nice day!