Join 2 queries - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join 2 queries

Author

Topic

Raoulh79
Starting Member

24 Posts

Posted - 2008-01-15 : 07:35:01

Dear All,

I want to join this 2 queries in order to get the result i want:

select t1.sz_item_ref_no, t1.sz_description, sum(t1.n0_quantity)
from rdb_log_item as t1
where
t1.n0_period_no = '200712310000' and
t1.bl_voided =0
group by t1.sz_item_ref_no, t1.sz_description
order by t1.sz_item_ref_no

RESULTSET
[sz_item_ref_n0], [sz_description], [n0_column_name]
1, double espresso, 55
2, espresso fredo , 34
3, coca cola, 265

This are the items sold

select t1.sz_item_ref_no, t1.sz_description, sum(t1.n0_quantity)
from rdb_log_item as t1
where
t1.n0_period_no = '200712310000' and
t1.bl_voided =1
group by t1.sz_item_ref_no, t1.sz_description
order by t1.sz_item_ref_no

I get the result:

RESULTSET
sz_item_ref_n0 , sz_description , n0_column_name
1 , double espresso , 9
2 , espresso fredo , 4
3 , coca cola , 3

This are the item's VOIDED (canceled)

I want to display the Items sold minus the items canceled (which means join the two queries???)

RESULTSET
sz_item_ref_n0, sz_description, n0_column_name
1 , double espresso , 45
2 , espresso fredo , 30
3 , coca cola , 262

i am trying several query types
i think the one more close to result i want should be like this, but when i run it i get an error:

select t1.sz_item_ref_no, t1.sz_description, sum(t1.n0_quantity)
from rdb_log_item as t1
where
t1.n0_period_no = '200712310000' and
t1.bl_voided =1 and
t1.bl_voided not in
(select * from rdb_log_item as t2
where
t2.bl_voided =0 and
t1.n0_xact_no= t2.n0_xact_no)
group by t1.sz_item_ref_no, t1.sz_description
order by t1.sz_item_ref_no

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Any help for the query is appreciated, i am on a hurry...

Thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 08:10:41

As a continuation from here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95539

SELECT		t1.sz_item_ref_no,
		t1.sz_description,
		SUM(CASE WHEN t1.bl_voided = 0 THEN t1.n0_quantity ELSE 0 END),
		SUM(CASE WHEN t1.bl_voided = 1 THEN t1.n0_quantity ELSE 0 END)
FROM		RDB_LOG_ITEM AS t1
WHERE		t1.n0_period_no = '200712310000'
		AND 
GROUP BY	t1.sz_item_ref_no,
		t1.sz_description
ORDER BY	t1.sz_item_ref_no

E 12°55'05.25"
N 56°04'39.16"

imrul
Starting Member

36 Posts

Posted - 2008-01-16 : 04:30:47

select t1.sz_item_ref_no, t1.sz_description, sum(t1.n0_quantity-IsNull(t2.n0_quantity,0))
from(select x.sz_item_ref_no, x.sz_description, sum(x.n0_quantity) As n0_quantity
from rdb_log_item As x
where
x.n0_period_no = '200712310000' and
x.bl_voided =0
group by s.sz_item_ref_no, x.sz_description
) as t1
Left Outer Join
(select y.sz_item_ref_no, y.sz_description, sum(y.n0_quantity) as n0_quantity
from rdb_log_item as y
where
y.n0_period_no = '200712310000' and
y.bl_voided =1
group by y.sz_item_ref_no, y.sz_description
order by y.sz_item_ref_no) As t2
Group By
t1.sz_item_ref_no, t1.sz_description,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources