[SOLVED] AND, OR, IN syntax?

[Prodigal Son]

If I want to make this statement shorter:

SELECT id FROM table WHERE col = 'this' OR col = 'that' OR col = 'bla' OR col = 'blah'

 

I can do

SELECT id FROM table WHERE col IN('this','that','bla','blah')

 

Is there a clause similar to that for AND?

Or I must I do:

SELECT id FROM table WHERE col = 'this' AND col = 'that' AND col = 'bla' AND col = 'blah'

 

Thanks.

[fenway]

That doesn't make any sense... a field can't have multiple values simultaneously!

[Mchl]

This query will get you same results (none)

SELECT id FROM table WHERE 1 = 0

[Prodigal Son]

That doesn't make any sense... a field can't have multiple values simultaneously!

Hmm, I didn't even realize the query wouldn't even work lol and I was jumping ahead of myself.

To simplify what I have is a product that could have many colors. In one table I will have the name of the product and the product id. In another table I will have the product id as a foreign key and the value could be "blue". If the product has more than one color I will insert another row with the same id and it could have a value of "purple". So how can I find the product id for something that has both blue and purple as color values?

[PFMaBiSmAd]

Your goal is to find id's that have both blue and purple rows?

 

Something like this should work -

SELECT id, count(*) as cnt FROM your_table WHERE color IN('blue','purple') GROUP BY id HAVING cnt = 2

[fenway]

That will work, but it's going to be very bad for index usage (check EXPLAIN) what if 90% of the rows have either, but only 2% have both? Also, depending on what version of mysql, index_merge many not be an option, so using IN() isn't great either.

 

It's better to simply to simply join the table to itself and see if you get any rows back:

 

SELECT t1.id
FROM your_table as t1
CROSS JOIN your_table as t2 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple'

[Mchl]

It's better to simply to simply join the table to itself and see if you get any rows back:

 

SELECT t1.id
FROM your_table as t1
CROSS JOIN your_table as t2 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple'

 

What if I need more colours? More joins?

[fenway]

Yup... it's still much faster.

[Mchl]

I'll take your word on it and test it when find an occasion

 

That was pretty interesting!

[fenway]

It's not "my" idea at all... just look at some EXPLAIN output and you'll see... but generally speaking, HAVING is bad.

[Mchl]

Yeah... as far as I remember it's executed after table(s) is scanned for rows.

[Prodigal Son]

That will work, but it's going to be very bad for index usage (check EXPLAIN) what if 90% of the rows have either, but only 2% have both? Also, depending on what version of mysql, index_merge many not be an option, so using IN() isn't great either.

 

It's better to simply to simply join the table to itself and see if you get any rows back:

 

SELECT t1.id
FROM your_table as t1
CROSS JOIN your_table as t2 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple'

 

Never used something like that, if I need 5 values am I supposed to join the table 5 times? Is this right:

 

SELECT t1.id
FROM your_table as t1
CROSS JOIN your_table as t2
CROSS JOIN your_table as t3
CROSS JOIN your_table as t4
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

And if even more, I just keep rejoining the table that many times?

[Mchl]

I believe like this:

 

SELECT t1.id
FROM your_table as t1
CROSS JOIN your_table as t2 USING ( id )
CROSS JOIN your_table as t3 USING ( id )
CROSS JOIN your_table as t4 USING ( id )
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

[Prodigal Son]

I believe like this:

 

SELECT t1.id
FROM your_table as t1
CROSS JOIN your_table as t2 USING ( id )
CROSS JOIN your_table as t3 USING ( id )
CROSS JOIN your_table as t4 USING ( id )
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

Yep, that's right. Thanks all.

[Prodigal Son]

Oh, one last question. You can't use an INNER JOIN before CROSS JOIN? Say I want to grab other info that's not in those tables and I want to try an INNER JOIN, then CROSS JOIN but it doesn't seem to work:

SELECT t1.id, col
FROM extra as e INNER JOIN your_table as t1
ON e.id = t1.id
CROSS JOIN your_table as t2 USING ( id )
CROSS JOIN your_table as t3 USING ( id )
CROSS JOIN your_table as t4 USING ( id )
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

[fenway]

I see no reason why that wouldn't work... the only reason I prefer cross join is that I have the conditions in the where clause, which I find more explicit when I'm joining for speed instead of "by design", if you know what I mean.  Besides, what error do you get?

[Prodigal Son]

I see no reason why that wouldn't work... the only reason I prefer cross join is that I have the conditions in the where clause, which I find more explicit when I'm joining for speed instead of "by design", if you know what I mean.  Besides, what error do you get?

If I run that statement without the INNER JOIN it works fine. Once I add the inner join it tells me:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id)
WHERE t1.color = 'blue' AND t2.color = 'purple'

Using the same query as before, except I only am joining the table twice.

SELECT t1.id, col
FROM extra as e INNER JOIN your_table as t1
ON e.id = t1.id
CROSS JOIN your_table as t2 USING (t1.id)
WHERE t1.color = 'blue' AND t2.color = 'purple'

[Mchl]

USING (t1.id)

this is incorrect

 

Either

USING (id)  

 

Or

ON (t1.id = t2.id)

[Prodigal Son]

USING (t1.id)

this is incorrect

 

Either

USING (id)  

 

Or

ON (t1.id = t2.id)

I had done that initially but then it will tell me column id is ambiguous so I added the prefixes and get the error above.

[fenway]

I had done that initially but then it will tell me column id is ambiguous so I added the prefixes and get the error above.

Done what? The whole point of USING() is that you don't need the prefix.

[Prodigal Son]

Done what? The whole point of USING() is that you don't need the prefix.

Oh I see...

 

Originally I had done this:

SELECT t1.id, col
FROM extra as e INNER JOIN your_table as t1
ON e.id = t1.id
CROSS JOIN your_table as t2 USING (id)
WHERE t1.color = 'blue' AND t2.color = 'purple'

 

So that's what "using" does hehe. I switched it to USING instead of ON for the inner join and now it works. Thanks again.