CODEWITHSUNDEEP
mysqlsubquery
Matt Elhotiby
Matt Elhotiby

Reputation: 44086

Why doesnt this query grab the correct records?

I have this query:

SELECT
      bp.product_id,bs.step_number,
      p.price, pd.name as product_name
    FROM 
      builder_product bp
      JOIN builder_step bs ON bp.builder_step_id = bs.builder_step_id
      JOIN builder b ON bp.builder_id = b.builder_id
      JOIN product p ON p.product_id = bp.product_id
      JOIN product_description pd ON p.product_id = pd.product_id
      WHERE b.builder_id = '74' and bs.optional != '1'
    ORDER by bs.step_number, p.price

which is returning 

    88  1   575.0000    Lenovo Thinkcentre POS PC
    92  1   799.0000    Lenovo Thinkcenter Server - RAID Configured
    31  1   1599.0000   All-In-One - Lenovo Thinkcentre 23"
    63  2   169.0000    Lenovo Thinkvision 18.5" - LCD
    62  2   249.0000    Lenovo Thinkvision 22" - LCD
    244 2   559.0000    Touchscreen with MSR - Firebox 15"
    104 3   285.0000    Remote Order Printer - Epson
    65  3   355.0000    Barcode and Label Printer - Zebra 2" TT
    68  3   399.0000    Barcode And Label Printer - Zebra 4" DT
    254 4   106.0000    Cash Drawer - APG - 14X16 - Black
    251 4   195.0000    Cash Drawer - APG - 16X16 - Serial
    97  4   395.0000    Aldelo Lite
    97  5   395.0000    Aldelo Lite
    121 5   549.0000    Cash Register Express  - Pro
    279 5   849.0000    Aldelo Premium
    135 6   0.0000      Free!! Payment Processing Software
    191 6   349.0000    Integrated Payment Processing
    231 7   0.0000    1 User/Location - 8Am - 8Pm Mon - Fri Support Plan - Level 1
    232 7   0.0000    1 User/Location - 24 X 7 X 365 Support Plan - Level 1
    155 7   369.0000    Accessory - Posiflex 12.1" LCD Customer Display

what i need is the lowest price from each step so i assumed adding the sub query would work like this

SELECT
      bp.product_id,bs.step_number,
      p.price, pd.name as product_name
    FROM 
      builder_product bp
      JOIN builder_step bs ON bp.builder_step_id = bs.builder_step_id
      JOIN builder b ON bp.builder_id = b.builder_id
      JOIN product p ON p.product_id = bp.product_id
      JOIN product_description pd ON p.product_id = pd.product_id
      WHERE b.builder_id = '74' and bs.optional != '1'
      AND bp.builder_product_id = (
            SELECT builder_product_id
            FROM builder_product as alt
            WHERE alt.step_number = bp.step_number
            LIMIT 1
        )
    ORDER by bs.step_number, p.price

but i get this returned 

88  1   575.0000    Lenovo Thinkcentre POS PC
244 2   559.0000    Touchscreen with MSR - Firebox 15"
104 3   285.0000    Remote Order Printer - Epson
97  4   395.0000    Aldelo Lite
121 5   549.0000    Cash Register Express  - Pro
191 6   349.0000    Integrated Payment Processing
155 7   369.0000    Accessory - Posiflex 12.1" LCD Customer Display

Which is not correct because as you can see step #2 should be returning 

    63  2   169.0000    Lenovo Thinkvision 18.5" - LCD

since 169.000 is less then 559.000 any ideas how to change this

Upvotes: 1

Views: 81

Answers (2)

Fabrizio
Fabrizio

Reputation: 3776

Look into the GROUP BY and MIN. try this

SELECT
  bp.product_id,bs.step_number,
  MIN(p.price) as price,
  pd.name as product_name
FROM 
  builder_product bp
  JOIN builder_step bs ON bp.builder_step_id = bs.builder_step_id
  JOIN builder b ON bp.builder_id = b.builder_id
  JOIN product p ON p.product_id = bp.product_id
  JOIN product_description pd ON p.product_id = pd.product_id
WHERE b.builder_id = '74' and bs.optional != '1'
GROUP BY bp.product_id
ORDER by bs.step_number, p.price

Upvotes: 3

Joe C.
Joe C.

Reputation: 1538

Your subselect is returning the first entry returned, since you used LIMIT 1, however, the results there are not ordered. Try ordering the subselect by price (this may require a join with the products table to determine the prices).

Upvotes: 0

Related Questions