Combine 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

SQL Server Development (2000)

Combine 2 Queries

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-07 : 16:45:01

How can I combine these 2 queries? I need the results of IMINVLOC_SQL.prod_cat from the first query to be used in the second query for #qCats.prod_cat# (variable)

SELECT monthlyspecials.page_number, monthlyspecials.ggroup, monthlyspecials.location, monthlyspecials.square_inches, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat
FROM monthlyspecials
LEFT OUTER JOIN
IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE IMINVLOC_SQL.loc = 'TP'
ORDER BY monthlyspecials.page_number

SELECT SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
AND OEHDRHST_SQL.Ord_dt >=20040801
AND OEHDRHST_SQL.Ord_dt <=20040831
AND OELINHST_SQL.prod_cat = '#qCats.prod_cat#'

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-07 : 17:11:17

[code]SELECT
SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
AND OEHDRHST_SQL.Ord_dt >=20040801
AND OEHDRHST_SQL.Ord_dt <=20040831
AND EXISTS (
SELECT 1
FROM monthlyspecials LEFT OUTER JOIN IMINVLOC_SQL ON
monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE IMINVLOC_SQL.loc = 'TP' AND OELINHST_SQL.prod_cat = IMINVLOC_SQL.prod_cat)[/code]

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-07 : 17:49:25

I'd like for monthlyspecials.page_number, monthlyspecials.ggroup, monthlyspecials.location, monthlyspecials.square_inches, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat

to be selected too.

GunZ
Starting Member

29 Posts

Posted - 2004-10-07 : 20:13:45

Provided the result sets of the two queries are similar in structure (ie, returns the same columns) you can:


SELECT name, age, sex 
   FROM company A 
   WHERE departmentID = 2

UNION

SELECT name, age, sex
   FROM company B
   WHERE departmentID = 5

Australia.NSW.Sydney.GunZ

GunZ
Starting Member

29 Posts

Posted - 2004-10-07 : 20:32:02

here's another way of illustrating what I said...



SELECT column01, column02, column03
   FROM table01 A
   WHERE A.column04 = <something>

UNION

SELECT column05, column06, column07
   FROM table02 B
   WHERE B.column09 = <something_else>

the type of column01 & column05, column02 & column06 and, column03 & column07, must match or can be casted to one another. table01 and table02 may not necessarily be on the same database.

Hope it helps.

Australia.NSW.Sydney.GunZ

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-08 : 10:02:18

quote:
Originally posted by jrockfl

I'd like for monthlyspecials.page_number, monthlyspecials.ggroup, monthlyspecials.location, monthlyspecials.square_inches, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat

to be selected too.

How about

SELECT 
    SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
    AND OEHDRHST_SQL.Ord_dt >=20040801
    AND OEHDRHST_SQL.Ord_dt <=20040831
    inner join (
        SELECT monthlyspecials.page_number, monthlyspecials.ggroup, monthlyspecials.location, 
            monthlyspecials.square_inches, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat 
        FROM monthlyspecials LEFT OUTER JOIN IMINVLOC_SQL ON 
            monthlyspecials.item_number = IMINVLOC_SQL.Item_No
        WHERE IMINVLOC_SQL.loc = 'TP') xyz on
    OELINHST_SQL.prod_cat = xyz.prod_cat

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources