SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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...

  2. #2
    purple monkey dishwasher scoates's Avatar
    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)
    Is that what you're looking for?

    S

  3. #3
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    umm.. how does the IN thing work? ive tried finding it in the manual.. but couldnt
    i dunno...

  4. #4
    purple monkey dishwasher scoates's Avatar
    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)
    IN is great for doing big sets.
    (especially coupled with implode(...))

    S

  5. #5
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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, 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...

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kunal
    any other ideas?
    scoates' query didn't work?
    - 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

  7. #7
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR


    scoates' query didn't work?

    it did not.
    i dunno...

  8. #8
    purple monkey dishwasher scoates's Avatar
    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

  9. #9
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    i dunno...

  10. #10
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    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..
    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.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •