mysql and conditions

[kunal]

hey guys.. i have a mysql table with the following structure :

id | tv | cable

id : stores a unique record id
tv : it stores
1 if black and white without remote
2 if color with without remote
3 if black and white with remote
4 if color with with remote

cable : stores a
1 if it is a month subscription
2 if it is a quaterly subscription
3 if it is a yearly subscription

now, I have over 100 records in this table... I am trying to extract every record, that has a black and white tv with a remote or without a remote AND also has a yearly subscription or quaterly subscription for cable...

how would i do this?

[scoates]

SELECT * FROM [table]
WHERE
  tv IN (1,3)
AND
  cable IN (2,3)

Is that what you're looking for?

S

[kunal]

umm.. how does the IN thing work? ive tried finding it in the manual.. but couldnt

[scoates]

it's pretty much a simplified OR clause:

SELECT * FROM [table]
WHERE
  (tv = 1 OR
   tv = 3)
AND
  (cable = 2 OR
   cable = 3)

IN is great for doing big sets.
(especially coupled with implode(...))

S

[kunal]

ah... i tried that [ using the normail OR and AND ]but it dint work... since it has to match the first criteria, that is the TV properties.. and then from that set, select the cable properties...

the easy way of doing this would be a sub select.. but mysql doesnt support it... also, reading all the values into an array and then re-filtering the array would be waaaay to much over head ...

any other ideas?

[DR_LaRRY_PEpPeR]

Originally posted by kunal
any other ideas?

scoates' query didn't work?

[kunal]

Originally posted by DR_LaRRY_PEpPeR


scoates' query didn't work?

it did not.

[scoates]

Then I'm misunderstanding what you need.
You'll need to elaborate, I think (-:

S

[kunal]

Originally posted by scoates
Then I'm misunderstanding what you need.
You'll need to elaborate, I think (-:

S

hehe.. ok.. here we go :


ah... i tried that [ using the normail OR and AND ]but it dint work... since it has to match the first criteria, that is the TV properties.. and then from that set of results, select the cable properties...

the easy way of doing this would be a sub select.. but mysql doesnt support it... also, reading all the values into an array and then re-filtering the array would be waaaay to much over head ...

any other ideas?

[DR_LaRRY_PEpPeR]

Originally posted by kunal
since it has to match the first criteria, that is the TV properties.. and then from that set, select the cable properties...

the easy way of doing this would be a sub select..

no need for a sub-select. that's what the AND is for (black-and-white TV AND a quarterly or yearly subscription).

i just used scoates' query. looks like it does what you want to me.

mysql> SELECT * FROM test;
+----+----+-------+
| id | tv | cable |
+----+----+-------+
|  1 |  1 |     1 |
|  2 |  1 |     2 |
|  3 |  1 |     3 |
|  4 |  2 |     1 |
|  5 |  2 |     2 |
|  6 |  2 |     3 |
|  7 |  3 |     1 |
|  8 |  3 |     2 |
|  9 |  3 |     3 |
| 10 |  4 |     1 |
| 11 |  4 |     2 |
| 12 |  4 |     3 |
+----+----+-------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE tv IN(1,3) AND cable IN(2,3);
+----+----+-------+
| id | tv | cable |
+----+----+-------+
|  2 |  1 |     2 |
|  3 |  1 |     3 |
|  8 |  3 |     2 |
|  9 |  3 |     3 |
+----+----+-------+
4 rows in set (0.00 sec)