PHP - MySQL IF (boolean) condition

Question

Sorry I wasn't sure how to title this question but I'll do my best to explain my problem. I have two tables in my DB that look like this:

APPLICATIONS:

app_id | data1 | data2
----------------------

1      | foo   | foo
2      | bar   | bar




APP_REQUIREMENTS:

app_id | requirement  | is_satisfied
--------------------------------------
1      | requirement1 | false
1      | requirement2 | false
2      | requirement1 | true
2      | requirement2 | true

What I am trying to do is query my DB to get all information from the APPLICATIONS table along with an extra field that represents whether there are any UNSATISFIED requirements for that application so my query would return something like this:

app_id | data1 | data2 | meets_all_requirements
------------------------------------------------
1      | foo   | foo   | false
2      | bar   | bar   | true

What would be the best way to do this with one query? Is there a better way to set up my tables/relationships to accommodate this?

Any advice is greatly appreciated!

Gordon Linoff · Accepted Answer · 2013-07-29 22:31:27Z

2

Assuming that is_satisfied is a boolean field, then min() effective does an and on all the conditions:

select a.*, min(is_satisfied) as all_satisfied
from Application a left outer join
     App_Requirements ar
     on a.app_id = ar.app_id
group by a.app_id;

If the values are really strings, you can do:

select a.*, min(is_satisfied = 'true') as all_satisfied
from Application a left outer join
     App_Requirements ar
     on a.app_id = ar.app_id
group by a.app_id;

answered Jul 29, 2013 at 22:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A.O. Over a year ago

Thanks, this is exactly what i was looking for. in your opinion is this the best way to handle this type of 1:N relationship?

Gordon Linoff Over a year ago

@A.O. This seems to be a reasonable structure.

juergen d · Accepted Answer · 2013-07-29 22:31:09Z

0

select a.*, sum(not r.is_satisfied) = 0 as meets_all_requirements
from applications a
left join app_requirements r on a.app_id = r.app_id
group by a.app_id

answered Jul 29, 2013 at 22:31

juergen d

205k40 gold badges303 silver badges377 bronze badges

Collectives™ on Stack Overflow

PHP - MySQL IF (boolean) condition

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related