StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POT-SQL Conditional Select Across Tables
text
Body
copied!<p>I have a table (lets call it main_guys) that looks like:</p> <pre><code>id, other_item_id, other_item_type, another_column, group_id </code></pre> <p>The <code>other_item_type</code> tells me which additional table holds additional information identified by the <code>other_item_id</code>.</p> <p>Additional columns required in the query depends on the table identified by <code>other_item_type</code>. So for <code>other_item_type == 'Type-X'</code> I need to get values from columns <code>foo</code> and <code>bar</code> from the <code>X</code> table. While for <code>other_item_type == Type-Y</code> I need to get values from columns <code>ick and blah</code> from <code>Y table</code>.</p> <p>In a perfect world, I would be able to get something like:</p> <p><code>id, other_item_id, other_item_type, another_column, group_id, foo, bar, ick, blah</code> - with values filled in where needed per type and other columns being null or what not if not needed.</p> <p>One additional issue is that <code>other_item_type</code> and <code>other_item_id</code> can also be empty - and the rest of the columns in that row need to be returned in the select - but in the empty case none of the additional columns across the other tables should contain any values.</p> <p>In terms of pseudo-code... </p> <pre><code>// obviously not proper code - just trying to communicate need // Note: Need all items from main_guys table - // even if main_guys.other_item_type is empty select * from main_guys where main_guys.group_id == "12345" if main_guys.other_item_type == "Type-X" select x.foo, x.bar from x where x.id == main_guys.other_item_id else if main_guys.other_item_type == "Type-Y" select y.ick, y.bar from y where y.id == main_guys.other_item_id </code></pre> <p>Thanks for any help or suggestions.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload