Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with joining tables together.		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rossy83
Starting Member

Australia
4 Posts
Posted - 05/04/2013 :  19:33:13  Show Profile  Reply with Quote
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

Bangladesh
1 Posts
Posted - 05/04/2013 :  22:16:23  Show Profile  Reply with Quote
show the every match 'ger' has goal matchid ,match dateand scroced by join codeind

nandini
Go to Top of Page

Rossy83
Starting Member

Australia
4 Posts
Posted - 05/04/2013 :  22:23:41  Show Profile  Reply with Quote
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

Australia
4 Posts
Posted - 05/05/2013 :  00:46:31  Show Profile  Reply with Quote
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

Australia
4 Posts
Posted - 05/05/2013 :  05:44:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2242 Posts
Posted - 05/06/2013 :  00:34:57  Show Profile  Reply with Quote
--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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000