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
July 12, 2015, 7:09pm
2
In the application language use a conditional to change the query.
r937
July 12, 2015, 8:06pm
3
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
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 -
if table1.type = 1 → I want join to table2
if table1.type = 2 → I want join to table3
r937
July 12, 2015, 9:26pm
5
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 -
if table1.type = 1 → I want join to table2
if table1.type = 2 → I want join to table3
okay, fine… join on which columns?
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
July 12, 2015, 11:36pm
7
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
Many thanks, r937!
Excellent.
Have a nice day!
system
Closed
October 12, 2015, 1:31pm
9
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.