SQL IN Query

[@sh]

A very simple one I hope?

I have a table like this...

ListID PropertyID MessageBody
1 75,62,2,4 erter tdfgs fd
2 6,25,75,23 dsfgsdfgsdfg sd
3 2,7,36,2 dfsdfgs dfgsdf
4 4,73,75,4 s dfgsdfg sfdg

I want to select a recordset containing only properties with a PropertyID of
75, I've tried this...

"SELECT * From Tbl_ListIDs WHERE " & Request("PropertyID") & " IN
PropertyID"

But I'm getting a SQL error of incorrect syntax? I appreciate that normally
the conditions of the IN statement are visa versa but is there an easier way
to achieve what I'm trying to do above?

Cheers, @sh

[rcamarda]

select * from tbl_listids where properid like '%75%'

[SQL]

what about 2
you will get back the second row with 25 and 23

create table Tbl_ListIDs (ListID int,PropertyID varchar(49))
insert into Tbl_ListIDs
select 1,'75,62,2,4' union all
select 2,'6,25,75,23' union all
select 3,'2,7,36,2' union all
select 4,'4,73,75,4' union all
select 5,'75,62,1,42'

--This should return only 2's that are not part of a bigger number
select * from tbl_listids where PropertyID like '%,2,%' OR
PropertyID like '2,%'
or PropertyID like '%,2'

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[Erland Sommarskog]

@sh (sp**@spam.com) writes:

ListID PropertyID MessageBody
1 75,62,2,4 erter tdfgs fd
2 6,25,75,23 dsfgsdfgsdfg sd
3 2,7,36,2 dfsdfgs dfgsdf
4 4,73,75,4 s dfgsdfg sfdg

I want to select a recordset containing only properties with a
PropertyID of 75, I've tried this...

"SELECT * From Tbl_ListIDs WHERE " & Request("PropertyID") & " IN
PropertyID"
First of all: do never include user-input data directly an SQL string
like that! That's opens for a security risk known as SQL injection.
Always used parameterised commands. This is also good for performance.

And don't use SELECT * in production code!
But I'm getting a SQL error of incorrect syntax? I appreciate that
normally the conditions of the IN statement are visa versa but is there
an easier way to achieve what I'm trying to do above?

There is no easy way, because this is a database design that violates the
first normal form by having a repeating group.

If you have control over the data model, I strongly recommend you change
the table definition, so that the property ids are strored in a subtable:

CREATE TABLE listproperties (
ListID int NOT NULL,
PropertyID int NOT NULL,
CONSTRAINT pk_listprops PRIMARY KEY (ListID, PropertyID))

If you do not have control over the data model, this may be your best
bet:

SELECT ListID, MessageBody
FROM Tbl_ListIDs
WHERE ',' + ltrim(str(@propertyid)) + ',' LIKE ',' + PropertyID + ','

It is not going to perform very well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 19 May 2006 05:44:05 -0700, SQL wrote:

--This should return only 2's that are not part of a bigger number
select * from tbl_listids where PropertyID like '%,2,%' OR
PropertyID like '2,%'
or PropertyID like '%,2'

Hi Denis,

Or (shorter)

SELECT something
FROM listids
WHERE ',' + PropertyID + ',' LIKE '%,2,%'

--
Hugo Kornelis, SQL Server MVP

[--CELKO--]

Please read any book on RDBMS and learn what First Normal Form is
(1NF). You think that SQL is a MV database or perhaps an old COBOL file
with variant records. That is fundamentally wrong. Do you understand
what a scalar value is? How about an atomic value (slight difference)?
Normalize the schema, which is the real problem, and your question is
answered immediately by the data.

[@sh]

Many thanks for all the help posted in response, I think I'll take the
suggestion below although do take into account all points raised, especially
that regarding SQL Injection - this application isn't a public one, its
within a secure administration panel, therefore the user would first have to
get beyond the security aspect before they could even attempt such an
exploit.

Also, all users are actually staff and so the risks are minimal.

Thanks once again!

Cheers, Ash

"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info.INVALID> wrote in message
news:5n********************************@4ax.com...

On 19 May 2006 05:44:05 -0700, SQL wrote:

--This should return only 2's that are not part of a bigger number
select * from tbl_listids where PropertyID like '%,2,%' OR
PropertyID like '2,%'
or PropertyID like '%,2'

Hi Denis,

Or (shorter)

SELECT something
FROM listids
WHERE ',' + PropertyID + ',' LIKE '%,2,%'

--
Hugo Kornelis, SQL Server MVP

[Jeff Kish]

On 19 May 2006 15:58:35 -0700, "--CELKO--" <jc*******@earthlink.net>
wrote:

Please read any book on RDBMS and learn what First Normal Form is
(1NF). You think that SQL is a MV database or perhaps an old COBOL file
with variant records. That is fundamentally wrong. Do you understand
what a scalar value is? How about an atomic value (slight difference)?
Normalize the schema, which is the real problem, and your question is
answered immediately by the data.

Excellent advise, however not realistic if the data model is not under
the posters control, right?
Yeah, not very normalized.. indeed!