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:
Quote ...
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:
Quote ...
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?
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.
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.
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?
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.
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.
Quote ...
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.
Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.
This thread is more than three months old
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Offline 
Marked 
