Name part search. Stumped...

/*
I'm having a problem making a select statement to return the
correct results.
Here is an example of what I'm trying to do. Two tables:
@.TblNameParts which contains the master list of all the name
parts and the MasterNameID which is a pointer to the master
record.
@.TblCriteria which is based on the parts of the name being
searched for.
I need a select statement which will return a list of the
valid MasterNameIDs. I know there is a simple solution
I'm just having a hard time finding it.
The logic is:
1) All @.TblCriteria.PartValue's must be in @.TblNameParts.PartValue
grouped by the @.TblNameParts.MasterNameID.
2) @.TblCriteria.PartValue's can be in any order in @.TblNameParts.
3) A @.TblNameParts.PartValue can only be used once for a
@.TblCriteria.PartValue. This is where I'm running into
trouble. You should be able to glean what I mean by the
example's correct results below.
I really appreciate any assistance you can provide, thanks.
*/
SET NOCOUNT ON
-- Main master name parts table.
DECLARE @.TblNameParts TABLE (
MasterNameID int,
PartIndex int,
PartValue varchar(50)
)
-- MasterNameID 10: 'Stacy Smith'
INSERT INTO @.TblNameParts VALUES (10, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (10, 1, 'Smith')
-- MasterNameID 15: 'John Doe'
INSERT INTO @.TblNameParts VALUES (15, 0, 'John')
INSERT INTO @.TblNameParts VALUES (15, 1, 'Doe')
-- MasterNameID 20: 'Stacy Ann Smith'
INSERT INTO @.TblNameParts VALUES (20, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (20, 1, 'Ann')
INSERT INTO @.TblNameParts VALUES (20, 2, 'Smith')
-- MasterNameID 25: 'Stacy Stacy'
INSERT INTO @.TblNameParts VALUES (25, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (25, 1, 'Stacy')
-- MasterNameID 30: 'Smith Stacy'
INSERT INTO @.TblNameParts VALUES (30, 0, 'Smith')
INSERT INTO @.TblNameParts VALUES (30, 1, 'Stacy')
-- Criteria to find a master name ID.
DECLARE @.TblCriteria TABLE (
CriteriaID int,
PartValue varchar(50)
)
-- Search 1 Criteria: 'John Doe'
INSERT INTO @.TblCriteria VALUES (0, 'John')
INSERT INTO @.TblCriteria VALUES (1, 'Doe')
-- Search 1 Correct Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 2 Criteria: 'Stacy'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
-- Search 2 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 3 Criteria: 'Stacy Smith'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
INSERT INTO @.TblCriteria VALUES (1, 'Smith')
-- Search 3 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 4 Criteria: 'Stacy Stacy'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
INSERT INTO @.TblCriteria VALUES (1, 'Stacy')
-- Search 4 Correct Result:
-- MasterNameID 25
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 25
SET NOCOUNT OFFGoogle for "relational division". There's a very comprehensive article on th
e
subject by Joe Celko.
ML
http://milambda.blogspot.com/

name of month from int value

What is the best way to get the month name from an int value
within a mssql statement ?
(there is no need to consider the language settings)

e.g.
1 -> January
11 -> Novembre

My first approach seems to work but is very ugly:
CASE WHEN my_month_int<10
THEN DATENAME(month,"20000"+cast(my_month_int as varchar(2))+"15 00:00:00")
ELSE DATENAME(month,"2000"+cast(my_month_int as varchar(2))+"15 00:00:00")
END AS my_month_nameselect datename(m,dateadd(m,my_month_int-1,0)) as my_month_name

:cool:|||Thanx a lot, r937. Nice trick.sql

N - curious?

Looking at an if exists statement created by SQL Enterprise Manager such as:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DeleteAllData]') and OBJECTPROPERTY(id, N'IsProcedure') =
1), what does the N do? I've seen cases where it is and isn't necessary, but
I cannot find documentation telling what it does. "N" is a very ineffective
search!!!The N represents a Unicode string.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"CR" <c@.home.com> wrote in message
news:%23DDNZixYGHA.4424@.TK2MSFTNGP05.phx.gbl...
Looking at an if exists statement created by SQL Enterprise Manager such as:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DeleteAllData]') and OBJECTPROPERTY(id, N'IsProcedure') =
1), what does the N do? I've seen cases where it is and isn't necessary, but
I cannot find documentation telling what it does. "N" is a very ineffective
search!!!|||See the Books Online topic 'Server-side Programming with Unicode'
(http://msdn2.microsoft.com/en-us/library/ms191313(SQL.90).aspx).
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"CR" <c@.home.com> wrote in message
news:%23DDNZixYGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Looking at an if exists statement created by SQL Enterprise Manager such
> as: if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DeleteAllData]') and OBJECTPROPERTY(id, N'IsProcedure')
> = 1), what does the N do? I've seen cases where it is and isn't necessary,
> but I cannot find documentation telling what it does. "N" is a very
> ineffective search!!!
>

MySQL Insert Conversion

Hi,
I have encoutnered the following MySQL statement and would like to
convert it to work with SQL Server 2005.
Is it possible to have two selects inserting into a row and if
affermative, how should it be done?
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Susan', 'Barker', '2002-09-12',
(select dept_id from department where name = 'Administration'),
'Vice President',
(select branch_id from branch where name = 'Headquarters'));
Thank You,
AlTry INSERT...SELECT:
INSERT INTO employee (
emp_id,
fname,
lname,
start_date,
dept_id,
title,
assigned_branch_id)
SELECT
NULL,
'Susan',
'Barker',
'2002-09-12',
(SELECT dept_id
FROM department
WHERE name = 'Administration'),
'Vice President',
(SELECT branch_id
FROM branch
WHERE name = 'Headquarters');
Hope this helps.
Dan Guzman
SQL Server MVP
<chribonn@.gmail.com> wrote in message
news:1138537484.578388.31890@.g43g2000cwa.googlegroups.com...
> Hi,
> I have encoutnered the following MySQL statement and would like to
> convert it to work with SQL Server 2005.
> Is it possible to have two selects inserting into a row and if
> affermative, how should it be done?
> insert into employee (emp_id, fname, lname, start_date,
> dept_id, title, assigned_branch_id)
> values (null, 'Susan', 'Barker', '2002-09-12',
> (select dept_id from department where name = 'Administration'),
> 'Vice President',
> (select branch_id from branch where name = 'Headquarters'));
> Thank You,
> Al
>|||insert employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
select null, 'Susan', 'Barker', '2002-09-12',
d.dept_id, 'Vice President', b.branch_id
from department d, branch b
where d.name = 'Administration'
and b.name = 'Headquarters'
"chribonn@.gmail.com" wrote:

> Hi,
> I have encoutnered the following MySQL statement and would like to
> convert it to work with SQL Server 2005.
> Is it possible to have two selects inserting into a row and if
> affermative, how should it be done?
> insert into employee (emp_id, fname, lname, start_date,
> dept_id, title, assigned_branch_id)
> values (null, 'Susan', 'Barker', '2002-09-12',
> (select dept_id from department where name = 'Administration'),
> 'Vice President',
> (select branch_id from branch where name = 'Headquarters'));
> Thank You,
> Al
>|||Thank you for your help.
Regards,
Al

Mysql and C++

hello
someone could tell me how could I insert SQL statement in a C++ code ?
If you know online documents easy to read about this subject, it will be
very welcome(in english or in french)..
Thanks[posted and mailed]

Borhen BOUAZIZ (bouazib4@.cti.ecp.fr) writes:
> someone could tell me how could I insert SQL statement in a C++ code ?
> If you know online documents easy to read about this subject, it will be
> very welcome(in english or in french)..

If you are using MySQL, you have posted to the wrong newsgroup. This
forum is for MS SQL Server.

If you are indeed using MS SQL Server, there are samples in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

my money cast no longer works in sql 2005

Hi

In a SQL 2000 stored proc I have the following statement to return a formatted price without the decimal place and cents

SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money(12,2)),1)

This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid attributes specified for type 'money')
By reading this forum I can see that I can use this instead:
select '$' + parsename(convert(varchar,convert(money,@.price),1),2)


I'm wondering why it doesn't work any more in SQL 2005 - are these differences documented anywhere ?

thanks
Bruce

It is because you are specifying precision and scale for money (money(12,2)) which is incorrect/invalid. Older versions allowed such syntaxes due to bug in parser and this has been corrected now. Do:

SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money),1)

|||> This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid
> attributes specified for type 'money') Where is there any documentation that states that MONEY has precision or scale? The SQL Server 2005 error message is correct. SQL Server 2000 was a little bit more lenient and just ignored the attributes, but don't blame the tool that you misused. :-) I would MUCH RATHER have this kind of thing break on me, than just ignore my specifications and lull me into believing that it is working correctly, when it is in fact not. DECLARE @.PRICE MONEY SET @.Price = 476.2354 SELECT CAST(@.Price AS MONEY(12,2)) Now, try instead: SELECT CONVERT(MONEY(12,2), @.Price) Hey, they got the error message right for CONVERT, but not for CAST. Server: Msg 291, Level 16, State 1, Line 3 CAST or CONVERT: invalid attributes specified for type 'money'
> By reading this forum I can see that I can use this instead:
> select '$' + parsename(convert(varchar,convert(money,@.price),1),2) Why do you need to convert to money? What is the original data type? If it is decimal or numeric, then the following will work fine: SELECT '$' + RTRIM(CONVERT(DECIMAL(12,2), @.price)) Or, just SELECT @.price And let the client/presentation tier add a dollar sign and round to the correct number of decimal places. I've actually never ended up using MONEY or SMALLMONEY in any database application I've built. Several reasons are listed here: http://www.aspfaq.com/2503
> I'm wondering why it doesn't work any more in SQL 2005 - are these
> differences documented anywhere ? No, that's the problem with having a loose syntax checker in previous versions, that allowed invalid things to work. I'm not even sure that Microsoft is aware of all of these obscure things that just happened to work in previous versions. If Microsoft were to document all of the undocumented behaviors that change, where should they put it? How would people find it? Who is going to tell them about all of the undocumented things that we all know about, and should be documented? There are some other examples, though I know they are aware of these, e.g. CREATE VIEW ... AS SELECT TOP 100 PERCENT ... ... ORDER BY This still passes the syntax checker, and you can still create the view, but now the lazy "SELECT * FROM viewname" no longer guarantees . Since the behavior people relied on was undocumented, . Another is CREATE TABLE dbo.foo ( col1 INT, col2 CHAR(1), ) This passes the syntax checker in both versions and works, even though there is a syntax error (dangling comma). A