Select right price by products..

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • djpaul
    New Member
    • Oct 2006
    • 137

    Select right price by products..

    Hello,
    I have a database for my products.
    In there is a table with all the products and there is a table with te actual prices.
    I need to select a product and inner join the price table an get the latest (read latest id) from the price table.

    What is the best way?
    Code:
    Table structure:
    products:
    ID               int(3) PK
    Description     text
    value           varchar(250)
    lokation          varchar(40)
    brand_id          int(11)
    type_id          int(11)
    article_id       int(11)
    crediteur_id     int(11)
    loaned       int(11)
    repair       int(11)
    Boxed         int(11)
    barcode1         varchar(20)
    barcode2         varchar(20)
    artikelnr_supplier varchar(45)
    garantuee         int(11)
    picture            varchar(85)
    Code:
    Table price:
    id               int(11) PK
    producten_id     int(11)
    exclusive_price  decimal(8,2)
    verwijderings_bijdrage_id int(11)
    tax_tarrif      decimal(4,2)
    inclusive_price  decimal(8,2)
    bruto_profit      decimal(8,2)
    marge            decimal(4,2)
    advice_price     decimal(8,2)
    our_price      decimal(8,2)
    porto_costs     decimal(5,2)
    date            datetime
    Thanks!
    Regards,
    Paul
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    The best way is as you described it in your post,
    select a product and inner join the price table

    Comment

    • djpaul
      New Member
      • Oct 2006
      • 137

      #3
      Well, i have this query but i have 2 prices for this product.
      And it returns 2 records.

      Code:
      SELECT p.id, artikel, merk, type, onze_prijs, garantie, verwijderings_bijdrage.bedrag,
                                  (producten_historie.aantal_ingekocht - (p.uitgeleend + p.reparatie) - producten_historie.aantal_verkocht) AS Stock
                                  FROM producten as p 
                                  INNER JOIN merk ON p.merk_id = merk.id 
                                  INNER JOIN type ON p.type_id = type.id 
                                  INNER JOIN producten_historie ON p.id = producten_historie.producten_id 
                                  INNER JOIN artikel ON p.artikel_id = artikel.id 
                                  LEFT OUTER JOIN 
                                  (SELECT id, onze_prijs, producten_id, verwijderings_bijdrage_id
                                  FROM producten_prijzen 
                                  ORDER BY id DESC
                                  ) T                    
                                  ON p.id = T.producten_id
                          LEFT OUTER JOIN verwijderings_bijdrage ON T.verwijderings_bijdrage_id = verwijderings_bijdrage.id 
                          WHERE (merk.merk LIKE '%hp550%') OR (type.type LIKE '%hp550%') OR (barcode1 LIKE '%hp550%') OR
                          (barcode2 LIKE '%hp550%') OR (artikel.artikel LIKE '%hp550%') OR p.omschrijving LIKE '%hp550%'

      Comment

      • Rabbit
        Recognized Expert MVP
        • Jan 2007
        • 12517

        #4
        In your subquery where you select from the price table, use a subquery to filter for the max id where you group by the product id.

        Comment

        • djpaul
          New Member
          • Oct 2006
          • 137

          #5
          Well, done that and it''s working!
          Jees, what a query was that!

          Added this:
          Code:
          ...LEFT OUTER JOIN
            (SELECT id, onze_prijs, producten_id, verwijderings_bijdrage_id
                                      FROM(SELECT MAX(id) as maxid
                                      FROM(producten_prijzen)
                                            GROUP BY producten_id)
                                       AS x
               INNER JOIN producten_prijzen AS p ON p.id = x.maxid 
               ) T
             ON.......
          Thanks!

          Comment

          • Rabbit
            Recognized Expert MVP
            • Jan 2007
            • 12517

            #6
            Not a problem, you should mark your post as the answer for others that may come upon this thread.

            Comment

            Working...