SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: help with query

  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with query

    I have the following tables:

    products
    +-----------+-------------+-----------+---------------+
    | product_id | attribute_id | vendor_id | product_name |
    +-----------+-------------+-----------+---------------+

    attributes
    +-------------+-----+--------+
    | attribute_id | size | color |
    +-------------+-----+--------+

    vendors
    +----------+--------+------+
    | vendor_id | name | www |
    +----------+--------+------+

    Now i want to select product_name from the products-table, and the fields size and color from the attributes-table where the attribute_id match the attribute_id in the products-table. And also the fields name and www from the vendors-table where the vendor_id match the vendor_id in the products-table. All this in one row so that i can select all data from the different tables in one query, is this possible?

  2. #2
    SitePoint Addict bkennedy's Avatar
    Join Date
    Aug 2004
    Location
    michigan
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you might want to look into subqueries or joins...

    http://www.fluffycat.com/sql/sql-join.html
    ----------------
    bob.kennedy
    ----------------

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i think i got it right, atleast if i look at the output i get i got it right but im just checking back with you guys to see if you think it looks ok and optimized. heres the sql:

    SELECT P.*, A.*, V.*
    FROM products P, attributes A, vendors V
    WHERE A.attribute_id IN ( SELECT attribute_id FROM products WHERE P.attribute_id = A.attribute_id )
    AND V.vendor_id IN ( SELECT vendor_id FROM products WHERE P.vendor_id = V.vendor_id )
    ORDER BY P.date_available

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select P.product_name 
         , P.date_available
         , A.size
         , A.color
         , V.name
         , V.www
      from products P
    inner
      join attributes A
        on P.attribute_id = A.attribute_id 
    inner
      join vendors V 
        on P.vendor_id = V.vendor_id 
    order 
        by P.date_available
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •