Problem Query - 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 2005 Forums

Transact-SQL (2005)

Problem Query

Author

Topic

thumor
Starting Member

4 Posts

Posted - 2008-03-14 : 15:11:58

I have 2 tables as follows
create table ord1 (customer Char (10), division char(3),
ovnd_key Char(10))
insert into ord1 values ('RICHS', 'M', '12')
insert into ord1 values ('RICHS', 'M', '13')
insert into ord1 values ('RICHS', 'M', ' ')

create table PRCR (customer Char (10), division char(3),
ovnd_key Char(10), Ack_by char(1), sku_upc char(1))
insert into PRCR values ('RICHS', 'M', '12', 'O','N')
insert into PRCR values ('RICHS', 'M', ' ', 'P','N')
insert into PRCR values ('RICHS', 'M', '13', 'P','N')
insert into PRCR values ('RICHS', 'M', '14', 'O','N')

Now, I want to select rows from ord1 such that PRCR these conditions
satisfied:
1. customer and division should match with PRCR.
2. AND PRCR.ack_by = "P" AND PRCR.sku_upc <> "N"

3. If ord1.Ovnd_key match PRCR.Ovnd_key then Select that row from ord1
4. Otherwise find one in PRCR that has Ovnd_key = ''

I tried this
Select distinct h.ovnd_key,h.customer, h.division
from ord1 h
LEFT JOIN zzeoprcr c
ON h.customer = c.customer
And h.division=c.division
And h.oVnd_key = c.oVnd_key
LEFT JOIN zzeoprcr c2
ON h.customer=c2.customer
And h.division=c2.division
And c2.oVnd_key = ' '
where ( (c2.ack_by='P' And Not c2.sku_upc='N')
OR (c.ack_by='P' And Not c.sku_upc='N'))

But do not get correct result.
This selects the row with ovnd_key="12", but should not
because ack_by="O" in PRCR.

Any ideas.
Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-14 : 15:25:58

I don't know if this right as you didn't include a desired result set

Select distinct h.ovnd_key,h.customer, h.division
from ord1 h
LEFT JOIN prcr c
ON h.customer = c.customer
And h.division=c.division

where c.ack_by='P' And (h.oVnd_key = c.oVnd_key OR c.oVnd_key= ' ')

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-14 : 15:43:15

According to your sample data no rows would be returned as none of the PRCR values pass: PRCR.ack_by = "P" AND PRCR.sku_upc <> "N

Am I reading that correctly?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-14 : 15:50:58

Maybe you ment sku_upc = 'N'??
EDIT: I'll leave my code here for what it's worth, but I suspect that Jim's code is faster (and smaller). Additionally, you can change Jim's code to use an INNER JOIN, since it's doing an inner join on the data.

Maybe this will help?

declare @ord1 table (customer Char (10), division char(3), ovnd_key Char(10))
insert into @ord1 values ('RICHS', 'M', '12')
insert into @ord1 values ('RICHS', 'M', '13')
insert into @ord1 values ('RICHS', 'M', '')

DECLARE @PRCR table (customer Char (10), division char(3), ovnd_key Char(10), Ack_by char(1), sku_upc char(1))
insert into @PRCR values ('RICHS', 'M', '12', 'O','N')
insert into @PRCR values ('RICHS', 'M', '', 'P','N')
insert into @PRCR values ('RICHS', 'M', '13', 'P','N')
insert into @PRCR values ('RICHS', 'M', '14', 'O','N')


SELECT *
FROM
(
	SELECT 
		O.*
	FROM 
		@ord1 AS O
	INNER JOIN
		@prcr AS P
		ON O.customer = P.customer
		AND O.division = P.division
		AND O.ovnd_key = P.ovnd_key
	WHERE
		P.ack_by = 'P' AND P.sku_upc = 'N'

	UNION ALL

	SELECT 
		O.*
	FROM 
		@ord1 AS O
	INNER JOIN
		@prcr AS P
		ON O.customer = P.customer
		AND O.division = P.division
		AND O.ovnd_key <> P.ovnd_key
		AND P.ovnd_key = ''
	WHERE
		P.ack_by = 'P' AND P.sku_upc = 'N'
) Temp

thumor
Starting Member

4 Posts

Posted - 2008-03-15 : 14:15:07

Let me first correct the information I gave out.
1. PRCR (sku_upc = 'U') in all inserts for the sample.
2. In my query replace zzeoprcr with PRCR.

As for result set, the row with Ovnd_key='12 in ord1 should not be selected, so only two rows would be selected.

In mine, the 'second' join pulls up the '12' row from ord1, even if it
does not satisfy the ack_by="P" clause.

Hope this helps.

Thanks all.

thumor
Starting Member

4 Posts

Posted - 2008-03-17 : 09:30:10

Help please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-17 : 12:49:11

Can you provide your expected o/p from sample data above?

thumor
Starting Member

4 Posts

Posted - 2008-03-17 : 23:10:35

Result would select these 2 rows from ord1

'RICHS', 'M', ' '
'RICHS', 'M', '13'

Thanks.