SQL WHERE syntax error when using separate account on the left
I have a suggestion WHERE
that logically I want it to return results when the first digit of short names doesn't match.
Short names can be:
1.1
1.2
2.1
2.2
Sample WITHOUT data WHERE
:
+-----------+--------+------+
| Shortname | number | ID |
+-----------+--------+------+
| 2.1 | 1 | 3333 |
| 1.1 | 60 | 3333 |
| 1.2 | 90 | 3333 |
| 2.1 | 50 | 4444 |
| 2.2 | 30 | 4444 |
| 1.1 | 80 | 5555 |
| 1.2 | 10 | 5555 |
+-----------+--------+------+
The expected WITH data WHERE
:
+-----------+--------+------+
| Shortname | number | ID |
+-----------+--------+------+
| 2.1 | 1 | 3333 |
| 1.1 | 60 | 3333 |
| 1.2 | 90 | 3333 |
+-----------+--------+------+
I tried the code:
SELECT shortname, number, id
FROM table
WHERE ((left(shortname,1) like '%1%') != ((left(shortname,1) like '%2%')
But it generates an error:
Msg 102, Level 15, State 1, Line 21
Invalid syntax near '!'.
Clarification UPDATE
I want the results for the ID, so the above example has IDs 3333, 4444, and 5555. I only want to return the ID 3333
because it doesn't only have one first character value in each shortname
. It contains both values 1
and 2
.
Where I don't want to see another ID, since the short names match at the first digit 1 = 1 and 2 = 2, etc.
source to share
If you want to return the ID
first few characters in shortname
, first look at getting a separate line count:
select id
from yourtable
group by id
having count(distinct left(shortname, 1)) > 1;
This should return you strings that have both 2 and 1 as the first character when associated with ids. Then you can use this to return the rest of the data:
;with cte as
(
select id
from yourtable
group by id
having count(distinct left(shortname, 1)) > 1
)
select
t.shortname,
t.number,
t.id
from yourtable t
inner join cte c
on t.id = c.id;
See SQL Fiddle with Demo . This returns:
| SHORTNAME | NUMBER | ID |
|-----------|--------|------|
| 2.1 | 1 | 3333 |
| 1.1 | 60 | 3333 |
| 1.2 | 90 | 3333 |
A more flexible option would be to get the characters before the decimal point and make sure you have a clear count of all digits. To do this, you will use the function CHARINDEX
along with LEFT
.
;with cte as
(
select id
from yourtable
group by id
having count(distinct left(shortname, charindex('.', shortname)-1)) > 1
)
select
t.shortname,
t.number,
t.id
from yourtable t
inner join cte c
on t.id = c.id;
See SQL Fiddle with Demo . This will return:
| SHORTNAME | NUMBER | ID |
|-----------|--------|------|
| 2.1 | 1 | 3333 |
| 1.1 | 60 | 3333 |
| 1.2 | 90 | 3333 |
| 14.1 | 5 | 6666 |
| 14.2 | 78 | 6666 |
| 24.1 | 89 | 6666 |
source to share
You are comparing two conditions! You might want to do something like this:
SELECT shortname, number, id
FROM table
WHERE ((left(shortname,1) like '%1%') OR ((left(shortname,1) like '%2%')))
Anyway the problem for you is:
SELECT shortname, number, id
FROM (
SELECT shortname, number, id, (SELECT COUNT(*)
FROM table AS TI
WHERE LEFT(ShortName, 1) = RIGHT(ShortName, 1)
AND TI.ID = T.ID) AS C
FROM table AS T) AS TT
WHERE C = 0
But this also excludes ID 3333 because it has one short name = 1.1.
source to share
What about:
Configuring MS SQL Server 2008 schema :
CREATE TABLE Sample
(
Shortname VARCHAR(10),
number tinyint,
Id int)
INSERT INTO Sample
VALUES
('2.1', 1, 3333),
('1.1', 60, 3333),
('1.2', 90, 3333),
('2.1', 50, 4444),
('2.2', 30, 4444),
('1.1', 80, 5555),
('1.2', 10, 5555)
Request 1 :
SELECT shortname, number, id
FROM [Sample] t1
WHERE EXISTS (
SELECT *
FROM [Sample] t2
WHERE t1.id = t2.id and LEFT(t1.shortname,1) <> LEFT(t2.shortname,1))
Results :
| SHORTNAME | NUMBER | ID |
|-----------|--------|------|
| 2.1 | 1 | 3333 |
| 1.1 | 60 | 3333 |
| 1.2 | 90 | 3333 |
source to share