sql structure

Tara14

Hi, I have two table like this:

quote_tb:

ID item vendor quote
11 a v1 100
11 a v2 200
11 b v1 400
11 b v2 300
11 c v1 555
11 c v2 777
22 a v1 122
22 a v2 222
22 b v1 322
22 b v2 422
22 c v1 555
22 c v2 777

vendor_tb

name phone# fax# address#
v1 1111 2222 3, x street
v2 1212 2323 4, m street

I am trying to write an sql that given me the following result:

lowestQ_tb
ID item vendor quote phone# fax#
11 a v1 100 1111 2222
11 b v2 300 1212 2323
11 c v1 555 1111 2222

i.e for each of the items a,b,c having the id(11) select the vendors that have the lease quote and then get the phone and fax numbers of the vendors form the table vendor_tb. To get the first part i.e. lowest quote for each item, this is the sql I use:

SELECT f.item, f.vendor, f.quote
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice

which gives me the result:

ID item vendor quote
11 a v1 100
11 b v2 300
11 c v1 555

I don't know how to take it further form here to get lowestQ_tb. Please can you tell me how to get the above desired result i.e tabele _lowestQ_tb_. Is it possible to do such a thing?

Thanks, Tara

andyharman

How about:

select Q.ID, Q.item, Q.vendor, Q.Quote, V.phone, V.fax
from (
  select ID, item, min(quote) AS MinQuote
  from quote_tb
  where ID = 11
  group by ID, item
) AS MinQ
inner join quote_tb Q
  on Q.ID = MinQ.ID
  and Q.item = MinQ.item
  and Q.quote = MinQ.quote
inner join vendor_tb AS V
  on V.name = Q.vendor
order by Q.ID, Q.item

Note that if two different suppliers give the same quote for the same item then they will both be displayed. You could correct that using:

...
from (
  select Q.ID, Q.item, min(vendor) AS MinVendor
  from (
    select ID, item, min(quote) AS MinQuote
    from quote_tb
    where ID = 11
    group by ID, item
  ) AS MinQ
  inner join quote_tb AS Q
    on Q.ID = MinQ.ID
    and Q.item = MinQ.item
    and Q.quote = MinQ.quote
  group by Q.ID, Q.item
) AS MinS
inner join quote_tb Q
  on Q.ID = MinS.ID
  and Q.item = MinS.item
  and Q.vendor = MinS.MinVendor
...

It may help you to run each sub-query in turn to see how it is built-up. If you are using SQL-Server 2005 then you may be able to rewrite this more concisley using the windowing-functions. Regards Andy

Syed Mehroz Alam

See my addition in bold, I hope this will help you.

SELECT f.item, f.vendor, f.quote, v.phone, v.fax
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
inner join vendor_tb as v on f.vendor = v.name

Regards, Mehroz

Tara14

Thank you for the reply. But for some reason, whatever way I try it doesn't seem to be working. I am connecting to MS Access database through VC++. In whatever way I try to add an extra 'inner join' it gives me the error : "Syntax error(missing operator) in query expression". If I remove the extra 'inner join' its all fine. Can't understand why. Is it something to do with the formatting? Thanks, Tara

Thanks, Tara

Tara14

I got it. Just needed to place a parenthesis.

SELECT f.item, f.vendor, f.quote, v.phone, v.fax
FROM ((SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice)
inner join vendor_tb as v on f.vendor = v.name

Thank you for the help.

Thanks, Tara

Tara14

Thank you for the reply. I am basically connecting to MS Access through my VC++ code. I tried the code that you have given and it works with a few parenthesis added. Thank you.

Thanks, Tara