SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining two queries

    Code MySQL:
    $query1 = "SELECT category1
    				, category2
    				, category3
     
    			FROM listing
    			WHERE username = '{$username}'
    			AND id = {$listing_id}  ";

    Code MySQL:
    $query2 = "SELECT meaning 
    			FROM category_dict
    			WHERE abbreviation in ('".$rows['category1']."'
    									,'".$rows['category2']."'
    									,'".$rows['category3']."')";

    I have two queries as above

    The table structure is as here:

    listing(title, category1, category2, category3, price, closing)

    category_dict(abbreviation, meaning)

    I need help to combine them correctly.
    Last edited by runrunforest; Dec 22, 2008 at 02:10.

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,147
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    Are you looking for UNION?
    PHP Code:
    $query "SELECT title
                    , category1
                    , category2
                    , category3
                    , price
                    , closing
                FROM listing
                WHERE username = '
    {$username}'
                AND id = 
    {$listing_id}
               UNION
               SELECT meaning 
                FROM category_dict
                WHERE abbreviation in ('"
    .$rows['category1']."'
                                        ,'"
    .$rows['category2']."'
                                        ,'"
    .$rows['category3']."')"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Mittineague

    But the $rows['category1'] ,$rows['category2'], $rows['category3'] only obtained after the query1, using fetch result.

    As we see in your example, the $rows variables weren't yet define, we can not use them in the query.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Use join
    Code:
    SELECT 
        category1
      , category2
      , category3
      , meaning
    FROM listing
    INNER JOIN category_dict
    ON abbreviation in (category1, category2, category3)
    WHERE username = '{$username}'
    AND   id = {$listing_id}

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido,

    The query gives insufficient resources for mysql_fetch_array.

    I still need help.

    Let me detail the need again.

    In table listing(id,title, category1, category2, category3), i want to select the row where id = $listing_id. This query should give back only one row.

    Now, in table category_dict(abbreviation, meaning), I want to select meaning, where category1= abbreviation, category2 = abbreviation, category3 = abbreviation. This should give back 3 meanings

    Please help!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT l.id
         , l.title
         , c1.meaning AS meaning1
         , c2.meaning AS meaning2
         , c3.meaning AS meaning3
      FROM listing AS l
    LEFT OUTER
      JOIN category_dict AS c1
        ON c1.abbreviation = l.category1
    LEFT OUTER
      JOIN category_dict AS c2
        ON c2.abbreviation = l.category2
    LEFT OUTER
      JOIN category_dict AS c3
        ON c3.abbreviation = l.category3
     WHERE l.username = '{$username}'           
       AND l.id = {$listing_id}
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was about to go back and post your code is syntax error, but after four days of struggling to find out why a beautifully sense making code can not work. Finally, its because the collation differentiation in some field in two tables.

    I am back to good night sleep as normal.

    @Guido, sorry, your code probably works too, I haven't tested it again but I'm sure it should work now.

    Merry Christmas!


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
  •