Sub Queries

[DHood]

I'm attempting to make a table of mine searchable. I have 4 fields, name, web, email, sms.

I intend to have a sidebar with links to filter the results with like web=Yes, email=Yes, sms=Yes. I want to next to the links show the amount of results that would be returned if the current query had that parameter added.

 

So lets say I'm at q=Test it'd be searching "SELECT * FROM table WHERE name LIKE '%test%'" I want to be able to return a count of results for "SELECT * FROM table WHERE name LIKE '%test%' and sms = 'Yes'" and "SELECT * FROM table WHERE name LIKE '%test%' and email = 'Yes'" without running two new queries.

[Jessica]

Use an OR clause between those two.

WHERE name ... AND (sms .. OR email .. )

[DHood]

Sorry, I probably could have been more clear.

I'm not looking to return the results. I just want the count as if that were the query.

 

I want to display the results of SELECT * FROM table WHERE name LIKE '%test%' and on the side bar show the number of row for SELECT * FROM table WHERE name LIKE '%test%' and sms = 'Yes' next to "SMS" and then SELECT * FROM table WHERE name LIKE '%test%' and email = 'Yes' number of results next to email.

 

Is it possible to do this with one query or do I need to run 3 different ones? I was looking for a way to do one query because there's actually like 8 of these that I want to run.

[Jessica]

Yes. 

 

Properly normalize your data, then you can use COUNT() and GROUP BY to get it all in one query.

[kicken]

Use a CASE statement and SUM. For example:

SELECT
   someField
   , SUM(CASE WHEN email='Yes' THEN 1 ELSE 0 END) as emailCount
   , SUM(CASE WHEN sms='Yes' THEN 1 ELSE 0 END) as smsCount
   , SUM(CASE WHEN web='Yes' THEN 1 ELSE 0 END) as webCount
FROM table
WHERE
   name LIKE '%test%'
GROUP BY
   someField

[DHood]

That's nearly what I want, however since I'm also displaying a list of results it's just making the counts 1 for each row. I want the sum of all rows with that match.

[Jessica]

Which is it? You want just the count, or you want all the rows?

 

Sorry, I probably could have been more clear.

I'm not looking to return the results. I just want the count as if that were the query.

 

 

That's nearly what I want, however since I'm also displaying a list of results it's just making the counts 1 for each row. I want the sum of all rows with that match.

[DHood]

I take it I have to do this as two separate queries since I want both?

 

I want the results of the query, but then I want to count how many would be results if I added "and field='Yes'"

[Jessica]

Yes, those are two different things.