MS SQL

sql - how to

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Mar 2008

Posts: 6

Reputation: Tara423 is an unknown quantity at this point

Solved Threads: 0

Tara423

Offline

Newbie Poster

sql - how to

Mar 12th, 2008

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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: sql - how to

Mar 12th, 2008

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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#
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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#
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#

Last edited by hollystyles; Mar 12th, 2008 at 9:47 am.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: sql - how to

Mar 12th, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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]
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]

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Mar 2008

Posts: 6

Reputation: Tara423 is an unknown quantity at this point

Solved Threads: 0

Tara423

Offline

Newbie Poster

Re: sql - how to

Mar 12th, 2008

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

•

Join Date: Mar 2008

Posts: 6

Reputation: Tara423 is an unknown quantity at this point

Solved Threads: 0

Tara423

Offline

Newbie Poster

Re: sql - how to

Mar 12th, 2008

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

Thanks,
Tara

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: sql - how to

Mar 12th, 2008

Excellent. Please mark thread solved

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Mar 2008

Posts: 6

Reputation: Tara423 is an unknown quantity at this point

Solved Threads: 0

Tara423

Offline

Newbie Poster

Re: sql - how to

Mar 12th, 2008

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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: sql - how to

Mar 12th, 2008

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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;
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;

Last edited by hollystyles; Mar 12th, 2008 at 1:41 pm.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Mar 2008

Posts: 6

Reputation: Tara423 is an unknown quantity at this point

Solved Threads: 0

Tara423

Offline

Newbie Poster

Re: sql - how to

Mar 12th, 2008

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

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

Once again, thank you for your help.

Tara.

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: sql - how to

#10

Mar 13th, 2008

•

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.

Last edited by hollystyles; Mar 13th, 2008 at 5:56 am.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey