SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: mysql and conditions
-
Mar 22, 2002, 12:49 #1
mysql and conditions
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?i dunno...
-
Mar 22, 2002, 13:31 #2
- Join Date
- Nov 2001
- Location
- Montreal
- Posts
- 794
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code:SELECT * FROM [table] WHERE tv IN (1,3) AND cable IN (2,3)
S
-
Mar 22, 2002, 14:10 #3
umm.. how does the IN thing work? ive tried finding it in the manual.. but couldnt
i dunno...
-
Mar 22, 2002, 14:16 #4
- Join Date
- Nov 2001
- Location
- Montreal
- Posts
- 794
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
it's pretty much a simplified OR clause:
Code:SELECT * FROM [table] WHERE (tv = 1 OR tv = 3) AND (cable = 2 OR cable = 3)
(especially coupled with implode(...))
S
-
Mar 22, 2002, 14:28 #5
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?i dunno...
-
Mar 22, 2002, 17:34 #6
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by kunal
any other ideas?- Matt** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
"Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR
-
Mar 23, 2002, 00:35 #7Originally posted by DR_LaRRY_PEpPeR
scoates' query didn't work?
it did not.i dunno...
-
Mar 23, 2002, 01:03 #8
- Join Date
- Nov 2001
- Location
- Montreal
- Posts
- 794
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Then I'm misunderstanding what you need.
You'll need to elaborate, I think (-:
S
-
Mar 23, 2002, 01:23 #9Originally posted by scoates
Then I'm misunderstanding what you need.
You'll need to elaborate, I think (-:
S
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?
i dunno...
-
Mar 23, 2002, 17:05 #10
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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..
i just used scoates' query. looks like it does what you want to me.
Code: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)
Bookmarks