CODEWITHSUNDEEP
mysqlsqljoin
th3an0maly
th3an0maly

Reputation: 3510

Mysql - Left Join all tables

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

Answers (5)

xQbert
xQbert

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

th3an0maly
th3an0maly

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

GolezTrol
GolezTrol

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

echo_Me
echo_Me

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

Gordon Linoff
Gordon Linoff

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

Related Questions