Multiple WHERE clause for each column?

delayedinsanity · 11-01-2009, 07:02 PM

Does anybody know if it's possible in MySQL to attach seperate WHERE clauses to each column being selected in a single query?

I'm running three queries to get data from a single table (so I can't use a join, afaik), which retrieves five columns. One column is the same amongst all three queries, and is how I join them in PHP afterwards. The first query selects two more individual columns, and the second and third one more each.

What I'd like to do is something akin to (and I know this is improper syntax, it's for demonstration of the concept):

php Code:

SELECT column WHERE column = this, column_two WHERE column_two = this FROM table

If it's possible, it would be much simpler than looping through and merging the results after the fact.

Enfernikus · 11-01-2009, 08:19 PM

AND ( Char limit )

delayedinsanity · 11-01-2009, 09:03 PM

No, no... I'm not that simple. :)

I meant distinct WHERE clauses for each column returned, as opposed to one that restricts the results globally. The first query needs to know a certain column is empty. The next two require that it isn't.

It doesn't seem like it's probable though... I'll just have to stick with using three queries instead of rolling it into one.

Rhinos · 11-01-2009, 09:21 PM

I don't know if I fully understand what you are asking without knowing more details but why can't you just select the five columns and use a UNION for the different WHERE conditions.

Kinda like:

Code:

(SELECT col1, col2, col3, col4, col5
FROM table
WHERE col1 = 'whatever')
UNION
(SELECT col1, col2, col3, col4, col5
FROM table
WHERE col2 = 'whatever')
UNION
(SELECT col1, col2, col3, col4, col5
FROM table
WHERE col3 = 'whatever');

Similar to how you described doing it in PHP however it may have better performance because it is just commissioning one query to mysql instead of the 3 and it may be faster in joining the results together also I don't know.

If I am completely wrong then by all means just ignore me :)

delayedinsanity · 11-01-2009, 10:22 PM

Won't work either, because the data returned in each column differs when you change the WHERE clause, so the UNION creates multiple groups for each timestamp.

If I then went one step further and created a virtual table using unions and grouped the results of that data, it would be severely skewed, as it would add columns that it's selected twice, so I'm not sure that UNION is the answer here.

rguy84 · 11-06-2009, 04:04 AM

I am not understanding what you are saying at all. Can you provide sample data, and what your result should look like?

Why would you use 3 queries to get data from one table, that seems terribly ineffient. Look into SQL Case (they are like if/else loops)...

delayedinsanity · 11-06-2009, 04:26 AM

Because the various data sets I need are all based on different stipulations.

I need a set of results for WHERE this = 'that' and a second set of results for the exact opposite, WHERE this <> 'that', and of course you cannot combine the two with AND as it would return nothing.

What I meant was on par with doing something like the following (which I know is not possible before anybody tells me):

SELECT this AS first WHERE this = '' this AS second WHERE this <> '' FROM `table`

Therefore I am forced to use

SELECT this AS first FROM `table` WHERE this = ''
SELECT this AS second FROM `table WHERE this <> ''

(these are simplified for the sake of demonstration).

rguy84 · 11-06-2009, 04:33 AM

yes

Quote:

SELECT this AS first WHERE this = '' this AS second WHERE this <> '' FROM `table`

is not valid SQL...
If you need DIFFERENT data sets displayed, why not make DIFFERENT queries?

delayedinsanity · 11-09-2009, 10:46 PM

I know it's not valid SQL.

me: I'm running three queries to get data from a single table
you: Why would you use 3 queries to get data from one table?
me: Because the various data sets I need are all based on different stipulations.
you: why not make DIFFERENT queries?

Do you see where this has gotten circular? I am using different queries. I was asking if anybody knew of a trick I was unaware of to nullify the need of multiple queries.

rguy84 · 11-09-2009, 10:50 PM

wow sorry I misread the thread. No need to bite my head off

delayedinsanity · 11-09-2009, 11:00 PM

Wasn't trying to bite your head off, just pointing out that we've covered those bases already.

I've tried a variety of tricks from joining the table on itself to using virtual tables and unions, but to no avail. Almost every effort skews the numbers in some way that I can't afford to permit, so I guess the consensus for now is that three queries are necessary after all.

rguy84 · 11-09-2009, 11:15 PM

Then did you look into cases like I suggested?

delayedinsanity · 11-09-2009, 11:40 PM

Tried them, with the following basic query to test it;

mysql Code:

SELECT SUBSTR( timestamp, 1, 10 ) AS timestamp,
CASE WHEN spider = '' THEN COUNT( DISTINCT ip ) END AS this,
CASE WHEN spider <> '' THEN COUNT( DISTINCT ip ) END AS that
FROM `table`
GROUP BY SUBSTR( timestamp, 1, 10 )

But I just get null values in column two for row one, null in column three for row two, and so on down the line. Seems like it should work though, but the null values are counter-productive. I should be getting values for both columns on any given date (the database is populated with test data, and running the queries seperately produces the right numbers, so it isn't just because it can't find any data based on the WHERE clause).