Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

sql - how to do this?

tara423tara423 Member Posts: 1
Hi,

I have two tables like this:

[code][b][color=Red]quote_tb:[/color][/b]
[b]
[u]ID item vendor quote[/u][/b]
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
[b]
[color=Red]vendor_tb[/color]
[u]
name phone# fax# address#[/u][/b]
v1 1111 2222 3, x street
v2 1212 2323 4, m street[/code]

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

[code][b][color=Red]lowestQ_tb[/color][/b]

[u][b]ID item vendor quote phone# fax#[/b][/u]
11 a v1 100 1111 2222
11 b v2 300 1212 2323
11 c v1 555 1111 2222[/code]

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 [color=Red]vendor_tb[/color].

To get the first part i.e. lowes quote for each item, this is the sql I use:

[code]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

This gives me the result:
[b][u]
ID item vendor quote[/u][/b]
11 a v1 100
11 b v2 300
11 c v1 555
[/code]
I don't know how to take it further form here. Please can you tell me how to get the above desired result i.e table [color=Red]lowestQ_tb[/color]?. Is it possible to do such a thing?

Thanks.
Tara

Comments

  • achilleas22achilleas22 Member Posts: 9
    You can put this one which the same as yours but i puted one more line


    SELECT f.item, f.vendor, f.quote,[b]phone,fax[/b]
    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 quote = x.minprice [b]inner join
    vendor_tb on name=vendor[/b];
Sign In or Register to comment.