sql - how to

[Tara423]

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

This 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. Please can you tell me how to get the above desired result i.e table lowestQ_tb. Is it possible to do such a thing?

Thanks.
Tara

[hollystyles]

Firstly just join the quote_tb to the vendor_tb. That gives the vendor details for ALL quotes.

Then use GROUP BY (to merge up all the repeating colums (ID, item, name, phone#, fax#) and the aggragate function MIN on the quote column to reduce the result set to just the lowest (MIN) quote amounts.

select 
    q.ID, 
    q.item, 
    v.name, 
    min(q.quote) minprice, 
    v.phone#, 
    v.fax# 
from 
    quote_tb q join vendor_tb v on q.vendor = v.name 
group by 
    q.ID, 
    q.item, 
    v.name, 
    v.phone#, 
    v.fax#

That gives ALL minimum quotes, if you want just a particular quote ID just add a where clause: where q.ID = 11

select 
    q.ID, 
    q.item, 
    v.name, 
    min(q.quote) minprice, 
    v.phone#, 
    v.fax# 
from 
    quote_tb q join vendor_tb v on q.vendor = v.name 
where 
    q.ID = 11 
group by 
    q.ID, 
    q.item, 
    v.name, 
    v.phone#, 
    v.fax#

[hollystyles]

Hmm re-reading your post that may not be quite what you want, you want lowest quote regardless of vendor I think.

select 
	q.[ID], 
	q.item,
	v.[name], 
	mq.minprice, 
	v.phone#, 
	v.fax#  
from 
	quote_tb q 
	join 
	(
		select 
			[ID], 
			item,  
			min(quote) minprice 
		from 
			quote_tb 
		group by 
			[ID], item
	) mq on q.[ID] = mq.[ID] 
		and q.item = mq.item 
		and q.quote = mq.minprice 
	join vendor_tb v on q.vendor = v.[name]

[Tara423]

Thank you for the reply.

I tried the sql as you have given. It gives the phone and fax details of the vendors but it also lists down all the items. It is not selecting only those items that have the mim quote.

Kindly advice.

Thanks,
Tara

[Tara423]

OOPS. please ignore the above post. I did not see your 2nd post.

Thanks,
Tara

[hollystyles]

Excellent. Please mark thread solved :)

[Tara423]

Mission unsuccessful!

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?

Kindly advise.

Thanks,
Tara

[hollystyles]

MS Access !! yuck !! You didn't say you were using Access, and as this is a MS SQL Forum I used TSQL.

Ok just for you I have recreated everything in Access and tweaked the SQL. Basically removed all formating, changed joins to INNER JOIN, added the AS keword where aliases were being used , bracketed the multiple column joins between quote_tb and the sub-query table mq and added the trailing semi-colon.

SELECT quote_tb.ID, quote_tb.item, vendor_tb.name, mq.minprice, vendor_tb.[phone#], vendor_tb.[fax#]
FROM (quote_tb INNER JOIN (select [ID], item,  min(quote) as minprice from quote_tb group by [ID], item) mq ON (quote_tb.quote = mq.minprice) AND (quote_tb.item = mq.item) AND (quote_tb.ID = mq.ID)) INNER JOIN vendor_tb ON quote_tb.vendor = vendor_tb.name;

[Tara423]

Oh my God! It worked! I can't tell you how very very happy I am. :icon_smile:

Actually, I just assumed that that the sql MS Access uses is going to be same as MS sql :$

Thanks a billion for your patience and help.

Gosh! How do you make these sql statements? Its so confusing and complex to me :sad:

Once again, thank you for your help.

Tara.

[hollystyles]

Actually, I just assumed that that the sql MS Access uses is going to be same as MS sql

There is a SQL standard but each implementatin has it's own quirks on top of that. The only cure for this is experience. INNER JOIN is the assumed default in TSQL so you can be lazy and just type JOIN, Access is less forgiving I guess but essentially they are the same.

Gosh! How do you make these sql statements? Its so confusing and complex to me

He he, yes it's like that for all of us, just got to keep reading and learning and using. Again experience will eventually win out.

I got to the final solution not because I know Access's SQL quirks of by heart. I created the subquery first in design view in Access (the group by on quote_db) and saved it. Then created a new one in design view, added the quote_tb and vendor-tb tables and then the subquery I had just saved previously, dragged the columns between them with the mouse to create the joins, then from the view menu chose SQL View. That reminded me how much Access likes brackets! then I just merged the SQL View of the seperate subquery into the main query using cut and paste to make it all one query statement.

[Tara423]

So, now I know a good way to test the 'yuck' Access sql syntax!

Thank you for the help.

Cheers,
Tara