David Jason David Jason - 4 months ago 13
SQL Question

SQL Select from all rows except this ID doesn't work

I want to show all rows except the one with the id '64'. But it shows the row with the id '64' too.

My SQL code is:

SELECT * from uploads where status='1' AND id != '64' AND typ = 'video' AND tags LIKE '%dad%' OR tags LIKE '%spiegel%' OR tags LIKE '%wasser%' order by timestamp DESC LIMIT 4


TABLE SQL:

CREATE TABLE IF NOT EXISTS uploads (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
user_id varchar(500) NOT NULL DEFAULT '0',
title varchar(500) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '0',
description varchar(500) NOT NULL DEFAULT '0',
file varchar(500) NOT NULL DEFAULT '0',
url varchar(500) NOT NULL DEFAULT '0',
thumbnail varchar(500) NOT NULL DEFAULT '0',
tags varchar(500) NOT NULL DEFAULT '0',
place varchar(500) NOT NULL DEFAULT '0',
categorie_id varchar(500) NOT NULL DEFAULT '0',
storage varchar(120) NOT NULL DEFAULT '0',
file_storage varchar(120) NOT NULL DEFAULT '0',
video_length varchar(120) NOT NULL DEFAULT '0',
status varchar(120) NOT NULL DEFAULT '0',
timestamp varchar(120) NOT NULL DEFAULT '0',
typ varchar(500) NOT NULL DEFAULT '0',
comments varchar(500) NOT NULL DEFAULT '1',
premium varchar(500) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=72 ;


INSERT INTO uploads (id, user_id, title, description, file, url, thumbnail, tags, place, categorie_id, storage, file_storage, video_length, status, timestamp, typ, comments, premium) VALUES
(1, '1', 'test', 'test', '', '0', '', 'dad, tags, wasser, spiegel', '', '0', '1983349', '0', '0', '1', '1467016374', 'image', '1', '0'),
(64, '1', 'Taddy', '', '0', '', '', 'dad, spiegel, wasser', '', '0', '0', '0', '-', '1', '1467387022', 'video', '1', '1');


SQL Fiddle:
http://sqlfiddle.com/#!9/0f3b54/1

But it doesn't work :( Can anyone help me

Answer

Change it to this syntax (<> and 64 as integer) and you also might add parenthesis to your last condition like bellow:

SELECT * FROM uploads 
WHERE status='1' 
AND id <> 64 
AND typ = 'video' 
AND (tags LIKE '%dad%' OR tags LIKE '%spiegel%' OR tags LIKE '%wasser%')
ORDER BY timestamp DESC LIMIT 4

What's the differance between != and <>?

Technically they function the same if you’re using MS SQL aka T-SQL. If you're using it in stored procedures there is no performance reason to use one over the other. It then comes down to personal preference. I prefer to use <> as it is ANSI compliant.

You can find links to the various ANSI standards at...

http://en.wikipedia.org/wiki/SQL

Comments