multiselecting UNLIKE values

[harcaype]

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?

[cmhampton]

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:

SELECT 
		r.[Officer ID],
		r.[Plate Number],
		r.[Violation Commited],
		v.[Violation Code],
		r.[Street Name],
		r.[City of Apprehension],
		r.[Vehicle Category],
		r.[Vehicle Type],
		r.[Vehicle Brand],
		r.[Date/Time Apprehended]
FROM 
		Records r,
		Violations v,
		DriverInfo di
WHERE 
		r.[Plate Number] != di.[Plate Number]
		and r.[Violation Commited] = v.Violations

I hope this helps.

[harcaype]

Thanks! But I have also used != and the result is still the same :(

[cmhampton]

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.

[tesuji]

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.

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 
-- two constraints for inner joins
Records.[Plate Number] = DriverInfo.[Plate Number] AND
Records.[Violation Commited] = Violations.Violations
AND
-- your specific predicates following here

Instead of doing joins via where clause there is a more modern joining syntax:

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 inner join DriverInfo ON Records.[Plate Number] = DriverInfo.[Plate Number]
inner join Violations ON Records.[Violation Commited] = Violations.Violations
WHERE 
-- your specific predicates here

As cmhampton allready advised aliases make life easier:

...
FROM 
Records r inner join DriverInfo d ON r.[Plate Number] = d.[Plate Number]
inner join Violations v ON r.[Violation Commited] = v.Violations
WHERE 
-- 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

[edgarlip]

SELECT productId, productName, productDescription, cost, ammount, minimumAmount
FROM  dbo.Products
WHERE (productId <> '123')