Do I use a union here? What's going on? - 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)

Do I use a union here? What's going on?

Author

Topic

sjamesje
Starting Member

7 Posts

Posted - 2004-04-01 : 11:19:53

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:27:25

I'd like to see some DDL/DML (CREATE TABLE/INSERT with sample data) to test these and see what is going on, without those it is hard to guess with the information provided.

The only thing I see is the FULL JOIN which is an OUTER JOIN on both tables involved in the join, but I'm not sure if that is the problem.

sjamesje
Starting Member

7 Posts

Posted - 2004-04-01 : 11:39:26

Unfortunately, the table structure is proprietary, and I cannot release that info...I know, it's silly. I have tried it with all different types of joins, and that doesn't seem to help...

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-01 : 11:59:49

I don't see how the table structure can be so sensitive that a mock CREATE with only the fields and relationships required to solve your issue can't be given. I would say the fields necessary are only the ones in the queries you posted.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-01 : 12:26:58

If you want some help, put in a little effort and give us some samples so we don't have to spend 20 minutes creating tables and entering sample data to help you out.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources