SQL (ugh!) - Teh Forum

<body> <div id="header"> <span class="left"> <span class="back"><a href="lthread_list.php?webtag=DEFAULT"><span class="image mobile_back" title="Back"></span></a></span> <span class="image mobile_logo" title="Beehive Forum Logo"></span> </span> <ul> <li> <a class="reply_all" href="lpost.php?webtag=DEFAULT&reply_to=30338.0&return_msg=30338.1"> <span class="image mobile_reply_all"></span><span class="text">Reply to All</span> </a> </li> <li> <a class="navigation" href="#"> <span class="image mobile_navigation"></span><span class="text">Show messages</span> </a> </li> <li> <a class="main" href="#"><span class="image mobile_menu" title="Menu"></span> </a> </li> </ul> </div> <div class="menu main"> <ul> <li><a href="lthread_list.php?webtag=DEFAULT">Messages</a></li> <li><a href="lpm.php?webtag=DEFAULT">Inbox</a></li> <li><a href="lsearch.php?webtag=DEFAULT">Search</a></li> <li><a href="llogon.php?webtag=DEFAULT">Login</a></li> </ul> </div> <div class="menu navigation"><a href="lmessages.php?webtag=DEFAULT&msg=30338.1">1–5</a></div> <div id="page_content"> <h3 class="thread_title"><a href="index.php?webtag=DEFAULT&amp;msg=30338.1">SQL (ugh!)</a> </h3> <div id="messages" data-navigation="30338_1_5_10"> <a name="a30338_1"></a><div class="message" id="message_30338_1"> <div class="message_header"> <div class="message_from"> From: Mikee<span class="message_time">17 Aug 2006 16:13</span> <div class="clearer"></div> </div><div class="message_to">To: ALL<span class="message_count">1 of 5</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30338.1">30338.1</a></div> <div class="message_body"> I'm trying to run a query that returns two sets of results from the same table.<br /> <br /> Basically, I have a table like this..<br /> <br /> <pre> id | surveyid | questiontext 1 | 1 | whats my name? 2 | 1 | whats my dob? 3 | 1 | whos your daddy? </pre><br /> <br /> And in the same query, I want to return a count of the number of rows with the surveyid of '1', and in the SAME query I want to return all the rows where the questions match what I'm sending in the query.<br /> <br /> For example...<br /> <br /> SELECT COUNT(id) FROM mytable WHERE surveyid = 1;<br /> SELECT COUNT(id) FROM mytable WHERE surveyid = 1 AND questiontext IN ('whats my name?', 'whats my dob?', 'whos your daddy?');<br /> <br /> I can do it in two queries in MySQL. Would anyone mind helping me turning it into one <b>SQL</b> query?<br /> <br /> chears </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30338.1&return_msg=30338.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30338.1"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30338_2"></a><div class="message" id="message_30338_2"> <div class="message_header"> <div class="message_from"> From: THERE IS NO GOD BUT (RENDLE)<span class="message_time">17 Aug 2006 16:24</span> <div class="clearer"></div> </div><div class="message_to">To: Mikee <span><span class="image post_read" title="Read: 17 Aug 2006 16:29"></span></span> <span class="message_count">2 of 5</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30338.2">30338.2</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30338.1#a30338_1" target="_self">30338.1</a></div> <div class="message_body"> You want to ask one question and get two answers? Why would you want to do that? Are you working under some kind of bizarre BOFH regime where you only get to run one query, or are you just mental? </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30338.2&return_msg=30338.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30338.2"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30338_3"></a><div class="message" id="message_30338_3"> <div class="message_header"> <div class="message_from"> From: Mikee<span class="message_time">17 Aug 2006 16:29</span> <div class="clearer"></div> </div><div class="message_to">To: THERE IS NO GOD BUT (RENDLE) <span><span class="image post_read" title="Read: 17 Aug 2006 16:39"></span></span> <span class="message_count">3 of 5</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30338.3">30338.3</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30338.1#a30338_2" target="_self">30338.2</a></div> <div class="message_body"> Slightly mental. </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30338.3&return_msg=30338.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30338.3"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30338_4"></a><div class="message" id="message_30338_4"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">17 Aug 2006 16:33</span> <div class="clearer"></div> </div><div class="message_to">To: Mikee <span><span class="image post_read" title="Read: 17 Aug 2006 16:41"></span></span> <span class="message_count">4 of 5</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30338.4">30338.4</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30338.1#a30338_1" target="_self">30338.1</a></div> <div class="message_body"> Not sure if this will work, but hopefully its along the right lines:<br /> <div class="quotetext" id="code-SQL"><b>SQL code:</b></div><pre class="code"><span style="color: #993333; font-weight: bold;">SELECT</span> id, SUM<span style="color: #66cc66;">(</span>questiontext <span style="color: #993333; font-weight: bold;">IN</span> <span style="color: #66cc66;">(</span>...<span style="color: #66cc66;">)</span><span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">AS</span> match_count <span style="color: #993333; font-weight: bold;">FROM</span> mytable <span style="color: #993333; font-weight: bold;">WHERE</span> surveyid = <span style="color: #cc66cc;">1</span> <span style="color: #993333; font-weight: bold;">GROUP</span> <span style="color: #993333; font-weight: bold;">BY</span> id</pre> <br /> Basically, you just get all rows with a matching surveyid and count the number of matching questions.<br /> <br /> Then you just use the query's recordcount to supply you with the first count. (which in PHP I think you use the sizeof() function for)<br /> <br /> But like I said, I'm not sure that SQL is the precise right way of doing it.<div class="edit_text">EDITED: 17 Aug 2006 16:34 by BOUGHTONP</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30338.4&return_msg=30338.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30338.4"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30338_5"></a><div class="message" id="message_30338_5"> <div class="message_header"> <div class="message_from"> From: Ben (BENLUMLEY)<span class="message_time">19 Aug 2006 15:07</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 19 Aug 2006 15:08"></span></span> <span class="message_count">5 of 5</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30338.5">30338.5</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30338.1#a30338_4" target="_self">30338.4</a></div> <div class="message_body"> if that doesn't work, an if will make it work. </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30338.5&return_msg=30338.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30338.5"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div></div> </div> </body>