Is it possible for a mysql query to return true/false instead of values?

Question

I have a table:

custID    orderID    orderComponent
=====================================
1          123        pizza
1          123        wings
1          234        breadsticks
1          239        salad
2          456        pizza
2          890        salad

I have a list of values - pizza, wings, breadsticks, and salad. I need a way to just get a true/false value if a customer has at least one record containing each of these. Is that possible with a mysql query, or do I just have to do a select distinct(orderComponent) for each user and use php to check the results?

Given the data you have there, what would you want to be the result from your query? — Eric Petroelje, Jan 15 '13 at 16:50
I think `custID` 1 would return true and `custID` 2 would return false — George, Jan 15 '13 at 16:50

Taryn · Accepted Answer · 2013-01-15T17:05:45.603

14

If you are just looking to see if the customer has ordered all items, then you can use:

select t1.custid,
  case when t2.total is not null 
    then 'true'
    else 'false'
  end OrderedAll
from yourtable t1
left join
(
  select custid, count(distinct orderComponent) Total
  from yourtable
  where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
  group by custid
  having count(distinct orderComponent) = 4
) t2
  on t1.custid = t2.custid

See SQL Fiddle with Demo

If you want to expand this out, to see if the custid has ordered all items in a single order, then you can use:

select t1.custid,
  t1.orderid,
  case when t2.total is not null 
    then 'true'
    else 'false'
  end OrderedAll
from yourtable t1
left join
(
  select custid, orderid, count(distinct orderComponent) Total
  from yourtable
  where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
  group by custid, orderID
  having count(distinct orderComponent) = 4
) t2
  on t1.custid = t2.custid
  and t1.orderId = t2.orderid

See SQL Fiddle with Demo

If you only want the custid and the true/false value, then you can add distinct to the query.

select distinct t1.custid,
  case when t2.total is not null 
    then 'true'
    else 'false'
  end OrderedAll
from yourtable t1
left join
(
  select custid, count(distinct orderComponent) Total
  from yourtable
  where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
  group by custid
  having count(distinct orderComponent) = 4
) t2
  on t1.custid = t2.custid

See SQL Fiddle with Demo

Or by custid and orderid:

select distinct 
  t1.custid,
  t1.orderid,
  case when t2.total is not null 
    then 'true'
    else 'false'
  end OrderedAll
from yourtable t1
left join
(
  select custid, orderid, count(distinct orderComponent) Total
  from yourtable
  where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
  group by custid, orderID
  having count(distinct orderComponent) = 4
) t2
  on t1.custid = t2.custid
  and t1.orderId = t2.orderid

See SQL Fiddle with Demo

edited Jan 15 '13 at 17:05

answered Jan 15 '13 at 17:00

Taryn

242,637
56
362
405

So this query doesn't return a single true/false for a custID - it returns true/false for each row that has an order component in the list. Correct? – EmmyS Jan 15 '13 at 17:02
Yes it adds a true/false to each row. Do you only want the custid and true/false? – Taryn Jan 15 '13 at 17:03
Yes. I need a single true/false per customer. – EmmyS Jan 15 '13 at 17:04
BTW, thanks for the links to the SQLFiddle. I use jsfiddle a lot, but had no idea there was one for SQL. Very useful. – EmmyS Jan 15 '13 at 17:05
Thanks. It's good to know how to do this; unfortunately because the list and number of items in the list can change frequently, it looks like it might be easier to do with PHP. – EmmyS Jan 15 '13 at 17:08
'true' is a sting type. is it possible to return Boolean true ? – Muaaz Khalid Oct 23 '15 at 19:11
@muaaz possibly you'd have to try it – Taryn Oct 23 '15 at 19:24
@bluefeet just tried following `SELECT case when col1 = 1 then true else false end as result FROM myTable` and there is an error. MySQL considers `true` as a column not a static value. it says unknown column – Muaaz Khalid Oct 23 '15 at 19:53
well, I found [the following](http://stackoverflow.com/questions/11167793/boolean-or-tinyint-confusion?answertab=votes#tab-top). So, **MySQL doesn't have Boolean data type**. really a strange thing.I didn't notice this before. – Muaaz Khalid Oct 23 '15 at 19:58

triclosan · Answer 2 · 2013-01-15T20:54:11.310

8

select case when 
count(distinct orderComponent) = 4 
then 'true' 
else 'false' 
end as bool
from tbl
where custID=1

edited Jan 15 '13 at 20:54

answered Jan 15 '13 at 16:51

triclosan

5,578
6
26
50

That won't work, because the list will not always be 4 items. – EmmyS Jan 15 '13 at 17:00

spencer7593 · Answer 3 · 2013-01-15T17:29:30.277

Here's one approach. This approach does not require an inline view (derived table), and can be effective if you want to include flags for multiple conditions:

EDIT:

This returns custID that has a row for all four items:

SELECT t.custID
     , MAX(IF(t.orderComponent='breadsticks',1,0))
       + MAX(IF(t.orderComponent='pizza',1,0))
       + MAX(IF(t.orderComponent='salad',1,0))
       + MAX(IF(t.orderComponent='wings',1,0)) AS has_all_four
  FROM mytable t
 GROUP BY t.custID
HAVING has_all_four = 4

ORIGINAL ANSWER:

(This checked for a customer "order" that had all four items, rather than just a "custID".)

SELECT t.custID
     , t.orderID
     , MAX(IF(t.orderComponent='breadsticks',1,0))
       + MAX(IF(t.orderComponent='pizza',1,0))
       + MAX(IF(t.orderComponent='salad',1,0))
       + MAX(IF(t.orderComponent='wings',1,0)) AS has_all_four
  -- , MAX(IF(t.orderComponent='breadsticks',1,0)) AS has_breadsticks
  -- , MAX(IF(t.orderComponent='pizza',1,0)) AS has_pizza
  -- , MAX(IF(t.orderComponent='salad',1,0)) AS has_salad
  -- , MAX(IF(t.orderComponent='wings',1,0)) AS has_wings
  FROM mytable t
 GROUP BY t.custID, t.orderID
HAVING has_all_four = 4

That will get the "orders" that have all four items. If you want to return just values for custID, then use the query above as an inline view (wrap it in another query)

SELECT s.custID
  FROM (
         SELECT t.custID
              , t.orderID
              , MAX(IF(t.orderComponent='breadsticks',1,0))
                + MAX(IF(t.orderComponent='pizza',1,0))
                + MAX(IF(t.orderComponent='salad',1,0))
                + MAX(IF(t.orderComponent='wings',1,0)) AS has_all_four
             FROM mytable t
            GROUP BY t.custID, t.orderID
           HAVING has_all_four = 4
       ) s
 GROUP BY s.custID

@EmmyS. That's my bad. That query checks for an "order" containing all four items. To check for a customer, just omit the orderID from the GROUP BY and the SELECT list. I will update my answer. — spencer7593, Jan 15 '13 at 17:27

score 0 · Answer 4 · answered Jan 15 '13 at 16:52

0

@EmmyS: you can do it both ways. If you want to check using MySql use:

SELECT @rowcount:=COUNT(*) FROM orderComponent Where (Your Conditions);
IF (@rowcount > 0) THEN
    'True'
ELSE
    'False'
END IF

answered Jan 15 '13 at 16:52

Learner

3,904
6
29
44

Is it possible for a mysql query to return true/false instead of values?

4 Answers4

EDIT:

ORIGINAL ANSWER:

Linked