Help me build this MySQL Query

Question

this should be easy, but it's not :(

I have a table with a bunch of different feature Ids.

What I need is to build a way to say:

"show me all pet stores that have puppies, kittens, mice, or fish" (at least one)

That's the easy part.

What I'm stuck at is...

"show me all pet stores that have puppies for sure, but maybe also kittens, mice or fish"

Any ideas? :)

Thanks guys!

Edit:

I posted this question on another site, but didn't get an answer, I'll post it here. This is my original question, please consider the above also:

+------------------+----------+---------+

¦ Column1 ¦ Column2 ¦ Active ¦

+------------------+----------+---------+

¦ blue widgets ¦ 14 ¦ y ¦

¦ red ¦ 14 ¦ y ¦

¦ redx ¦ 15 ¦ y ¦

¦ blues ¦ 14 ¦ y ¦

¦ blue ¦ 15 ¦ n ¦

¦ bluesX ¦ 15 ¦ n ¦

¦ widgets ¦ 14 ¦ n ¦

+------------------+----------+---------+

Here is my drama... I need to query this:

where (Column2 = 14 AND Column2 = 15) and active = 'y'

I a result set that has all records where there is active = 'y' for both Column2=14 and Column2=15

I can't figure this out...

I can do it so it returns me 14 and 15 with at least one of them as y, but not both.

I tried:

select * from table where (Column2 = 14 and Column2 = 15) and active = 'y'

Of course, this returns nothing, since both can't be 14 and 15 at the same time.

So I tried:

select * from table where ((Column2 = 14 or Column2 = 15) and active = 'y')

And this returns a result set, but not what I want...

I need to only return results in column1 that have:

column2 = 14 and active = 'y' column2 = 15 and active = 'y'

both conditions must be true.

Any help? thanks guys!

Answer 1

This is simple:

SELECT * FROM table 
WHERE (Column2 = 14 AND active = 'y') OR (Column2 = 15 AND active = 'y')

Answer 2

You could do something like this:

select
    pet_store_name
from pet_store
where
    pet_type = 'puppies' or
    (pet_type in ('kittens', 'mice', 'fish'))

*Edit: See Michael's answer.

Answer 3

Assuming your table def is

id, pet_store_name, pet_type


select distinct   
     pet_store_name
from 
     pet_store
where    
     pet_type = 'puppies' AND
     pet_store_name in (select pet_store_name from pet_store 
                        where pet_type in('kittens', 'mice', 'fish')

Answer 4

Sorry, your question is still ill-defined: you say you want to select rows which have both column2 = 14 and column2 = 15. Simple: none exist, column two has at most one value (and might be null).

Since the question is tagged homework, I'm not going to just give you the answer. First of all, aks yourself a uniqueness question: is it possible that there are multiple rows with column1 = 'red' and column2 = '14'? If not, than you just need to count how many of the requirements "column2 = 'some value'" are met, and select only those where this number is the full number of requirements.

If this is the case, try

SELECT
  Column1,
  SUM(Column2 IN (14, 15)) AS NumberOfRequirementsMet
FROM table
WHERE Active = 'y'
GROUP BY Column1
HAVING NumberOfRequirementsMet = 2;

Answer 5

I'm also reluctant to give out the answer, but I'll give you some hints.

Suppose the rows you're interested in are like this:

col1 col2 active
 red  14   y
 red  15   y

Then you want the color such that some row has 14 & y, and some other row has 15 & y.
And of course the color on both rows must be equal to each other.

The answer is related to this principle:

Any SQL expression references only one row at a time.

You can use a JOIN to combine one row with another row, and the result is one new row that has twice as many columns and exists ephemerally as the query is being executed (i.e. it's not stored anywhere). Since it's now one row, you can use write SQL expressions to compare columns that exist on separate rows in the source table.

Another concept that is important for using joins: table aliases.

Does that give you enough to go on?

---

Okay, yes I was reluctant because the question was tagged homework. But it isn't any more.

Here's what I mean:

SELECT DISTINCT col1
FROM mytable t1 JOIN mytable t2
 ON (t1.col1 = t2.col1)              -- i.e. they are both red
WHERE t1.col2 = 14 AND t1.active = 'y'
 AND  t2.col2 = 15 AND t2.active = 'y';

Once you do the join, you have columns from both tables on the same row (at least in the result set being formed, not in storage). So you can write an expression referencing columns from both rows of the base table.

Answer 6

Referring to your table, you are probably looking for something like:

SELECT DISTINCT t1.col1 FROM table t1 INNER JOIN table t2 ON t1.col1 = t2.col1 AND t1.col2 = 'puppies' AND t2.col2 IN ('mice','fish','reptiles')

You will probably want to denormalize your table and reorganize the data if this is a common query.