Query for an expert

I have been trying to find someone who can help me with this one and no luck
so far. Maybe an expert can do it, here it is:
I have a database with three tables, one table has "cases" the other table
has "persons" and the other has the "links", There are more fields which are
not relevant for the query.
Table # 1 (Cases)
Id: Integer, autonumber
CaseId: The Case number (nvarchar)
Table # 2 (Users)
UserId: Integer, autonumber
Name: Name of the person (nvarchar)
Table # 3 (Links)
Attsid : Integer, autonumber (Links)
CaseId: The Id of the case (Comes from the "cases" table, which is table #
1)
UserId: The id of the person (Comes from the "users" table, which is table #
2)
Note: In table # 3 "users" are linked to the "cases", so there could be
multiple users for each case, example:
Example (With values)
Table # 1
Id: 1
CaseId: Case number one
Table # 2
UserId: 1
Name: Alejandro
UserId: 2
Name: Mariana
UserId: 3
Name: John
Table # 3
AttsId: 1
CaseId: 1
UserId: 1
AttsId: 2
CaseId: 1
UserId: 2
AttsId: 3
CaseId: 1
UserId:3
In this example all 3 users are "linked" to case # 1
I have a search page and a results page, using Dreamweaver to develop. In
the "search" page users select the "Users" from a drop down menu and the
parameter is passed to the results page as a comma delimited string, for the
example above the string would be: 1,2,3
The query I need to build is one that will bring all "cases" in which all
the selected users are linked to.
If all users are linked to case #1 and user selects all users, or user 1,2
or user 1,3 or user 1, then the case should be displayed, but if for example
the case only has user 1,2 linked (In the links table) and user selects
users 1,2,3 from the search page then the case should not be displayed.
I haven't been able to figure out the query, I am hoping some expert may
give some light on this complex task for me.
Any help is greately appreciated.
AleksCan you post DDL for the tables and some sample data in SQL format? Then it
is much easier to test and work with. You also need to rethink the
misnaming of CaseID having one meaning in one table, and another in a
different one. Consider renaming CaseId in the Cases table to CaseNumber,
or something like that.
From what I can read, it sounds like you want to join:
select cases.columns
from users
join table$delimitedString('1,2,3',',')
on cast(value as integer) = users.userId
join links
on users.userId = links.userId
join cases
on cases.id = links.caseId
Where the table function is one of mine, though there are slightly better
ways of doing this, this one works fine enough:
create function table$delimitedString
(
@.input varchar(8000),
@.delimiter char(1) = ','
) returns @.output table (
id int identity, --gives us ordering abilities
value varchar(2000)
)
as
begin
--simply loop and peel off each of the values and insert into the tbl
while (1=1)
begin
--check to see if the delimiter exists in the string
if charindex(@.delimiter,@.input) = 0
begin
--if not, we just insert the string into the table
insert into @.output (value)
values (@.input)
break
end
else
begin
--if so, we get the substring of the first character, to
--the first instance of the delimiter in the string,
--less one, so we don't get the delimiter in the output
insert into @.output (value)
values( substring(@.input,1,
limiter,@.input) -1)) --then we trim off the first item and
delim from the val
set @.input = substring(@.input,charindex(@.delimiter,@.i
nput)
+ 1 ,len(@.input))
end
end
return
end
go
For more information about using array-type constructs, check out:
http://www.sommarskog.se/arrays-in-sql.html
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:OtwYe7IBFHA.2076@.TK2MSFTNGP10.phx.gbl...
>I have been trying to find someone who can help me with this one and no
>luck so far. Maybe an expert can do it, here it is:
> I have a database with three tables, one table has "cases" the other
> table has "persons" and the other has the "links", There are more fields
> which are not relevant for the query.
> Table # 1 (Cases)
> Id: Integer, autonumber
> CaseId: The Case number (nvarchar)
> Table # 2 (Users)
> UserId: Integer, autonumber
> Name: Name of the person (nvarchar)
> Table # 3 (Links)
> Attsid : Integer, autonumber (Links)
> CaseId: The Id of the case (Comes from the "cases" table, which is table #
> 1)
> UserId: The id of the person (Comes from the "users" table, which is table
> # 2)
> --
> Note: In table # 3 "users" are linked to the "cases", so there could be
> multiple users for each case, example:
> --
> Example (With values)
> Table # 1
> Id: 1
> CaseId: Case number one
> Table # 2
> UserId: 1
> Name: Alejandro
> UserId: 2
> Name: Mariana
> UserId: 3
> Name: John
> Table # 3
> AttsId: 1
> CaseId: 1
> UserId: 1
> AttsId: 2
> CaseId: 1
> UserId: 2
> AttsId: 3
> CaseId: 1
> UserId:3
> In this example all 3 users are "linked" to case # 1
> --
> I have a search page and a results page, using Dreamweaver to develop. In
> the "search" page users select the "Users" from a drop down menu and the
> parameter is passed to the results page as a comma delimited string, for
> the example above the string would be: 1,2,3
> The query I need to build is one that will bring all "cases" in which all
> the selected users are linked to.
> If all users are linked to case #1 and user selects all users, or user 1,2
> or user 1,3 or user 1, then the case should be displayed, but if for
> example the case only has user 1,2 linked (In the links table) and user
> selects users 1,2,3 from the search page then the case should not be
> displayed.
> I haven't been able to figure out the query, I am hoping some expert may
> give some light on this complex task for me.
> Any help is greately appreciated.
> Aleks
>|||On Thu, 27 Jan 2005 11:40:10 -0500, Aleks wrote:

>I have a database with three tables, one table has "cases" the other table
>has "persons" and the other has the "links", There are more fields which ar
e
>not relevant for the query.
(snip)
Hi Aleks,
I'll come to your query later. First some comments on your tables.

>Table # 1 (Cases)
>Id: Integer, autonumber
>CaseId: The Case number (nvarchar)
Do you really need an automatically generated ID? Can't you use the CaseId
as primary key instead?

>Table # 2 (Users)
>UserId: Integer, autonumber
>Name: Name of the person (nvarchar)
>Table # 3 (Links)
>Attsid : Integer, autonumber (Links)
>CaseId: The Id of the case (Comes from the "cases" table, which is table #
>1)
>UserId: The id of the person (Comes from the "users" table, which is table
#
>2)
I really fail to see why you would want an auto-generated ID for this
table. Just upgrade your current unique contraint on (CaseId, UserId) to a
primary key constraint.
Also, your choice of columns names is confusing. I assume that the column
named CaseId in Links should not match to the column of the same name in
Cases, but to the column Id instead? Usually, two columns having the same
name implies that they are the same - I'd intuitively join these tables on
the CaseId column common to them both (and that would result in a datatype
mismatch).

>I have a search page and a results page, using Dreamweaver to develop. In
>the "search" page users select the "Users" from a drop down menu and the
>parameter is passed to the results page as a comma delimited string, for th
e
>example above the string would be: 1,2,3
The first thing to do is to build a temp table from that string. A good
discussions on possible ways to do that can be found on Erland's site:
http://www.sommarskog.se/arrays-in-sql.html

>The query I need to build is one that will bring all "cases" in which all
>the selected users are linked to.
Assuming the cases to find are in the temp table #sought, with column name
CaseId, try this query:
SELECT l.UserId
FROM Links AS l
INNER JOIN #sought AS s
ON s.CaseId = l.CaseId
GROUP BY l.UserId
HAVING COUNT(*) = (SELECT COUNT(*) FROM #sought)
Since you didn't provide CREATE TABLE statements to recreate your tables,
nor INSERT statements to fill then with some test data, I didn't test this
query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I think this is a mix of "arrays in t-sql" and "relational division". Here
are a couple of links where you can read about these topics and also an
example of how to accomplish your query.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Relational Division
http://www.dbazine.com/celko1.html
Example:
use tempdb
go
create table cases(
caseid int not null primary key
)
go
create table users(
userid int not null primary key,
fname varchar(50) not null,
lname varchar(50) not null
)
go
create table links(
caseid int not null,
userid int not null,
constraint pk_links primary key (caseid, userid),
constraint fk_links_cases foreign key (caseid) references cases(caseid),
constraint fk_links_users foreign key (userid) references users(userid))
go
insert into cases values(1)
insert into cases values(2)
insert into users values(1, 'a', 'b')
insert into users values(2, 'b', 'c')
insert into users values(3, 'c', 'd')
insert into users values(4, 'd', 'e')
insert into links values(1, 1)
insert into links values(1, 2)
insert into links values(1, 3)
insert into links values(2, 2)
insert into links values(2, 3)
go
declare @.s varchar(8000)
create table #t(userid int not null unique)
set @.s = '1,2,3'
-- I am using this method for simplicity (Read Erland's article)
set @.s = 'select ' + replace(@.s, ',', ' union all select ')
insert into #t
exec(@.s)
-- should bring caseid 1 because userid 1 is not linked to caseid 2
select
l.CaseId
from
Links as l
inner join
#t as t
on l.UserId = t.UserId
group by
l.CaseId
having
count(distinct t.UserId) = (select count(*) from #t)
set @.s = '2,3'
set @.s = 'select ' + replace(@.s, ',', ' union all select ')
truncate table #t
insert into #t
exec(@.s)
-- should bring 1 and 2
select
l.CaseId
from
Links as l
inner join
#t as t
on l.UserId = t.UserId
group by
l.CaseId
having
count(distinct t.UserId) = (select count(*) from #t)
drop table #t
go
drop table links, cases, users
go
AMB
"Aleks" wrote:

> I have been trying to find someone who can help me with this one and no lu
ck
> so far. Maybe an expert can do it, here it is:
> I have a database with three tables, one table has "cases" the other tabl
e
> has "persons" and the other has the "links", There are more fields which a
re
> not relevant for the query.
> Table # 1 (Cases)
> Id: Integer, autonumber
> CaseId: The Case number (nvarchar)
> Table # 2 (Users)
> UserId: Integer, autonumber
> Name: Name of the person (nvarchar)
> Table # 3 (Links)
> Attsid : Integer, autonumber (Links)
> CaseId: The Id of the case (Comes from the "cases" table, which is table #
> 1)
> UserId: The id of the person (Comes from the "users" table, which is table
#
> 2)
> --
> Note: In table # 3 "users" are linked to the "cases", so there could be
> multiple users for each case, example:
> --
> Example (With values)
> Table # 1
> Id: 1
> CaseId: Case number one
> Table # 2
> UserId: 1
> Name: Alejandro
> UserId: 2
> Name: Mariana
> UserId: 3
> Name: John
> Table # 3
> AttsId: 1
> CaseId: 1
> UserId: 1
> AttsId: 2
> CaseId: 1
> UserId: 2
> AttsId: 3
> CaseId: 1
> UserId:3
> In this example all 3 users are "linked" to case # 1
> --
> I have a search page and a results page, using Dreamweaver to develop. In
> the "search" page users select the "Users" from a drop down menu and the
> parameter is passed to the results page as a comma delimited string, for t
he
> example above the string would be: 1,2,3
> The query I need to build is one that will bring all "cases" in which all
> the selected users are linked to.
> If all users are linked to case #1 and user selects all users, or user 1,2
> or user 1,3 or user 1, then the case should be displayed, but if for examp
le
> the case only has user 1,2 linked (In the links table) and user selects
> users 1,2,3 from the search page then the case should not be displayed.
> I haven't been able to figure out the query, I am hoping some expert may
> give some light on this complex task for me.
> Any help is greately appreciated.
> Aleks
>
>|||I suppose you could solve this different ways, but I tried it like this and
it seems to work. However, it involves some dynamic sql and I'm not sure
that's what you want. (dyn sql is mostly due to the comma seperated list
actually)
-- init fase
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 't_case' AND xtype = 'U
')
BEGIN
SELECT CaseId = IDENTITY(int, 1, 1),
CaseName = Convert(varchar(500), 'casexyz')
INTO t_case
WHERE 1 = 2
CREATE UNIQUE CLUSTERED INDEX uq0_case ON t_case (CaseId)
CREATE UNIQUE INDEX uq1_case ON t_case (CaseName)
INSERT t_case (CaseName) VALUES ('case xyz2')
INSERT t_case (CaseName) VALUES ('case xyz3')
INSERT t_case (CaseName) VALUES ('case xyz4')
INSERT t_case (CaseName) VALUES ('case abc')
INSERT t_case (CaseName) VALUES ('case def')
INSERT t_case (CaseName) VALUES ('case pqr')
INSERT t_case (CaseName) VALUES ('case all')
END
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 't_case' AND xtype = 'U
')
BEGIN
SELECT UserId = IDENTITY(int, 1, 1),
UserName = Convert(varchar(500), 'John Doe')
INTO t_user
WHERE 1 = 2
CREATE UNIQUE CLUSTERED INDEX uq0_user ON t_user (UserId)
CREATE UNIQUE INDEX uq1_user ON t_user (UserName)
INSERT t_user (UserName) VALUES ('Julius Ceasar')
INSERT t_user (UserName) VALUES ('Nero')
INSERT t_user (UserName) VALUES ('August')
INSERT t_user (UserName) VALUES ('Alexander')
END
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 't_case' AND xtype = 'U
')
BEGIN
SELECT LinkId = IDENTITY(int, 1, 1),
CaseId = Convert(int, 0),
UserId = Convert(int, 0)
INTO t_link
WHERE 1 = 2
CREATE UNIQUE CLUSTERED INDEX uq0_user ON t_link (CaseId, UserId)
INSERT t_link (CaseId, UserId)
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%xy%' AND UserName LIKE '%u%'
UNION ALL
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%abc%' AND UserName LIKE '%e%'
UNION ALL
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%pqr%' AND UserName LIKE '%a%'
UNION ALL
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%all%' AND UserName LIKE '%'
END
SET NOCOUNT OFF
-- full overview :
SELECT l.LinkId, u.UserId, u.UserName, c.CaseId, c.CaseName
FROM t_link l
JOIN t_user u ON u.UserId = l.UserId
JOIN t_case c ON c.CaseId = l.CaseId
ORDER BY c.CaseName, u.UserName
-- limit to list of users req : needs to be comma seperated)
DECLARE @.user_list varchar(500)
DECLARE @.dyn_sql varchar(8000)
SELECT @.user_list = ''
SELECT @.user_list = @.user_list + Convert(varchar, UserId) + ','
FROM t_user
WHERE UserName IN ('Nero', 'Julius Ceasar', 'Alexander')
-- WHERE UserName IN ('Nero')
-- WHERE UserName IN ('Julius Ceasar', 'Alexander')
-- WHERE UserName IN ('Julius Ceasar')
SELECT @.user_list = Left(@.user_list, Len(@.user_list) - 1) -- strip last comm
a
PRINT @.user_list
SELECT @.dyn_sql = 'SELECT user_list = ''' + Rtrim(@.user_list) + ''',
l.LinkId, u.UserId, u.UserName, c.CaseId, c.CaseName '
+ ' FROM t_link l'
+ ' JOIN t_user u ON u.UserId = l.UserId'
+ ' JOIN t_case c ON c.CaseId = l.CaseId'
+ ' WHERE u.UserId IN (' + @.user_list + ')'
-- make sure there is no user in our user_list that is
NOT linked to our case
+ ' AND NOT EXISTS (SELECT * FROM t_user WHERE UserId IN
(' + @.user_list + ') AND UserId NOT IN (SELECT UserId FROM t_link WHERE
CaseId = l.CaseId))'
+ ' ORDER BY c.CaseId, u.UserName'
PRINT @.dyn_sql
EXEC (@.dyn_sql)|||(previous post went boink, hope I'm not double posting)
As I said : The dynamic sql is mostly due to the comma seperated list, if
you'd create a function to make it a table or recordset you probably can
avoid dynamic sql by simply joining on the table/recordset
This seems to do more or less what you need :
-- init fase
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 't_case' AND xtype = 'U
')
BEGIN
SELECT CaseId = IDENTITY(int, 1, 1),
CaseName = Convert(varchar(500), 'casexyz')
INTO t_case
WHERE 1 = 2
CREATE UNIQUE CLUSTERED INDEX uq0_case ON t_case (CaseId)
CREATE UNIQUE INDEX uq1_case ON t_case (CaseName)
INSERT t_case (CaseName) VALUES ('case xyz2')
INSERT t_case (CaseName) VALUES ('case xyz3')
INSERT t_case (CaseName) VALUES ('case xyz4')
INSERT t_case (CaseName) VALUES ('case abc')
INSERT t_case (CaseName) VALUES ('case def')
INSERT t_case (CaseName) VALUES ('case pqr')
INSERT t_case (CaseName) VALUES ('case all')
END
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 't_case' AND xtype = 'U
')
BEGIN
SELECT UserId = IDENTITY(int, 1, 1),
UserName = Convert(varchar(500), 'John Doe')
INTO t_user
WHERE 1 = 2
CREATE UNIQUE CLUSTERED INDEX uq0_user ON t_user (UserId)
CREATE UNIQUE INDEX uq1_user ON t_user (UserName)
INSERT t_user (UserName) VALUES ('Julius Ceasar')
INSERT t_user (UserName) VALUES ('Nero')
INSERT t_user (UserName) VALUES ('August')
INSERT t_user (UserName) VALUES ('Alexander')
END
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 't_case' AND xtype = 'U
')
BEGIN
SELECT LinkId = IDENTITY(int, 1, 1),
CaseId = Convert(int, 0),
UserId = Convert(int, 0)
INTO t_link
WHERE 1 = 2
CREATE UNIQUE CLUSTERED INDEX uq0_user ON t_link (CaseId, UserId)
INSERT t_link (CaseId, UserId)
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%xy%' AND UserName LIKE '%u%'
UNION ALL
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%abc%' AND UserName LIKE '%e%'
UNION ALL
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%pqr%' AND UserName LIKE '%a%'
UNION ALL
SELECT c.CaseId, u.UserId FROM t_case c CROSS JOIN t_user u WHERE
c.CaseName Like '%all%' AND UserName LIKE '%'
END
SET NOCOUNT OFF
-- full overview :
SELECT l.LinkId, u.UserId, u.UserName, c.CaseId, c.CaseName
FROM t_link l
JOIN t_user u ON u.UserId = l.UserId
JOIN t_case c ON c.CaseId = l.CaseId
ORDER BY c.CaseName, u.UserName
-- limit to list of users req : needs to be comma seperated)
DECLARE @.user_list varchar(500)
DECLARE @.dyn_sql varchar(8000)
SELECT @.user_list = ''
SELECT @.user_list = @.user_list + Convert(varchar, UserId) + ','
FROM t_user
WHERE UserName IN ('Nero', 'Julius Ceasar', 'Alexander')
-- WHERE UserName IN ('Nero')
-- WHERE UserName IN ('Julius Ceasar', 'Alexander')
-- WHERE UserName IN ('Julius Ceasar')
SELECT @.user_list = Left(@.user_list, Len(@.user_list) - 1) -- strip last comm
a
PRINT @.user_list
SELECT @.dyn_sql = 'SELECT user_list = ''' + Rtrim(@.user_list) + ''',
l.LinkId, u.UserId, u.UserName, c.CaseId, c.CaseName '
+ ' FROM t_link l'
+ ' JOIN t_user u ON u.UserId = l.UserId'
+ ' JOIN t_case c ON c.CaseId = l.CaseId'
+ ' WHERE u.UserId IN (' + @.user_list + ')'
-- make sure there is no user in our user_list that is
NOT linked to our case
+ ' AND NOT EXISTS (SELECT * FROM t_user WHERE UserId IN
(' + @.user_list + ') AND UserId NOT IN (SELECT UserId FROM t_link WHERE
CaseId = l.CaseId))'
+ ' ORDER BY c.CaseId, u.UserName'
PRINT @.dyn_sql
EXEC (@.dyn_sql)