Using IN(xxx, yyy) with table row

[laurenq uantrell]

I know I can use:

@FirstName nvarchar(50)

SELECT
c.FullName
FROM
dbo.tblCONTACTS c
WHERE
@FirstName IN("Bob", "Robert", "Bobby")

but...

How can I get the same results if I have a table of like names and each
row contains a group of similar names:

tblLIKENAMES
ID FirstNames
1 Bob, Robert, Bobby, Rob, Roberto
2 Frank, Franky, Francis
3 etc...

Where a user enters

@FirstName nvarchar(50), = "bob"
@LastName nvarchar(50) = "smith"
SELECT
c.Fullname
FROM
dbo.tblCONTACTS c
WHERE
c.LastName = @LastName
AND
/* Foo code: */
c.FirstName IN (SELECT FirstNames FROM tblLIKENAMES WHERE FirstNames
LIKE '%' + @FirstName + '%')


thanks,
lq

[--CELKO--]

Re: Using IN(xxx, yyy) with table row

YOu need to clean up your code and your data.

Standard SQL uses single quotes on strings; double quotes are the same
as the proprietary square brackets in T-SQL. Have you ever met anyone
with a CHAR(50) first name? No, you just pulled a magical number out
of the air without any thought.
[color=blue][color=green]
>> How can I get the same results if I have a table of like names and[/color][/color]
each row contains a group of similar names: <<

By ignoring First Normal Form and writing really bad SQL. This is
covered in the first 2-3 week of any RDBMS class. Next you will be
using IDENTITY columns instead of keys, prefixing tables with "tbl-"
and all the other things newbies do.

CREATE TABLE LikeNames
(base_name VARCHAR(15) NOT NULL,
synonym_name VARCHAR(15) NOT NULL PRIMARY KEY);

Trim and scrub your strings in the DDL and avoid the LIKE predicate for
an equality test.

SELECT @search_last_na me, C.first_name, L.synonym_name
FROM Contacts AS C , LikeNames AS L
WHERE C.last_name = @search_last_na me
AND C.first_name = L.synonym_name;

[Erland Sommarskog]

Re: Using IN(xxx, yyy) with table row

laurenq uantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> I know I can use:
>
> @FirstName nvarchar(50)
>
> SELECT
> c.FullName
> FROM
> dbo.tblCONTACTS c
> WHERE
> @FirstName IN("Bob", "Robert", "Bobby")[/color]

Assuming that you intend the " as string delimiter, this is a somewhat
funny SQL statement. If @FirstName is any of Bob, Robert or Bobby you
will get back all rows, else no rows at all. (If " is indeed an
identifier delimiter, the statement makes more sense, only the column
names are funny).
[color=blue]
> How can I get the same results if I have a table of like names and each
> row contains a group of similar names:
>
> tblLIKENAMES
> ID FirstNames
> 1 Bob, Robert, Bobby, Rob, Roberto
> 2 Frank, Franky, Francis
> 3 etc...[/color]

Don't store comma-elimited strings in database columns. There is no
end to the misery you run into. (OK, so it will be a lot easier to
handle this in SQL 2005, but you are not there yet.) Rotate the table
so that you have:

tblLIKENAMES
ID FirstName
1 Bob
1 Robert
1 Bobby
1 Rob
1 Roberto
2 Frank
2 Franky
2 Francis
[color=blue]
> Where a user enters
>
> @FirstName nvarchar(50), = "bob"
> @LastName nvarchar(50) = "smith"
> SELECT
> c.Fullname
> FROM
> dbo.tblCONTACTS c
> WHERE
> c.LastName = @LastName
> AND
> /* Foo code: */
> c.FirstName IN (SELECT FirstNames FROM tblLIKENAMES WHERE FirstNames
> LIKE '%' + @FirstName + '%')[/color]

Well, since I don't know how this tblLIKENAMES is related to tblCONTACTS
I can't really tell how to do this, whichever the table design.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[laurenq uantrell]

Re: Using IN(xxx, yyy) with table row

Erland,
Your suggestion to change row: 1 Bob, Robert, Bobby
to rows:
1 Bob
1 Robert
1 Bobby
is the perfect solution to my problem.
I'll post the full solution code I came up with for others who may have
the same question.