Tricky SQL column check within the table

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

General SQL Server Forums

New to SQL Server Programming

Tricky SQL column check within the table

Author

Topic

sushant17284
Starting Member

12 Posts

Posted - 2010-08-25 : 03:35:57

Hi All,
I am stuck on this issue since last 2 days .I hope anyone of you could help me resolve this problem or atleast show me the right direction.

Here's the problem.

I have a table named Product which looks like the following:
ID |PRODUCT_CODE|TITLE | IMAGE |REQUIRED_PRODUCT1 | REQUIRED_PRODUCT2

So as shown above the table has these 6 fields.Here ID is the primary key of this table.
Now I have data in this table like the following:
ID |PRODUCT_CODE|TITLE | IMAGE |REQUIRED_PRODUCT1 | REQUIRED_PRODUCT2
1 | AB_008 | s-video cable | svideo.jpg| | AB_010 | AB_011
2 | AB_009 | composite video | composite.jpg | AB_020 | AB_023
3 | AB_010 | RCA connector | rca.jpg | AB_008 | AB_011
4 | AB_011 | comp to svideo| c-svid.jpg | AB_008 | AB_025

here is the problem I am stuck with.I need to fetch the rows from
the product table where RELATED_PRODUCT1 or RELATED_PRODUCT2 column values does not exist in the PRODUCT TABLE as PRODUCT_CODE.
So to be more explicit,I mean to say that I need to check for each row whichever value is available in RELATED_PRODUCT1 or RELATED_PRODUCT2 and see if that value is available as a product code in the entire table.

So for e.g. for ID 2 , the RELATED_PRODUCT1 is AB_020 and RELATED_PRODUCT2 is AB_023. As we can see there is no row in the table which has PRODUCT_CODE as either AB_020 and AB_023.So this row should be displayed in the resultset.There is also a case of ID as 4 where RELATED_PRODUCT1 is available in the table as product code but
RELATED_PRODUCT2 is not available.In this case also the resultset should contain this row.

SO the final resultset should look like :
2 | AB_009 | composite video | composite.jpg | AB_020 | AB_023
4 | AB_011 | comp to svideo| c-svid.jpg | AB_008 | AB_025

I believe this should not be complex if we can use stored procedures.
But we are not allowed to use Stored procedure and have been restricted to achieve this resultset only using an SQL query.
I need help.........I really hope someone can show me a ray of hope.
Thanks to all of you for having this wonderful platform to post your problems and solutions.I really appreciate any help on this issue.
Thanks,
Sushant

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-25 : 04:17:21

[code]select * from product a
where not exists ( select 1 from product b where b.PRODUCT_CODE=a.REQUIRED_PRODUCT1)
or not exists ( select 1 from product c where c.PRODUCT_CODE=a.REQUIRED_PRODUCT2)[/code]

sushant17284
Starting Member

12 Posts

Posted - 2010-08-25 : 04:44:32

Wonderful post mate.It worked like a charm.Thanks for the stunning post Could you please do me a favour and explain the query so that I can understand how I am able to fetch the desired resultset

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:36:00

not exists checks for boolean result. inside condition checks whether you've a record that satisfies given condition in product table. if it doesnt exist in at least one of queries it returns false. so not exists returns true and hence record will be included in result.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-25 : 12:29:55

Since the data is not in a form I can query against this is untested, but I think you can also use joins to get the results you want:

select 
	a.* 
from 
	product as a
left outer 
	product as b 
	on  b.PRODUCT_CODE = a.REQUIRED_PRODUCT1
left outer join 
	product as c 
	on c.PRODUCT_CODE = a.REQUIRED_PRODUCT
where
	b.PRODUCT_CODE IS NULL
	OR c.PRODUCT_CODE IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources