MYSQL SQL statement. Is this conditional query possible?

Question

3I was just wondering if this type of query is even possible. If so, help would be greatly appreciated.

    SELECT 

        field1,
        field2,
        field3

    FROM maintable

    WHERE maintable.field1 = passed_in_parameter


    IF (maintable.field2 = 1) THEN
            //do anything like joins
        INNER JOIN widgettable on widgettable.widgetid = field3

    ELSEIF (maintable.field2 = 2) THEN
            //do anything like joins
        INNER JOIN gizmottable on gizmottable.gizmoid = field3

    END IF

I hope what i am trying to do makes sense. In summary, I need to join different tables based on what a field value is in the original select.

Answer 1

As zerkms suggested, you can left outer join them both. Please see if the following works

select mt.field1,
    mt.field2,
    mt.field3,
    CASE mt.field2 WHEN 1 THEN wt.widgetname WHEN 2 THEN gt.gizmoname END AS name 
from maintable mt
  left join widgettable wt
    on wt.widgetid = mt.field3
  left join gizmotable gt
    on gt.gizmoid = mt.field3
where mt.field1 = 'param'
   and ((mt.field2 = 1 and wt.widgetid is not null)
       or (mt.field2 = 2 and gt.gizmoid is not null))

Answer 2

That sounds a bit like a UNION query, except you've not specified that you want to see any data from the joined tables. That means that the inner joins are simple existence checks; they check that there is a row in the joined table that matches the row in the main table. I've taken the 'radical' step of assuming you want a couple of columns from the joined tables; you can omit those joined columns without seriously altering the overall effect. This UNION query assumes that the columns you need from WidgetTable and GizmotTable are sufficiently similar. Then you can write:

SELECT m.field1,
       m.field2,
       m.field3,
       w.column1,
       w.column3
  FROM maintable   AS m
  JOIN widgettable AS w ON w.widgetid = m.field3
 WHERE m.field1 = passed_in_parameter
   AND m.field2 = 1

UNION

SELECT m.field1,
       m.field2,
       m.field3,
       g.attribute23,
       g.attribute19
  FROM maintable   AS m
  JOIN gizmottable AS g ON g.gizmoid = m.field3
 WHERE m.field1 = passed_in_parameter
   AND m.field2 = 2

The direct analogue of what you're seeking is:

SELECT m.field1, m.field2, m.field3,
  FROM maintable   AS m
  JOIN widgettable AS w ON w.widgetid = m.field3
 WHERE m.field1 = passed_in_parameter
   AND m.field2 = 1
UNION
SELECT m.field1, m.field2, m.field3,
  FROM maintable   AS m
  JOIN gizmottable AS g ON g.gizmoid = m.field3
 WHERE m.field1 = passed_in_parameter
   AND m.field2 = 2