SitePoint Sponsor

User Tag List

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

    combine two queries together

    table listing(id,category)

    table listing_photo(listing_id,path)

    query for these two tables

    Code MySQL:
    "SELECT a.* 
    	   , b.*
       FROM listing a 
       LEFT OUTER 
       JOIN listing_photo b 
         ON b.listing_id = a.id
      WHERE a.id = $id"

    table dictionary(abbreviation, meaning)

    query for this table

    Code MySQL:
    "SELECT * 
    FROM dictionary
    WHERE abbreviation LIKE 'a.category%' "

    Can you help me combine these two queries ?
    Last edited by runrunforest; Jan 5, 2009 at 01:28. Reason: together

  2. #2
    SitePoint Addict
    Join Date
    Jun 2006
    Location
    Durban, South Africa
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Give this a go

    Code:
    SELECT *
    FROM dictionary
    WHERE abbreviation LIKE (SELECT a.category
                                       FROM listing a
                                       WHERE a.id = $id)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT a.* 
         , b.*
         , d.*
      FROM listing AS a 
    LEFT OUTER 
      JOIN listing_photo AS b 
        ON b.listing_id = a.id
    LEFT OUTER
      JOIN dictionary AS d
        ON d.abbreviation LIKE CONCAT(a.category,'%')
     WHERE a.id = $id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much but that didn't help.

    The "%" needs to be in the query

    This is the reply for poizn answer

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

    The correct way to add a query varible to the query

    Code PHP:
    if(isset($a)) 
    { 
    	$a_select = ", d.meaning ";
    	$a_query = " LEFT OUTER JOIN dictionary d ON d.abbr = a.cat";
    }
    else
    {
    	$a_select = '';
    	$a_query = '';
    }

    Code MySQL:
    "SELECT a.* 
    	   , b.*
         {$a_select}
       FROM listing a 
       LEFT OUTER 
       JOIN listing_photo b 
         ON b.listing_id = a.id
    	AND b.key =1
    	{$a_query}
      WHERE a.id = $id"

    I put the variable $a_query in the query like that, would that be correct ? As I have unexpected error executing that query.

  6. #6
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try putting as...

    sumthing like this FROM listing AS a ... JOIN listing_photo AS b..

    not quite sure though...

    or may be its the curly braces...

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah thanks for reply. Don't bother about other syntax, assuming everything works fine untill I put the variable $a_query in there.

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT a.* 
         , b.*
         , d.*
      FROM listing AS a 
    LEFT OUTER 
      JOIN listing_photo AS b 
        ON b.listing_id = a.id
    LEFT OUTER
      JOIN dictionary AS d
        ON d.abbreviation LIKE CONCAT(a.category,'%')
     WHERE a.id = $id
    This is exactly what I want.

    Your MYSQL skill is like Michael Jordan in basketball, Schumacher in F1.


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
  •