join and subqueries! - SQL Server Forums

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

join and subqueries!

Author

Topic

saravanataee
Starting Member

4 Posts

Posted - 2013-05-20 : 10:01:10

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-20 : 11:15:59

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 01:13:42

-- 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

saravanataee
Starting Member

4 Posts

Posted - 2013-05-21 : 01:46:49

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 02:05:43

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

saravanataee
Starting Member

4 Posts

Posted - 2013-05-21 : 03:03:01

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 03:06:51

[code]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[/code]

--
Chandu

saravanataee
Starting Member

4 Posts

Posted - 2013-05-21 : 03:19:40

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 06:04:37

>> 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources