Hi,

Here’s the problem: you;re saying

...r.RC_code not IN
(Select v.RC_code From iv_detail v 
,iv_list i 
Where (v.rc_CODE = r.rc_code) and ...

The sub-query is correlated to the main query: that is, it uses a value from the main query and (potentially) runs the sub-query a separate time for each row in the main query. The correlating condition in the sub-query is “v.rc_CODE = r.rc_code”, so when, in the sub-query, you say “Select v.RC_code …” that’s equivalent to saying “Select r.RC_code …”, and your main query has the condition

r.RC_code not IN
(Select r.RC_code ...

I think what you want is an UNcorellated sub-query, that is, a sub-query that will run once (no matter how many rows are in the main query) to produce a list of bad rc_codes, like this:

SELECT p.PO_code,p.Po_date,
p.Supp_code + ' : '+ s.Sname as Supp_Nme,p.po_total,r.RC_code,r.Amount
FROM PO_list p inner join RC_list r on r.PO_code = p.PO_code
inner join Supplier s on s.supp_code = p.supp_code
WHERE (p.Supp_code in ('102200','102083')) and (substring(p.PO_CODE ,5,1) = 'R' ) and 
(p.cancel = 'N' and r.cancel = 'N' ) and (r.RC_code not IN
(Select v.RC_code From iv_detail v 
,iv_list i 
Where i.iv_code = v.iv_code and i.Cancel = 'N' )...

that is, get rid of the condition “(v.rc_CODE = r.rc_code)” in the sub-query.

The first person to answer this question has a good point. It’s always better if you can include a specific example, the simpler the better.

Thank you for solving my program

This right the problem is NOT IN command.
Please see the detail below :
File Structure :
po_list : PO_code(key) , desc, supp_code, cancel ,…
rc_list : RC_code(key) , po_code , desc, cancel , …
iv_detail : iv_code, rc_code
iv_list : IV_code(key) , desc. cancel ,…
supplier : Supp_code(key) , snme, addr ,…

Data sample :
PO_list : po507486 , po_desc , 102200 , n
: po508270 , po_desc , 102083 , n
RC_list : rc50c100 , po507486 , rc_desc , n
: rc508018 , po508270 , rc_desc , n
IV_detail : iv50c059 , rc50c100
: iv508014, rc508018
IV_listt : iv50c059 , iv_desc, n
: iv508014, iv_desc , Y

The data of PO508270 should be showed in this query.

Thaks again for any help,
ndID

Hi,

Disregard the suggestion I made yesterday; it won’t change the results of your query.

The row with PO_CODE ‘PO508270′ isn’t being selected because the WHERE-clause says

(substring(p.PO_CODE ,5,1) = 'R' )

The fifth character of ‘PO508270′ is ’8′, not ‘R’.

Also, there may not be a match in the supplier table.

Hi,

Sorry . I made a sample data incorrect .The actual data are :

PO_list : H01-R-po507486 ,po_desc , 102200 , n
: H01-R-po508270 , po_desc , 102083 , n
RC_list : H01-R-rc50c100 , H01-R-po507486 , rc_desc , n
: H01-R-rc508018 , H01-R-po508270 , rc_desc , n
IV_detail : H01-R-iv50c059 , H01-R-rc50c100
: H01-R-iv508014, H01-R-rc508018
IV_listt : H01-R-iv50c059 , iv_desc, n
: H01-R-iv508014, iv_desc , Y

Table : Supplier : Supp_code , sname,addr …
and query is
SELECT p.PO_code,p.Po_date,
p.Supp_code + ‘ : ‘+ s.Sname as Supp_Nme,p.po_total,r.RC_code,r.Amount
FROM PO_list p inner join RC_list r on r.PO_code = p.PO_code
inner join Supplier s on s.supp_code = p.supp_code
WHERE (p.Supp_code in (’102200′,’102083′)) and (substring(p.PO_CODE ,5,1) = ‘R’ ) and
(p.cancel = ‘N’ and r.cancel = ‘N’ ) and (r.RC_code not IN
(Select v.RC_code From iv_detail v ,iv_list i
Where (v.rc_CODE = r.rc_code) and i.iv_code = v.iv_code and i.Cancel = ‘N’ )

The H01-R-PO508270 still did not showed

Thanks you for your feedback.
ndID

Hi,
Then there’s probably no match in the supplier table. Without seeing what’s in your supplier table, I can’t be more specific.

Hi,

I don’t think so. Because when i changed The condition as : (Not IN to be IN command )

SELECT p.PO_code,p.Po_date,
p.Supp_code + ‘ : ‘+ s.Sname as Supp_Nme,p.po_total,r.RC_code,r.Amount
FROM PO_list p inner join RC_list r on r.PO_code = p.PO_code
inner join Supplier s on s.supp_code = p.supp_code
WHERE (p.Supp_code in (’102200′,’102083′)) and (substring(p.PO_CODE ,5,1) = ‘R’ ) and
(p.cancel = ‘N’ and r.cancel = ‘N’ ) and
(r.RC_code IN (Select v.RC_code
From iv_detail v ,iv_list i
Where (v.rc_CODE = r.rc_code) and
i.iv_code = v.iv_code and i.Cancel = ‘N’ ) )

The H01-R-po507486 ,which have i.cancel = ‘N’ is showed in this query.

Thank you for your help,
ndID

Hi,

I’m not sure if you still have a problem, and, if so, what it is.
When I run the script below in SQL Server 2005 Express, the output of the “Original Query” is one row:

PO_code         RC_code
H01-R-po508270  H01-R-rc508018

If you’re not getting the same results, what are you doing differently? Describe the results you want to get and why. If the data below is not a good test case, then modify it and post the improved version.

This site seems to replace double-dashes with another character that looks like one dash. The script below contains no subtraction: anything that looks like a minus sign is actually the beginning of a comment.

Here’s my script:

--	==========  iv_detail  ==========

DROP TABLE	iv_detail;

CREATE TABLE	iv_detail
(	iv_code	CHAR (20)
,	rc_code	CHAR (20)
);

INSERT INTO	iv_detail	(iv_code,		rc_code)
	VALUES			('H01-R-iv50c059',	'H01-R-rc50c100');
INSERT INTO	iv_detail	(iv_code,		rc_code)
	VALUES			('H01-R-iv508014',	'H01-R-rc508018');

SELECT	*
FROM	iv_detail
ORDER BY	iv_code;


--	==========  iv_list  ==========

DROP TABLE	iv_list;

CREATE TABLE	iv_list
(	iV_code	CHAR (20)
,	dsc	CHAR (10)
,	cancel	CHAR (10)
);

INSERT INTO iv_list	(iV_code,		dsc,		cancel)
	VALUES		('H01-R-iv50c059',	'iv_desc',	'n');
INSERT INTO iv_list	(iV_code,		dsc,		cancel)
	VALUES		('H01-R-iv508014',	'iv_desc',	'Y');

SELECT	*
FROM	iv_list
ORDER BY	iv_code;


--	==========  po_list  ==========

DROP TABLE	po_list;

CREATE TABLE	po_list
(	PO_code		CHAR (20)
,	dsc		CHAR (10)
,	supp_code	INT
,	cancel		CHAR (10)
);

INSERT INTO po_list	(PO_code,		dsc,		supp_code,	cancel)
	VALUES 		('H01-R-po507486',	'po_desc',	102200,		'n');
INSERT INTO po_list	(PO_code,		dsc,		supp_code,	cancel)
	VALUES 		('H01-R-po508270',	'po_desc',	102083,		'n');

SELECT	*
FROM	po_list
ORDER BY	po_code;


--	==========  rc_list  ==========

DROP TABLE	rc_list;

CREATE TABLE	rc_list
(	RC_code	CHAR (20)
,	po_code	CHAR (20)
,	dsc	CHAR (10)
,	cancel	CHAR (10)
);


INSERT INTO	rc_list	(RC_code,		po_code,		dsc,		cancel)
	VALUES		('H01-R-rc50c100',	'H01-R-po507486',	'rc_desc',	'n');
INSERT INTO	rc_list	(RC_code,		po_code,		dsc,		cancel)
	VALUES		('H01-R-rc508018',	'H01-R-po508270',	'rc_desc',	'n');

SELECT	*
FROM	rc_list
ORDER BY	rc_code;


--	==========  supplier  ==========

DROP TABLE	supplier;

CREATE TABLE	supplier
(	Supp_code	CHAR (10)
,	snme		CHAR (10)
,	addr		CHAR (10)
);

SELECT	*
FROM	supplier
ORDER BY	supp_code;



--	==========  Original Query  ==========

SELECT	p.PO_code
-- ,	p.Po_date
-- ,	p.Supp_code + ' : '+ s.Sname	as Supp_Nme
-- ,	p.po_total
,	r.RC_code
-- ,	r.Amount
FROM		PO_list		p
inner join	RC_list		r on r.PO_code		= p.PO_code
WHERE	(p.Supp_code			in ('102200','102083')) 
and	(substring (p.PO_CODE, 5, 1)	= 'R')
-- inner join	 Supplier 	s on s.supp_code 	= p.supp_code
and	(	p.cancel		= 'N' 
	AND	r.cancel		= 'N'
	)
and	(	r.RC_code		not IN
		(
		Select	v.RC_code
		From	iv_detail	v 
		,	iv_list		i 
		Where	(v.rc_CODE	= r.rc_code)
		and	i.iv_code	= v.iv_code
		and	i.Cancel	= 'N'
		)
	)
;

Hi,
Thank again for your advise. I will try your suggestion and let you know the result.

I wonder if i could replace Not In with Not Exists Command in this query. what is the different using.

Your help is greatly appreciated.
ndID

Hi,

It works fine without any problem in your script and I found that some wrong in my data ,not script.

Thanks you again for your help and advice.
ndID