SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict suzkaw's Avatar
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    338
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select from three tables with one having more than one value.

    I have three tables setup that I am querying.

    Products, Users, and products_fields.

    The product_fields table is setup like this:
    Code:
    CREATE TABLE `products_fields` (
      `pID` int(11) NOT NULL default '0',
      `fID` int(11) NOT NULL default '0',
      `sValue` text NOT NULL
    )
    The sValue is what I want to match up. It has items like year, make, etc.

    So for one record it could have:
    pID=2
    fID=1
    sValue=suzuki
    pID=2
    fID=2
    sValue=2005

    Then for my query I have:
    Code:
    SELECT p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.description, p.price, p.dateadded, p.expiration, p.pBold, p.pHighlighted, u.state, u.city, u.country, u.phone
    FROM products AS p
    INNER JOIN products_fields ON p.id = products_fields.pID
    LEFT JOIN users AS u ON p.owner = u.id
    WHERE p.expiration > NOW( )
    AND p.display = 'Y'
    AND p.section LIKE '%10%'
    AND (
    p.price
    BETWEEN 0
    AND 9999999
    )
    AND (
    sValue = 'suzuki'
    OR sValue = '2005'
    )
    GROUP BY p.id, p.owner, p.title, p.featured, p.section, p.description, p.dateadded, p.expiration, u.state, u.city, u.country
    ORDER BY featured ASC , p.dateadded DESC
    LIMIT 0 , 30
    The problem is it always returns anything with suzuki no matter what year.

    Do you have any ideas to reformat this to make it work?
    Thanks Eric.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select p.id
         , p.owner
         , p.title
         , p.featured
         , p.section
         , p.shortDescription
         , p.description
         , p.price
         , p.dateadded
         , p.expiration
         , p.pBold
         , p.pHighlighted
         , u.state
         , u.city
         , u.country
         , u.phone
      from products as p
    inner 
      join products_fields 
        on p.id = products_fields.pID
    left 
      join users as u 
        on p.owner = u.id
     where p.expiration > now()
       and p.display = 'Y'
       and p.section like '%10%'
       and p.price between 0 and 9999999
       and (
           sValue = 'suzuki'
        or sValue = '2005'
           )
    group 
        by p.id
         , p.owner
         , p.title
         , p.featured
         , p.section
         , p.description
         , p.dateadded
         , p.expiration
         , u.state
         , u.city
         , u.country
    having count(distinct sValue) = 2     
    order 
        by p.featured asc 
         , p.dateadded desc
    limit  0, 30
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict suzkaw's Avatar
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    338
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank You!
    Thanks Eric.


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
  •