Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts

    query for item on 2 paramaters

    I am stumped on how to do this query. I want to search a table and get the items that appear in the database with matching numbers in the variable column. So if I search for any fruit in this table that have been entered in this table for both 1 and 23, I should get Apple.

    Code:
    item_id     |    variable
    ------------+----------------
    grape       |     1
    ------------+----------------
    peach       |    23
    ------------+----------------
    orange      |    22
    ------------+----------------
    Apple       |     1
    ------------+----------------
    Apple       |    23
    ------------+----------------
    This query below gives me no results

    SELECT item_id FROM `connect_tables` JOIN items ON connect_tables.item_id = items.item_id
    WHERE connect_tables.term_id LIKE 1 AND connect_tables.item_id LIKE 23

    This query gives me too many entries: apples...grapes and peaches. I only want apples.

    SELECT item_id FROM `connect_tables` JOIN items ON connect_tables.item_id = items.item_id
    WHERE connect_tables.term_id LIKE 1 OR connect_tables.item_id LIKE 23

    I hope that made sense.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    Well, you didn't bother to show us what is in your connect_table so I will have to ignore that until you do.

    But what you want is not hard:

    Code:
    SELECT item_id, COUNT(*) AS howmany
    FROM items 
    WHERE variable IN ( 1, 23 )
    GROUP BY item_id
    HAVING howmany = 2
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    And NEVER use LIKE unless you use a wild card character on the right side of the LIKE. It just slows things down and is pointless.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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