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.
| Author |
Topic |
|
onerrorgoto
Starting Member
3 Posts |
 Posted - 2009-12-09 : 06:08:56
|
| HelloI have a table containing article description, make and models.I want to find all descriptions that do not contain Make OR modeltable 1ID | description | make | model1 | washingmashine ekc6858 elux | elux | ekc68582 | vacumcleaner trf58 whirlpool | whirlpool | NULL3 | micro jgh8 Nobrand | NULL | jgh84 | TV | Philips | nc48005 | DVD player | Maxtor | NULL6 | Keyboard Logitech MX5000 | Logitech | mx5000what I want to get is the two rows ID 2-5. they do not meet the requirement make and model must exist in column description. the order is not that important at this point./AndersTrying is the first step toward failure. H Simpson |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-12-09 : 06:15:49
|
| [code]select ID from table1 where make is null or model is null[/code]http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-12-09 : 06:20:35
|
| [code]DECLARE @t TABLE (ID int, description VARCHAR(128), make VARCHAR(32), model VARCHAR(32))INSERT INTO @tSELECT 1 ,'washingmashine ekc6858 elux','elux','ekc6858' UNION ALL SELECT 2 ,'vacumcleaner trf58 whirlpool','whirlpool',NULL UNION ALL SELECT 3 ,'micro jgh8 Nobrand',NULL ,'jgh8' UNION ALL SELECT 4 ,'TV ','Philips','nc4800' UNION ALL SELECT 5 ,'DVD player','Maxtor ',NULL UNION ALL SELECT 6 ,'Keyboard Logitech MX5000','Logitech','mx5000'SELECT * FROM @t WHERE (make IS NULL OR description LIKE '%'+make +'%' ) AND (model IS NULL OR description LIKE '%'+model +'%' )SELECT * FROM @t WHERE description LIKE '%'+make +'%' AND description LIKE '%'+model +'%' [/code] |
|
|
|
onerrorgoto
Starting Member
3 Posts |
Posted - 2009-12-09 : 07:46:14
|
| Thank you bklrafter some modifiyng I got the result I wanted.SELECT * FROM @t WHERE (description not LIKE '%'+make +'%' or description not LIKE '%'+model +'%')I just want the articles that do not match the criteria:make and model must exist in description.DonAtWork:Your query dont give me row 4/Anders |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2009-12-09 : 07:52:35
|
Note that the following queries are equivalent.SELECT * FROM @t WHERE (description not LIKE '%'+make +'%' or description not LIKE '%'+model +'%')SELECT * FROM @t WHERE not (description LIKE '%'+make +'%' and description LIKE '%'+model +'%') I find the 2nd one easier to follow at a glance, but that's a personal preference.Ryan Randall - Yak of all tradesSolutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-12-09 : 08:12:33
|
quote:
Originally posted by onerrorgoto
Thank you bklrafter some modifiyng I got the result I wanted.SELECT * FROM @t WHERE (description not LIKE '%'+make +'%' or description not LIKE '%'+model +'%')I just want the articles that do not match the criteria:make and model must exist in description.DonAtWork:Your query dont give me row 4/Anders
welcome |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-12-09 : 14:58:18
|
| Sorry, your requirements were not clear to me. I read "make and model must exist" and missed the "in the description".http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
|
|
compare values in column with other column values