Help with joining tables together.

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

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.

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'

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?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources