Databases
StackOverflow2013
Postsdbo
POHow to write this? Mysql Result(Dynamic Table Retrieval)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to write this? Mysql Result(Dynamic Table Retrieval)
    text
    copied!<p>What I am looking for is a query that dynamically selects from ACTIVITY which is then applied based on ACTIVITY.what (either 'posted_blog' or 'posted_video') to retrieve the appropriate information from either the BLOG or VIDEO table. </p> <p>I wonder whether <code>A.</code> Is this possible to do? <code>B.</code> If it is possible, how? </p> <p>My three tables:</p> <pre><code>ACTIVITY ========= id what - can either be 'posted_blog' or 'posted_video' reference user BLOG ========= id title VIDEO ========= id title </code></pre> <p>The problem I'm having I think is where the case goes, and perhaps the proper usage?</p> <p>So far I have tried as follows:</p> <pre><code>$result = mysql_query("SELECT ACTIVITY.user,CASE ACTIVITY.what WHEN "posted_blog" THEN (SELECT BLOG.title FROM BLOG WHERE BLOG.id=ACTIVITY.reference) WHEN "posted_video" THEN (SELECT VIDEO.title FROM VIDOES WHERE VIDEOS.id=ACTIVITY.reference) END FROM ACTIVITY WHERE ACTIVITY.user='10' ORDER BY ACTIVITY.id DESC ")or die(mysql_error()); </code></pre> <p>The solution is:</p> <pre><code>mysql_query("SELECT CASE ACTIVITY.what WHEN 'posted_video' THEN(SELECT VIDEO.title FROM VIDEO WHERE VIDEO.id=ACTIVITY.reference) WHEN 'posted_blog' THEN(SELECT BLOG.title FROM BLOG WHERE BLOG.id=ACTIVITY.reference)END AS title FROM ACTIVITY WHERE ACTIVITY.user='10' AND (ACTIVITY.action='posted_video' OR ACTIVITY.action='posted_blog')")or die(mysql_error()); </code></pre> <p>Now if I try to get an additional column from the VIDEO or BLOG table it is throwing an error Operand should contain 1 column(s). Does that mean I cannot select two columns? Would I then have to rewrite the case statement for any additional columns I need from those tables?</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