Selecting only where values are "yes" in Group BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Selecting only where values are "yes" in Group BY

Post by simonmlewis »

This is difficult to describe, but will try my best.

Code: Select all

$result = mysql_query ("SELECT * FROM `trades` WHERE tradeid = '$id' AND selected = 'yes' GROUP BY tradeid") or die (mysql_error());
This needs to show a form when ALL the "selected" fields have a yes in them. I have to 'groupby' it, otherwise I'd get loads of rows.

How do you force it to only show it when ALL the 'selected' fields say yes?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mesotier
Forum Newbie
Posts: 2
Joined: Thu Jul 29, 2010 3:12 am

Re: Selecting only where values are "yes" in Group BY

Post by mesotier »

You can do a subquery to check for rows where selected is not = yes, then just check that the count of the subquery results is zero.
Add any additional where clauses (in both the main query and subquery) that you need.

SELECT * FROM trades where (select count(*) from trades where selected != 'yes') = 0

Does this help?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Selecting only where values are "yes" in Group BY

Post by simonmlewis »

Hi

The idea was to only show some buttons when all boxes are ticked.

Having now consulted with the person I am doing it for, they don't really mind if they are not all ticked.

Basically, if they have got to tick three boxes to show they have got three items - having three items in their hand makes it obvious they have got three.

It's like a "double check" for them. If they had 50, they would use the ticks more. So I think now I won't need it.

However - please can you check my other latest post, as it is VERY similar indeed. It's about grouping, and controlling what is shown. If you could lend a hand there, it would be brilliant as that IS needed. Thank you very much for your time.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply