Union? What should I use here? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Union? What should I use here?

Author

Topic

sjamesje
Starting Member

7 Posts

Posted - 2004-04-01 : 11:24:00

When I use this query,
---------------------------------------------------------------------

select a.store_id,
dbr_date,
a.upc_code,
package,
product_desc
from srpack a full join mdproduct b on
(a.store_id = b.store_id and a.upc_code = b.upc_code)
where a.store_id = '7700' and dbr_date = '1/10/2004'
---------------------------------------------------------------------
I get this result:
---------------------------------------------------------------------
store_id dbr_date upc_code package product_desc
-------- --------------------------- ------------- ----------- --------------------
7700 2004-01-10 00:00:00.000 01230000007 3 Camel Lts Bx
7700 2004-01-10 00:00:00.000 01230010713 10 NULL
7700 2004-01-10 00:00:00.000 01820000016 6 Bud Can
7700 2004-01-10 00:00:00.000 01820000833 6 NULL
7700 2004-01-10 00:00:00.000 02820000276 3 Marlboro 100 Mnth Bx
7700 2004-01-10 00:00:00.000 02820000276 10 Marlboro 100 Mnth Bx
7700 2004-01-10 00:00:00.000 02820010690 10 NULL
7700 2004-01-10 00:00:00.000 02840003337 2 Doritos Cra
7700 2004-01-10 00:00:00.000 04900001252 2 Coke Classic 12pk
7700 2004-01-10 00:00:00.000 04900002656 2 Dasani 1 ltr
7700 2004-01-10 00:00:00.000 07114200126 6 NULL
7700 2004-01-10 00:00:00.000 07199030005 6 NULL
7700 2004-01-10 00:00:00.000 07289000011 6 NULL
7700 2004-01-10 00:00:00.000 63598510062 6 NULL

(14 row(s) affected)
---------------------------------------------------------------------
And when I use THIS query,
---------------------------------------------------------------------
select a.store_id,
a.dbr_date,
a.upc_code,
package,
description,
c.product_desc,
c.category_id
from srpack a full join mdupcxref b on
(a.store_id = b.store_id and a.upc_code = b.upc_code)
full join mdproduct c on
(b.store_id = c.store_id and b.product_num = c.product_num)
where a.store_id = '7700' and dbr_date = '1/10/2004'
---------------------------------------------------------------------
I get THIS result:
---------------------------------------------------------------------
store_id dbr_date upc_code package description product_desc category_id
-------- --------------------------- ------------- ----------- -------------------- -------------------- -----------
7700 2004-01-10 00:00:00.000 01230000007 3 NULL NULL NULL
7700 2004-01-10 00:00:00.000 01230010713 10 Camel Lts Bx Camel Lts Bx 02
7700 2004-01-10 00:00:00.000 01820000016 6 NULL NULL NULL
7700 2004-01-10 00:00:00.000 01820000833 6 Bud Lt Ln Bud Lt Ln 16
7700 2004-01-10 00:00:00.000 02820000276 3 NULL NULL NULL
7700 2004-01-10 00:00:00.000 02820000276 10 NULL NULL NULL
7700 2004-01-10 00:00:00.000 02820010690 10 Marlboro Lt Box Marlboro Lt Box 02
7700 2004-01-10 00:00:00.000 02840003337 2 NULL NULL NULL
7700 2004-01-10 00:00:00.000 04900001252 2 NULL NULL NULL
7700 2004-01-10 00:00:00.000 04900002656 2 NULL NULL NULL
7700 2004-01-10 00:00:00.000 07114200126 6 Arrowhead .5ltr Arrowhead .5ltr 03
7700 2004-01-10 00:00:00.000 07199030005 6 Coors Ln Nr Coors Light Ln Nr 16
7700 2004-01-10 00:00:00.000 07289000011 6 Heiniken 6pk Heineken Nr 16
7700 2004-01-10 00:00:00.000 63598510062 6 Mike's Hard Lemonade Mike's Hard Lemonade 16

(14 row(s) affected)
---------------------------------------------------------------------

It's the same 14 rows of information, but the UPC's have opposite nulls. How can I combine these two queries to eliminate the nulls?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-01 : 11:30:05

This is the proper forum for this. I had a question for you from your post in the other forum.

Click here but respond back on this forum.

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33895[/url]

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-04-01 : 16:48:24

Without DDL this is all you get, not tested, but if my SQL hasn't failed me it will work. A quick fix to a quick question.

--Insert your second query into a temporary table.

SELECT INTO #TEMPTABLE

FROM

(SELECT A.STORE_ID,
A.DBR_DATE,
A.UPC_CODE,
PACKAGE,
DESCRIPTION,
C.PRODUCT_DESC,
C.CATEGORY_ID
FROM SRPACK A FULL JOIN MDUPCXREF B ON
(A.STORE_ID = B.STORE_ID AND A.UPC_CODE = B.UPC_CODE)
FULL JOIN MDPRODUCT C ON
(B.STORE_ID = C.STORE_ID AND B.PRODUCT_NUM = C.PRODUCT_NUM)
WHERE A.STORE_ID = '7700' AND DBR_DATE = '1/10/2004')
) AS DERIVEDTABLE

------THEN UPDATE THAT TABLE'S PRODUCT_DESC WITH THE FIRST QUERY

UPDATE #TEMPTABLE

SET PRODUCT_DESC = DT.PRODUCT_DESC FROM

(SELECT A.STORE_ID,
DBR_DATE,
A.UPC_CODE,
PACKAGE,
PRODUCT_DESC
FROM SRPACK A FULL JOIN MDPRODUCT B ON
(A.STORE_ID = B.STORE_ID AND A.UPC_CODE = B.UPC_CODE)
WHERE A.STORE_ID = '7700' AND DBR_DATE = '1/10/2004'

)AS DT

WHERE #TEMPTABLE.STORE_ID = DT.STORE_ID AND #TEMPTABLE.DBR_DATE = DT.DBR_DATE AND #TEMPTABLE.UPC_CODE = DT.UPC_CODE
AND #TEMPTABLE.PACKAGE = DT.PACKAGE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources