SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    UK
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Help: Data from Two Tables

    I have two tables:

    ic_orders
    Contains orders placed for products in the ic_products table.

    id mediumint (8)
    product mediumint (8)
    customer_id mediumint( 8)
    timestamp int (10)
    status tinyint (1)
    total decimal (10,2)

    ic_products
    Contains a list of available store products.

    product_id mediumint (8)
    owner_id mediumint (8)
    product_price decimal(10,2)
    product_state tinyint(1)
    product_title varchar(255)
    product_desc text
    product_date int(10)
    product_agreement mediumint(8)

    The prominant relationship here is the product -> product_id

    I then have a variable, $mbr_config['id']

    What I want to accomplish is:

    Retrieve all records from the ic_orders table, where the product = product_id AND owner_id in the ic_products table = $mbr_config['id']

    In English: Get order details from 'ic_orders' table, only where the owner_id in the 'ic_products' table equals the $mbr_config['id']

    I hope I've explained that clearly enough. It's been driving me nuts!

    What I feebly attempted was:

    Code:
    $query = "	SELECT 
    					o.id,
    					o.product,
    					o.timestamp,
    					o.total,
    					p.owner_id
    				FROM
    					".INV_PREFIX."orders o
    				INNER JOIN ".INV_PREFIX."products p 
    				WHERE p.owner_id =  ".$mbr_config['id']."
    			";
    but that gave me each record about 4 times!

  2. #2
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    UK
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm I think I may have figured it, but if someone with a little more experience could just double-check:

    Code:
    $query = "	SELECT 
    					o.id,
    					o.product,
    					o.timestamp,
    					o.total
    				FROM
    					".INV_PREFIX."orders o
    				LEFT JOIN ".INV_PREFIX."products p 
    				ON 
    					(p.product_id=o.product)
    				WHERE 
    					p.owner_id = ".$mbr_config['id']." 
    				ORDER BY 
    					o.timestamp DESC
    			";

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    should work, but could be tuned just a wee bit

    you actually don't want a LEFT OUTER JOIN because there should be no instances of an order where the product value isn't in the products table product_id column

    also, i would make the owner a join condition, rather than a filter condition, although in an INNER JOIN the difference is moot
    Code:
    SELECT o.id
         , o.product
         , o.timestamp
         , o.total
      FROM ".INV_PREFIX."orders o
    inner
      JOIN ".INV_PREFIX."products p 
        ON p.product_id = o.product
       and p.owner_id = ".$mbr_config['id']." 
    ORDER 
        BY o.timestamp DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    UK
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks again. Slowly I'm getting the hang of these JOINS. They drive me insane most of the time.

    also, i would make the owner a join condition, rather than a filter condition, although in an INNER JOIN the difference is moot
    May be moot, but looking at it, it does appear more sensible in context.

    Thank you again. Much appreciated.


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
  •