MS SQL RSS MS SQL RSS

multiselecting UNLIKE values

Please support our MS SQL advertiser: Intel Parallel Studio Home
Reply

•
•
Join Date: May 2008
Posts: 31
Reputation: harcaype is an unknown quantity at this point 
Solved Threads: 0
harcaype harcaype is offline Offline
Light Poster

multiselecting UNLIKE values

 
0
  #1
Jul 11th, 2008
hi. my thread title seems strange but its really about comparing unlike values as an output.

I want all values that are not LIKE the other values. To make things clearer. Here's my query:

SELECT Records.[Officer ID],
Records.[Plate Number],
Records.[Violation Commited],
Violations.[Violation Code],
Records.[Street Name],
Records.[City of Apprehension],
Records.[Vehicle Category],
Records.[Vehicle Type],
Records.[Vehicle Brand],
Records.[Date/Time Apprehended]
FROM Records,Violations,DriverInfo
WHERE Records.[Plate Number] NOT LIKE DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations

What I want to do in here is select all columns that has the column name value,which is not equivalent to the other column from another table. So its like saying, "From all eggs that came from Hens , select those who are not eggs of Hen # 1."

When I tried the NOT LIKE query. it still showed the ones that are LIKE and worse, it redundantly displayed its data. Any suggestions?
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 72
Reputation: cmhampton is an unknown quantity at this point 
Solved Threads: 10
cmhampton's Avatar
cmhampton cmhampton is offline Offline
Junior Poster in Training

Re: multiselecting UNLIKE values

 
0
  #2
Jul 11th, 2008
Try using != (not equal) instead of NOT LIKE. LIKE is a little more 'fuzzy' than an explicit equal, which is why you did not get the data you were looking for.

Also, one other thing that might help make your queries easier to read (and type) is using aliases for your tables:

SQL Syntax (Toggle Plain Text) 
  1. SELECT 
  2. 		r.[Officer ID],
  3. 		r.[Plate Number],
  4. 		r.[Violation Commited],
  5. 		v.[Violation Code],
  6. 		r.[Street Name],
  7. 		r.[City of Apprehension],
  8. 		r.[Vehicle Category],
  9. 		r.[Vehicle Type],
  10. 		r.[Vehicle Brand],
  11. 		r.[DATE/TIME Apprehended]
  12. FROM 
  13. 		Records r,
  14. 		Violations v,
  15. 		DriverInfo di
  16. WHERE 
  17. 		r.[Plate Number] != di.[Plate Number]
  18. 		AND r.[Violation Commited] = v.Violations

I hope this helps.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2008
Posts: 31
Reputation: harcaype is an unknown quantity at this point 
Solved Threads: 0
harcaype harcaype is offline Offline
Light Poster

Re: multiselecting UNLIKE values

 
0
  #3
Jul 11th, 2008
Thanks! But I have also used != and the result is still the same
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 72
Reputation: cmhampton is an unknown quantity at this point 
Solved Threads: 10
cmhampton's Avatar
cmhampton cmhampton is offline Offline
Junior Poster in Training

Re: multiselecting UNLIKE values

 
0
  #4
Jul 11th, 2008
•
•
•
•
Originally Posted by harcaype View Post
Thanks! But I have also used != and the result is still the same
Can you make up some sample data for me? I'll recreate this structure on my server and test it.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 296
Reputation: tesuji is on a distinguished road 
Solved Threads: 42
tesuji tesuji is offline Offline
Posting Whiz in Training

Re: multiselecting UNLIKE values

 
0
  #5
Jul 11th, 2008
hello,

You want to select some data from three tables. This will be done by two inner joins. If you don't tell which columns of the involved tables should be joined together the result will be cartesian product (cross join), that is all columns of 1st table will be joined with all columns of 2nd table and all columns of that result will be joined with all columns of 3rd table what usually is an undesirable result. Similar results will be given by LIKE and NOT LIKE comparison operators what might be theoretically correct operators for joining constraints but rather unlikely.

If your select statement contains columns from various tables you must:

1. Figure out the correct joining constraints for WHERE or the newer ON clause

2. Supply further predicates for the WHERE clause to reduce result set to the
rows you really expect.

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT Records.[Officer ID],
  2. Records.[Plate Number],
  3. Records.[Violation Commited],
  4. Violations.[Violation Code],
  5. Records.[Street Name],
  6. Records.[City of Apprehension],
  7. Records.[Vehicle Category],
  8. Records.[Vehicle Type],
  9. Records.[Vehicle Brand],
  10. Records.[Date/Time Apprehended]
  11. FROM Records,Violations,DriverInfo
  12. WHERE 
  13. -- two constraints for inner joins
  14. Records.[Plate Number] = DriverInfo.[Plate Number] AND
  15. Records.[Violation Commited] = Violations.Violations
  16. AND
  17. -- your specific predicates following here
Instead of doing joins via where clause there is a more modern joining syntax:
MS SQL Syntax (Toggle Plain Text) 
  1. SELECT Records.[Officer ID],
  2. Records.[Plate Number],
  3. Records.[Violation Commited],
  4. Violations.[Violation Code],
  5. Records.[Street Name],
  6. Records.[City of Apprehension],
  7. Records.[Vehicle Category],
  8. Records.[Vehicle Type],
  9. Records.[Vehicle Brand],
  10. Records.[Date/Time Apprehended]
  11. FROM 
  12. Records INNER JOIN DriverInfo ON Records.[Plate Number] = DriverInfo.[Plate Number]
  13. INNER JOIN Violations ON Records.[Violation Commited] = Violations.Violations
  14. WHERE 
  15. -- your specific predicates here
As cmhampton allready advised aliases make life easier:
MS SQL Syntax (Toggle Plain Text) 
  1. ...
  2. FROM 
  3. Records r INNER JOIN DriverInfo d ON r.[Plate Number] = d.[Plate Number]
  4. INNER JOIN Violations v ON r.[Violation Commited] = v.Violations
  5. WHERE 
  6. -- your specific predicates here
Instead of the very common = operator {<, <=, =>, >, !=} are very rarely used (to do
such called theta join). In your case you only need = operator (to do such called equi join).

You may check these joining constraints first to see whether redundant rows disappear. Then add further predicates to where clause to solving: "From all eggs that came from Hens , select those who are not eggs of Hen # 1." (This can never be get by join constraints only.)

krs,
tesu
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2009
Posts: 1
Reputation: edgarlip is an unknown quantity at this point 
Solved Threads: 0
edgarlip edgarlip is offline Offline
Newbie Poster

try this one

 
0
  #6
Mar 21st, 2009
sql Syntax (Toggle Plain Text) 
  1. SELECT productId, productName, productDescription, cost, ammount, minimumAmount
  2. FROM  dbo.Products
  3. WHERE (productId <> '123')
Last edited by peter_budo; Mar 21st, 2009 at 3:15 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the MS SQL Forum
Thread Tools Search this Thread



autogrowth connectingtodatabaseinuse count cursor data database datepart dbsize deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop maximum microsoft ms mssql multiple multithreading news number permission query reporting result server services sets source sql sqlserver sqlserver2005 supercomputing tables uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC