Creating complex IF, OR, NOT & AND SQL queries (MySQL)

Question

(Apologies if this a duplicate - I have tried searching, but I may not know the right word for what I'm trying to achieve - feel free to correct me!)

The Background

So I have a PHP based app (Codeigniter, but I'm using normal SQL language for this part), that has a MySQL database, with 2 tables - 'contact' and 'order'.

For simplicity, let's assume that:

'contact' has 3 cols : Id, FirstName, LastName
'order' has 4 cols : Id, ContactId, ItemBought, ItemValidDate
Example of a row in 'order' table: 22, 11, Adult Membership, 2012/13
Id is primary key of both tables, ContactId is foreign key for 'contact' and ItemBought and ItemValidDate are both simple varchar (we're storing 'seasons' rather than dates -I know, its not ideal but its what the client wants)

At some point, I know, I am going to have to extend this for 3 tables and use an OrderItem table, to allow an order to have multiple items, so I'd like to find a solution that can be built on. But at present, I don't even understand the basics so I've kept it to 2 tables

The Problem

I want to create a search page that allows the user to find subsets of records based on lots of different criteria.

See screenshot of search page

This form submits as an array of criteria like this:

[order_type_operator] => Array
    (
        [0] => equal
        [1] => equalor
        [2] => notequal
    )

[order_type] => Array
    (
        [0] => Adult Membership
        [1] => Adult Membership
        [2] => Adult Membership
    )

[order_expire] => Array
    (
        [0] => 2005/06
        [1] => 2006/07
        [2] => 2010/11
    )

[submit] => Start Search

I then cycle through this array, testing to see if values have been submitted, and build up my SQL query.

So, I hope I've explained it properly, so that its clear a user may use this form to search for records that match lots of different conditions - in theory, unlimited numbers of conditions - to end up with a list of contacts that match this criteria.

What I have Tried

Example 1 - simple WHERE

"find contact records that have an order record for 'Adult Membership' in '2009/10'"
i.e. SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10')

This works fine.

Example 2 - WHERE OR WHERE

"find contact records that have an order record for 'Adult Membership' in '2009/10'" OR have a an order record for 'Adult Membership' in '2010/10'
i.e. SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10') OR (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2010/11')

This works fine as long as EVERY condition the user is asking for is an OR query. I assume that I can build this query up using brackets and OR for as big as I like? E.g. find Adult membership in 2005/06, OR 2006/07, OR 2007/08, OR 2008/09 etc etc will be just like the above SQL with lots more brackets joined by 'OR'?

Example 3 - WHERE AND WHERE - I'm stuck!

"find contact records that have an order record for 'Adult Membership' in '2009/10' OR 2010/11 AND have a an order record for 'Adult Membership' in '2012/13'

At the moment, I've been trying UNION, however if there are more queries to follow this (e.g Adult membership in 2008 OR 2009 AND 2010) this means doing more than one SELECT. (Perhaps this is the answer?)

e.g. `SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10') OR (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2010/11')

UNION

SELECT * FROM contact
JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2012/13)`

Example 4 - But does NOT have a record.... Blows my mind

"find contact records that have an order record for 'Adult Membership' in '2009/10' AND have an order record for 'Adult Membership' in '2010/10' BUT DO NOT have an order of 'Sponsorship' in 2007/08

I wondered about running these queries, storing the results in a PHP array and then doing a IN (*array of ids already selected*), but this just seems like I'm not using SQL properly.

So clever people - what am I doing wrong?

Thank you so much in advance for you help.

PS. Not asking you write the code for me!

PPS. If you know of any good tutorials then I'll happily follow them!

PPPS. If this is a duplicate, then please accept my apologies!

Sorry, but what seems to be the problem if This works fine.? — Maxim Kumpan, Jun 14 '13 at 8:20
Not an answer but maybe a start to understand JOINs in mysql. helped me alot! A visual explanation of SQL-Joins — ferdynator, Jun 14 '13 at 8:24
Sorry maxim - I wasn't clear. Example 1 and 2 work as I expect them to. Example 3 & 4 are where I am stuck. — al_manchester, Jun 14 '13 at 9:34
byf-ferdy: I LOVE that blog bost! It was really useful in getting me to here. But I just can't see how examples 3 & 4 fit into those posts, if they are part of a larger, more complex query. — al_manchester, Jun 14 '13 at 9:35

Kickstart · Accepted Answer · 2013-06-14 09:13:29Z

As ZorleQ says it can rapidly get to be a mess

For your 3rd question a possible solution using joins of subselects would be as follows

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
INNER JOIN (SELECT DISTINCT ContactId 
            FROM order 
            WHERE (ItemBought = 'Adult Membership' AND ItemValidDate = '2009/10') 
            OR (ItemBought = 'Adult Membership' AND ItemValidDate = '2010/11')) Sub1
ON contact.Id = Sub1.ContactId
INNER JOIN (SELECT DISTINCT ContactId
            FROM order 
            WHERE (ItemBought = 'Adult Membership' AND ItemValidDate = '2012/13')) Sub2
ON contact.Id = Sub2.ContactId

You could probably do this without using the subselects and just a plain join as follows

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
LEFT OUTER JOIN order Sub1
ON contact.Id = Sub1.ContactId AND Sub1.ItemBought = 'Adult Membership' AND Sub1.ItemValidDate = '2009/10'
LEFT OUTER JOIN order Sub2
ON contact.Id = Sub2.ContactId AND Sub2.ItemBought = 'Adult Membership' AND Sub2.ItemValidDate = '2010/11'
INNER JOIN order  Sub3
ON contact.Id = Sub3.ContactId AND Sub3.ItemBought = 'Adult Membership' AND Sub3.ItemValidDate = '2012/13'
WHERE Sub1.ContactId IS NOT NULL OR Sub2 IS NOT NULL

Your 4th question can be done using a LEFT OUTER JOIN to find a record with Sponsorship bought for 2007/08, and only returning rows where a match isn't found (ie, check the ContactId on the LEFT OUTER JOINed table is NULL).

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
INNER JOIN order Sub1
ON contact.Id = Sub1.ContactId AND Sub1.ItemBought = 'Adult Membership' AND Sub1.ItemValidDate = '2009/10'
INNER JOIN order Sub2
ON contact.Id = Sub2.ContactId AND Sub2.ItemBought = 'Adult Membership' AND Sub2.ItemValidDate = '2010/10'
LEFT OUTER JOIN order Sub3
ON contact.Id = Sub3.ContactId AND Sub3.ItemBought = 'Sponsorship' AND Sub3.ItemValidDate = '2007/08'
WHERE Sub3.ContactId IS NULL

Kickstart: Thank you so much for your clear examples. I think i follow them. So would I be able to create a rule that helps me build the query? E.g. 'if the user wants to search for AND then do an OUTER join, if they want OR do an INNER join and if they want NOT do an OUTER join but a WHERE contactId is NULL? (Obv this is in thoery - I haven;t yet worked out if these rules are right) — al_manchester, Jun 14 '13 at 9:45
You should be able to do that. However for an OR you would need an OUTER JOIN, then check for the existence of one record in the WHERE clause (ie, 2nd example). It will be horrible to understand though! — Kickstart, Jun 14 '13 at 10:31
Making more and more sense. I've already rewritten the user form page based on both yours and ZorleQ's advice, and I think i've ironed about 90% of the issues out now. Thank you so so much. — al_manchester, Jun 14 '13 at 14:09
PS. Do I need to pick and answer now? how do I do this when its a mix of the 2? — al_manchester, Jun 14 '13 at 14:09
You can vote for answers and pick an answer. Think you can upvote 2 answers. Glad to have helped — Kickstart, Jun 14 '13 at 15:16

ZorleQ · Answer 2 · 2013-06-14 09:24:36Z

up vote 0 down vote

I think you have to take a step back and try to visualize your question on paper first. Examples 1 and 2 are pretty easy, but let's look at example 3.

For conditions where all your criteria are 'AND' or 'OR' - things are very simple. Just do a long WHERE, just liek before. However, when you start mixing them you have to answer yourself a serious question:

How do you split the conditions?

Lets say someone picked up those criteria:

and A
or B
and C

This gives you so many permutations of your query! eg:

(A or B) and C
A or (B and C)
(A and C) or B

If you add one more 'OR' to it, you will end it with tens of combinations more! Leaving you in a place where you have to guess what to do. Don't even want to think what would happen if there is a NOT involved...

This is not a direct answer to your question, but more of a pointer towards a possible solution. The last time we had to do something similar, we've ended up grouping the conditions together into blocks.

You could either add a condition within a block or add a new search block. Think of the blocks as brackets in the example above. Everything in a block is an 'AND' or 'NOT AND', and between blocks you can specify 'and' or 'or'. This way you know straight away how to structure your query. This worked like a charm in a standalone application. Might be a bit tricky to implement it nicely on a page, but you catch the idea.

edited Jun 14 '13 at 9:24

answered Jun 14 '13 at 8:50

ZorleQ
951413

ZorleQ: Thank you! I'm firmly of the opinion that if its getting very complex then you're probably doing it wrong, and your answer suggests that maybe this is the case here. I will go back to the the (literal) drawing board and look at other ways of achieving this. – al_manchester Jun 14 '13 at 9:36

It's just a matter of common sense. If you can't plan it on paper / in your head - you will never be able to code it. And if the client wants so much control over the results - do it client side via filtering. I can guarantee you that having a simpler query to load more results with a nice jQuery table filtering plugin will be faster than trying to do millions of NASA style joins with extra PHP processing. – ZorleQ Jun 14 '13 at 9:40

ZorelQ - just wondering if this was a public app you built or private? I think this is the route I need to go but would love to see a working example. Is it possible to see your app? – al_manchester Jun 14 '13 at 11:50

Unfortunately I don't have access to this tool anymore. It was create membership requirements for groups based on other memberships. So for example mailing list would be automatically populated if all criteria groups were true. Can't really write too much at the moment, but I can walk you through the details in the evening if you want. – ZorleQ Jun 14 '13 at 13:02

ZorleQ - very very kind of you, but think we're getting somewhere based on your advice. I'll report here when were done. Thanks again! – al_manchester Jun 14 '13 at 18:39

add comment

Rodney · Answer 3 · 2014-01-24 19:17:00Z

My solution to all issues like this where multiple criteria may or may not be provided by the user is the following... (this example is for oracle, but should be able to be done in MySQL as well)...

You pass in all the filter variables, regardless of whether they are null or filled with a value. In this example, I'll say I have 3 values the user may or may not fill that act as filters on the SELECT.

SELECT
   *
FROM
    table
WHERE
    NVL2(InputVariable1, InputVariable1, Column1) = Column1
OR  NVL2(InputVariable2, InputVariable2, Column2) = Column2 
OR  NVL2(InputVariable3, InputVariable3, Column3) = Column3

NVL2 - This is an oracle function. If the first value is not null, it returns the second value, otherwise it returns the third value. If you aren't using oracle, and there is no equivalent function for NVL2, simply write the function yourself.

So, using the above example, the code ALWAYS passes all three InputVariables into the select statement, even if they are NULL. By using NVL2 or an equivalent function, the comparison is between the InputVariable and the Column ONLY if the InputVariable is not null; otherwise it is between the Column and the Column, which will of course always be true, thereby effectively ignoring that filter variable, which is what you want (i.e. a null filter value matches all rows - i.e. if user does not specify LastName, then include all LastNames).

This solution allows you to use many filter variables without having to do a lot of processing up front - just pass them all down into the SELECT every time, whether they are null or not.

If you have sets of filter variables (i.e. the user enables a set of input values via a checkbox or some similar mechanism), you can do the above inside of a CASE statement. Each case should check the enable value for a given set, and return the result of evaluating the entire set of filter variables (exactly like the above). You then compare the result of the entire CASE structure to 1, as in...

WHERE

CASE [ expression ]

    WHEN enableSet1 
    THEN     NVL2(InputVariable1, InputVariable1, Column1) = Column1
         OR  NVL2(InputVariable2, InputVariable2, Column2) = Column2 
         OR  NVL2(InputVariable3, InputVariable3, Column3) = Column3

   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

END = 1

This works because the value of a CASE structure is the result of the THEN block which was evaluated.

This will allow you to do ALL or MOST of your desired filtering within the confines of a single SELECT statement - again, without having to do a lot of pre-processing to build the SELECT.

asked	8 months ago
viewed	293 times
active	1 month ago

current community

more stack exchange communities

Creating complex IF, OR, NOT & AND SQL queries (MySQL)

The Background

The Problem

What I have Tried

Example 1 - simple WHERE

Example 2 - WHERE OR WHERE

Example 3 - WHERE AND WHERE - I'm stuck!

Example 4 - But does NOT have a record.... Blows my mind

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql sql codeigniter or ask your own question.

Community Bulletin

Hot Network Questions

current community

more stack exchange communities

Creating complex IF, OR, NOT & AND SQL queries (MySQL)

The Background

The Problem

What I have Tried

Example 1 - simple WHERE

Example 2 - WHERE OR WHERE

Example 3 - WHERE AND WHERE - I'm stuck!

Example 4 - But does NOT have a record.... Blows my mind

3 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql sql codeigniter or ask your own question.

Community Bulletin

Related

Hot Network Questions