MYSQL SQL statement. Is this conditional message possible?
3 I'm just wondering if this type of query is 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 that what I am trying to do makes sense. In general, I need to concatenate different tables based on what field value is in the original element.
source to share
As zerkms suggested, you can leave external ones connecting both of them. 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))
source to share
SELECT
maintable.field1,
maintable.field2,
maintable.field3,
CASE maintable.field2
WHEN 1 THEN widgettable.widget_name
WHEN 2 THEN gizmotable.gizmo_name
END AS consolidated_name
FROM maintable
LEFT JOIN widgettable ON widgettable.widgetid = maintable.field3
LEFT JOIN gizmottable ON gizmottable.gizmoid = maintable.field3
WHERE maintable.field1 = 'passed in parameter'
source to share
This is similar to a UNION query, except that you did not specify that you want to see any data from the joined tables. This means that inner joins are simple existence checks; they check that there is a row in the concatenated table that matches the row in the main table. I have taken a "radical" step, assuming you want to get multiple columns from the joined tables; you can omit these combined columns without drastically changing the overall effect. This UNION query assumes that the columns you want from the WidgetTable and the GizmotTable are fairly 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
A direct analogue of what you are looking for 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
source to share