SQL Server Forums
Profile | Register | 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
 join and subqueries!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

saravanataee
Starting Member

India
4 Posts

Posted - 05/20/2013 :  10:01:10  Show Profile  Reply with Quote
Dear all,
I am having the following problem and did not know the solution.

I have three related tables.

product
purchase_order
git_order.

In product table

product_id product_name
1 barrings
2 wheel rim
3 rodd

purchase_order

po_id product_id package_id numbers received etc.
1 1 2 100 50
2 2 4 250 60
3 1 1 200 40
4 1 3 200 40
5 2 5 200 40
6 1 1 200 40
7 1 5 200 40


git_order
git_id product_id package_id numbers received etc.
1 1 2 100 50
2 2 4 250 60
3 1 1 200 40
4 1 3 200 40
5 2 5 200 40
6 2 1 200 40
7 1 5 200 40


The output i want is.

product_id sum(numbers)in 2ndtable | sum(numbers)in 3rdtable
1 900 700
2 450 650


i need a query to do this. i m new to this. i spent lot of time in this but couldnt resolve. please help me.


Saravanan

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 05/20/2013 :  11:15:59  Show Profile  Reply with Quote
Here is one way of doing it:
SELECT
	p.product_id,
	po.ponumbers,
	g.gonumbers
FROM
	product p
	OUTER APPLY
	(SELECT SUM(numbers) AS ponumbers FROM purchase_order po
	WHERE po.product_id = p.product_id) AS po
	OUTER APPLY
	(SELECT SUM(numbers) AS gonumbers FROM git_order g
	WHERE g.product_id = p.product_id) AS g
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/21/2013 :  01:13:42  Show Profile  Reply with Quote
-- Alternate is as follows:

SELECT
	p.product_id,
	po.ponumbers,
	g.gonumbers
FROM
Product p
JOIN (SELECT Product_id, SUM(numbers) AS ponumbers 
		FROM purchase_order
		GROUP BY product_id ) AS po ON p.product_id = po.product_id
JOIN (SELECT product_id, SUM(numbers) AS gonumbers 
		FROM git_order
		GROUP BY product_id) AS g ON p.product_id = g.product_id


--
Chandu
Go to Top of Page

saravanataee
Starting Member

India
4 Posts

Posted - 05/21/2013 :  01:46:49  Show Profile  Reply with Quote
Hi bandi & james K,
I appreciate your help. It does solved my issue. But one more thing.

the output of the query contains the following array.
Array ( [0] => 1 [product_id] => 1 [1] => 19103 [ponumbers] => 19103 [2] => 18410 [gonumbers] => 18410 )

When i printed num of rows using mysql_num_rows(), it says 23 rows in it.

Two questions i have here,
1. how can i print the values in this format.

product_id ponumbers gonumbers
1 19103 18410
2 xxxxx xxxxx
3 xxxxx xxxxx
4 xxxxx xxxxx

as long as how many product_id's are available in my product table.

2. I now 1ly see product_id =1 and its ponumbers and gonumbers. how do i get the remaining all??

please help me clear about two questions?

Thanks a lot!!

Saravanan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/21/2013 :  02:05:43  Show Profile  Reply with Quote
2nd question answer:
SELECT
	p.product_id,
	po.ponumbers,
	g.gonumbers
FROM
Product p
LEFT JOIN (SELECT Product_id, SUM(numbers) AS ponumbers 
		FROM purchase_order
		GROUP BY product_id ) AS po ON p.product_id = po.product_id
JOIN (SELECT product_id, SUM(numbers) AS gonumbers 
		FROM git_order
		GROUP BY product_id) AS g ON p.product_id = g.product_id


1st Answer:
In SQL Server, we can display result set directly ( no need of arrays)

I think you are using MySQL/PHP, not the SQL Server.... This is SQL Server forum...
You can try your luck by posting MySQL/PHP Forums...
EDIT: Refer this link
http://www.php.net/manual/en/refs.database.php
--
Chandu

Edited by - bandi on 05/21/2013 02:07:07
Go to Top of Page

saravanataee
Starting Member

India
4 Posts

Posted - 05/21/2013 :  03:03:01  Show Profile  Reply with Quote
Hi bandi,
Thanks for the quick reply. Still, the answer for 2nd question is not solved my prob. My product table as stated in original question has atleast 3 products.

But the query result contain's details associated to only one product that is product_id=1. Why it did not contain remaining. I expect this result.
Product ID PO Count GIT Count
1 19103 18410
2 xxxxx xxxxx
3 xxxxx xxxxx


as the output of the entire query..!
Any help.



Saravanan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/21/2013 :  03:06:51  Show Profile  Reply with Quote
SELECT
	p.product_id,
	po.ponumbers,
	g.gonumbers
FROM
Product p
LEFT JOIN (SELECT Product_id, SUM(numbers) AS ponumbers 
		FROM purchase_order
		GROUP BY product_id ) AS po ON p.product_id = po.product_id
LEFT JOIN (SELECT product_id, SUM(numbers) AS gonumbers 
		FROM git_order
		GROUP BY product_id) AS g ON p.product_id = g.product_id



--
Chandu
Go to Top of Page

saravanataee
Starting Member

India
4 Posts

Posted - 05/21/2013 :  03:19:40  Show Profile  Reply with Quote
Hi bandi,
Still i didnt get the output that i wanted.

As the result of the new query join's product_id=1 even if it doesnt present in 2nd table.

I expect the query to get result for 'n' number of product's that i have in my product table.

It still pick only 1st product.

Array ( [0] => 1 [product_id] => 1 [1] => 19103 [ponumbers] => 19103 [2] => 18410 [gonumbers] => 18410 )

but the num of rows now returns 27.

any guess!



Saravanan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/21/2013 :  06:04:37  Show Profile  Reply with Quote
>> Array ( [0] => 1 [product_id] => 1 [1] => 19103 [ponumbers] => 19103 [2] => 18410 [gonumbers] => 18410 ) ?

The above is not the SQL Server syntax .....
You should post in relevant forum for quick response...

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.11 seconds. Powered By: Snitz Forums 2000