CodingSQL (ugh!)

  

Press Ctrl+Enter to quickly submit your post
Quick Reply  
 
 
  
 From:  Mikee   17 Aug 2006 17:13 
 To:  ALL 1 of 5 
30338.1 
I'm trying to run a query that returns two sets of results from the same table.

Basically, I have a table like this..

id | surveyid | questiontext
1  |  1       | whats my name?
2  |  1       | whats my dob?
3  |  1       | whos your daddy?


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.

For example...

SELECT COUNT(id) FROM mytable WHERE surveyid = 1;
SELECT COUNT(id) FROM mytable WHERE surveyid = 1 AND questiontext IN ('whats my name?', 'whats my dob?', 'whos your daddy?');

I can do it in two queries in MySQL. Would anyone mind helping me turning it into one SQL query?

chears


[Mwah]
0/0
 Reply   Quote More 

 From:  THERE IS NO GOD BUT (RENDLE)   17 Aug 2006 17:24 
 To:  Mikee      2 of 5 
30338.2 In reply to 30338.1 
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?

0/0
 Reply   Quote More 

 From:  Mikee   17 Aug 2006 17:29 
 To:  THERE IS NO GOD BUT (RENDLE)      3 of 5 
30338.3 In reply to 30338.2 
Slightly mental.


[Mwah]
0/0
 Reply   Quote More 

 From:  Peter (BOUGHTONP)   17 Aug 2006 17:33 
 To:  Mikee      4 of 5 
30338.4 In reply to 30338.1 
Not sure if this will work, but hopefully its along the right lines:
SQL code:
SELECT id, SUM(questiontext IN (...)) AS match_count
FROM mytable
WHERE surveyid = 1
GROUP BY id

Basically, you just get all rows with a matching surveyid and count the number of matching questions.

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)

But like I said, I'm not sure that SQL is the precise right way of doing it.
0/0
 Reply   Quote More 

 From:  Ben (BENLUMLEY)   19 Aug 2006 16:07 
 To:  Peter (BOUGHTONP)      5 of 5 
30338.5 In reply to 30338.4 
if that doesn't work, an if will make it work.

SIGSIZESWITCHER
0/0
 Reply   Quote More 

Reply to All    
 

1–5

Rate my interest:

Adjust text size : Smaller 10 Larger

Beehive Forum 1.5.2 |  FAQ |  Docs |  Support |  Donate! ©2002 - 2024 Project Beehive Forum

Forum Stats