Reputation: 3510
I have a query that looks like:
SELECT 'asdf', '123' ...
FROM table1
LEFT JOIN table2
on
(
condition1
)
LEFT JOIN table3
on
(
condition2
)
where
(
main_condition
)
Now the problem is, I need to conditionally include table1
as well. I tried this:
..
..
FROM table1
on
(
new_condition
)
..
..
but it wouldn't work. Please help.
EDIT (New finding): In this post (http://blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/), I found this piece of code:
SELECT 1 as i, f.bar, f.jar FROM dual LEFT JOIN foo AS f on f.bar = 1 WHERE dual.dummy = ‘X’
UNION
SELECT 2 as i, f.bar, f.jar FROM dual LEFT JOIN foo AS f on f.bar = 2 WHERE dual.dummy = ‘X’
I'm sure it's not directly related to what I'm trying to do, but is it possible to JOIN
a table to DUAL
like that?
Upvotes: 2
Views: 2701
Reputation: 35353
My best guess with comments to date.
SELECT 'asdf', '123' ...
FROM table1
FULL OUTER JOIN table2 --NOTE THE FULL OUTER here all records in table 2 and only those that match in table 1
on
condition1 AND
new_condition=True
LEFT JOIN table3
on
(
condition2
)
where
(
main_condition
)
Upvotes: 0
Reputation: 3510
Thanks for contributing to the discussion. I found the answer. It's really simple:
SELECT temp_table.* FROM
(SELECT 'asdf', '123' ... FROM DUAL) temp_table
LEFT JOIN table1
on
(
new_condition
)
LEFT JOIN table2
on
(
condition1
)
LEFT JOIN table3
on
(
condition2
)
where
(
main_condition
)
Interesting problem. Maybe I should favorite my own question this time :)
Upvotes: 2
Reputation: 116200
Dummy table:
Select a record from a dummy table first. dual
is such a table, that is built in in MySQL for this exact purpose. I wrapped dual
in a subselect, because MySQL apparently doesn't allow left joining against it.
SELECT 'asdf', '123' ...
FROM
(select 1 from dual) d
LEFT JOIN table1
on(
new_condition
)
LEFT JOIN table2
on
(
condition1
)
LEFT JOIN table3
on
(
condition2
)
Full (outer) join
Another solution, though different is using a full join
or full outer join
, which is like a left join
and right join
combined. It is quite different, though you can achieve a very similar result:
select
*
from
table1
full outer join table2 on joincondition.
In the query above, all records from both tables are returned, even if no matching record in either table exists.
Upvotes: 3
Reputation: 37243
you cant make this new condition in ON clause
on clause is just when you join, but you can add this new condition in where clause
example
where
(
main_condition
)
AND
(
new condition
)
EDIT:
try this
SELECT 'asdf', '123' ...
FROM (select 'asdf', '123' ... FROM table1 WHERE new_condition ) t
^^--your new condition here
LEFT JOIN table2
on
........
EDIT2: if your new condition can be wrong you can make an if statment
where
(
main_condition
)
AND
(
if(new condition is something , do something , else do something else)
)
edit3:
SELECT 'asdf', '123' ...
FROM (select 'asdf', '123' ... FROM table1 where main condition
UNION
select 'asdf', '123' ... FROM table1 WHERE new_condition ) t
^^--your new condition here
LEFT JOIN table2
on
........
Upvotes: 0
Reputation: 1271231
You need to include the condition in the on
clause for the first join:
SELECT 'asdf', '123' ...
FROM table1 LEFT JOIN
table2
on condition1 AND new condition LEFT JOIN
table3
on condition2
where main_condition
When using a where
clause with left join
be careful. Normally, you want to move these conditions into the on
clauses, because they can inadvertently undo the effect of the left outer join (turning it into an inner join
).
Upvotes: 0