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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with joining tables together.

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 codeind

nandini
Go to Top of Page

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 codeind

nandini



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.
Go to Top of Page

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_id
FROM po_items
JOIN items
ON po_items.item_id = items.item_id
AND items.price > 50.00
JOIN pos
ON pos.po_id = po_items.po_id
AND pos.vendor_id = 'ABC'
Go to Top of Page

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 bookjobs
JOIN pos
ON 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?



Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-06 : 00:34:57
--this?
SELECT 
FROM bookjobs b
JOIN (SELECT job_id, COUNT(DISTINCT vendor_id) cntVendors
FROM pos
GROUP BY job_id
HAVING COUNT(DISTINCT vendor_id) > 1) j
ON b.job_id = j.job_id


--
Chandu
Go to Top of Page
   

- Advertisement -