Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Rossy83
Starting Member
4 Posts |
Posted - 2013-05-04 : 19:33:13
|
Hi guys,I'm new to SQL and am currently doing a Database and Conceptual modelling subject. I was hoping for some help with the following question..."Find all those po_items with price exceeding 50.00 associated with one or more pos from vendor_id ABC"I have included a screenshot of the relevant tables im working with. I've tried to find the answer using nested queries without quiet getting the right answer and now I'm thinking i need to join the tables together however i still can't quiet get it right. Any help would be much appreciated. |
|
debu
Starting Member
1 Post |
Posted - 2013-05-04 : 22:16:23
|
show the every match 'ger' has goal matchid ,match dateand scroced by join codeindnandini |
 |
|
Rossy83
Starting Member
4 Posts |
Posted - 2013-05-04 : 22:23:41
|
quote: Originally posted by debu show the every match 'ger' has goal matchid ,match dateand scroced by join codeindnandini
Sorry mate, I didn't understand your answer. I understand what the question is asking I'm just unsure how to go about writing the querie. |
 |
|
Rossy83
Starting Member
4 Posts |
Posted - 2013-05-05 : 00:46:31
|
I have managed to get an answer which as far as i can tell is correct. SELECT po_items.job_id, po_items.po_id, po_items.item_id, po_items.quantity, items.price, pos.vendor_idFROM po_itemsJOIN items ON po_items.item_id = items.item_id AND items.price > 50.00JOIN pos ON pos.po_id = po_items.po_id AND pos.vendor_id = 'ABC' |
 |
|
Rossy83
Starting Member
4 Posts |
Posted - 2013-05-05 : 05:44:15
|
Now I'm stuck on another one."List all bookjobs information for those jobs which have pos for more than one vendor"So far i've come up with...SELECT *FROM bookjobsJOIN posON bookjobs.job_id = pos.job_id;The result is below. I cant work out how to isolate the bookjobs are associated with more than 1 vendor? |
 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-06 : 00:34:57
|
--this?SELECT FROM bookjobs bJOIN (SELECT job_id, COUNT(DISTINCT vendor_id) cntVendors FROM pos GROUP BY job_id HAVING COUNT(DISTINCT vendor_id) > 1) jON b.job_id = j.job_id --Chandu |
 |
|
|
|
|