SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL IN operator SubQuery value, pls help

    my subquery return this value = 69, 656, 133, 0. the main query only return value for 69. how to get a relevant record for the four values. pls help.

    SELECT title, recipes.id
    FROM recipes
    WHERE id in (SELECT CONCAT(menudays.main1,', ',menudays.main2,', ',menudays.main3,', ',menudays.main4) as onecol FROM menudays WHERE menudays.date = '20110124' AND area_id=1
    )

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    first of all, you should not concatenate the values like that

    if you really want to use an IN subquery for this, then you would need to use a UNION in the subquery, like this --
    Code:
    SELECT title
         , recipes.id 
      FROM recipes 
     WHERE id IN 
           ( SELECT main1
               FROM menudays 
              WHERE menudays.date = '20110124' 
                AND area_id = 1
             UNION 
             SELECT main2
               FROM menudays 
              WHERE menudays.date = '20110124' 
                AND area_id = 1
             UNION 
             SELECT main3
               FROM menudays 
              WHERE menudays.date = '20110124' 
                AND area_id = 1
             UNION 
             SELECT main4
               FROM menudays 
              WHERE menudays.date = '20110124' 
                AND area_id = 1
           )
    the source of your difficulties is the menudays table, which should really be redesigned (to give you only one of several reasons, what happens if you add a 5th menu? all your queries have to change)

    even without redesigning, it's still better to use a join rather than an IN subquery --
    Code:
    SELECT recipes.title
         , recipes.id 
      FROM menudays 
    INNER
      JOIN recipes 
        ON recipes.id IN ( main1,main2,main3,main4 )
     WHERE menudays.date = '20110124' 
       AND menudays.area_id = 1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for that tip,


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
  •