July 9, 2008 at 5:22 am
Hi
I have tables like
1) tEmployee and tclients the data containg like
temployee
----------
Employeeid Clientids
---------- --------
100 12,10,13,14
101 15,16,17
103 Null
104 12,15,16
105 16,17,18
tClients
--------
Clientid Clientname
------- ----------
10 scott
12 tiger
13 xyz
14 abc
15 Huli
Now i have retrive the temployee details Where Clientids is null or
check the specific employee containg clientids those all clientid are present in client table if all clientid are present retrive the that Employee.
how to write a query for this requirement.
finally my output is
100 12,10,13,14
103 Null
i am trying like
first i am writing stored procedure(checkclients) for that i am passing employeeid internally it checks the all clientids are present or not if all cleent id are present it returns true.
But how to use in select command for this stored procedure
see below
Select * from temployee Where clientid is null or 'true'=exec checkclients @applicationno
it is throwing complie error.
please help me.
thanks in advance
Regards
swamy.
July 9, 2008 at 5:36 am
IMHO your clientidS column is a prove of bad design ! (one column containing a bunch of seperate dataitems)
Check out "normalization" at SSC.
You cannot execute a sproc in a query !
You may want to have a look at user defined functions.
In your case a "split" function may come in handy (available at SSC)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 9, 2008 at 5:53 am
Thank u.
yes first i am using The function only but to split the clientid
i am using the temp tables but in a function we cant use the temp table .
Please help me how to split the clientid's by using function.
Regards
Swamy.
July 9, 2008 at 6:31 am
IMO you are turning your cat inside out.:ermm:
If you stick to this bad design, your queries will not perform well !!
resulting in slow applications and over utilised servers.
If you install this function...
/*
* Split a delimited string using a table valued function with XML
* thank you Jeff Moden (SSC)
*/
-- table-valued function
CREATE FUNCTION dbo.fn_Split2tb(@Data varchar(4000),
@Delimiter varchar(10) = ',')
RETURNS @tblSplit TABLE
(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Item varchar(4000) NULL)
AS
BEGIN
/*
select e.*
from pubs..employee E
inner join dbo.fn_Split2tb('ikke,jij,hij,zij,Karin',',') U
on E.fname = U.item
*/
DECLARE @x XML
SET @x = ' '
INSERT INTO @tblSplit (Item)
SELECT x.i.value('.', 'VARCHAR(4000)')
FROM @x.nodes('//i') x(i)
RETURN
END
you can pull over the temployee rows that contain faulty data using :
create table #tmp (myid int not null identity(1,1), FKids varchar(100))
create table #tmpFK (myid int not null , myname varchar(100))
set nocount on
insert into #tmpFK values (1,'a')
insert into #tmpFK values (2,'b')
insert into #tmpFK values (3,'c')
insert into #tmpFK values (4,'d')
insert into #tmpFK values (5,'e')
insert into #tmpFK values (6,'f')
insert into #tmpFK values (7,'g')
insert into #tmp values ('1,2,3')
insert into #tmp values ('5,100,7')
insert into #tmp values ('5,6,7')
insert into #tmp values ('5,6,200')
insert into #tmp values ('300,6,1')
set nocount off;
Select E.*
, U.*
from #tmp E
outer apply dbo.fn_Split2tb(E.FKids,',') U
left join #tmpFK F
on F.myid = U.item
where F.myid is null
drop table #tmp
drop table #tmpFK
-- ItemID and Item will point to the position in the string and the value
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 9, 2008 at 6:47 am
You don't need the overhead of a "split" function for this... just a Tally table. Please see the section titled "One Final "Split" Trick with the Tally Table" in the following URL which also explains how to build and use a Tally Table... I think you'll find it's well worth the read because a Tally Table can be used to solve a huge number of problems that either require a Cursor, While Loop, or some trick with XML...
http://www.sqlservercentral.com/articles/TSQL/62867/
Here's a full solution to the problem... as always, documentation is in the code... and do notice how to create data for testing... this is the way most of us prefer to see data listed on a forum question so we can concentrate on answering the question instead of building test data. See the Link in my signature for more details, please.
--===========================================================================================================
-- Create a demonstration table and populate it.
-- THIS IS NOT PART OF THE SOLUTION.
-- This is the best way to submit data on a forum.
-- See the link in may signature line for why
--===========================================================================================================
--===== Conditionally drop the demo table so can rerun code to "play"
IF OBJECT_ID('TempDB..#DemoTable','U') IS NOT NULL
DROP TABLE #DemoTable
--===== Create the demo table
CREATE TABLE #DemoTable
(EmployeeID INT, ClientIDs VARCHAR(100))
--===== Populate the demo table with test data from the forum post
INSERT INTO #DemoTable
(EmployeeID,ClientIDs)
SELECT '100','12,10,13,14' UNION ALL
SELECT '101','15,16,17' UNION ALL
SELECT '103',Null UNION ALL
SELECT '104','12,15,16' UNION ALL
SELECT '105','16,17,18'
--===========================================================================================================
-- Demonstrate the solution
--===========================================================================================================
;WITH
cteSplit AS
(--==== Split or "Normalize" the whole table at once
SELECT EmployeeID,
SUBSTRING(','+dt.ClientIDs+',',N+1,CHARINDEX(',',','+dt.ClientIDs+',',N+1)-N-1) AS ClientID
FROM dbo.Tally t
CROSS JOIN #DemoTable dt
WHERE N < LEN(','+dt.ClientIDs+',')
AND SUBSTRING(','+dt.ClientIDs+',',N,1) = ',' )
,
cteUniqueClient AS
(--==== Find ClientIDs that only appear once
SELECT ClientID
FROM cteSplit
GROUP BY ClientID
HAVING COUNT(*) = 1)
--===== List the items that have ANY unique ClientID or a Null for ClientIDs
SELECT DISTINCT dt.EmployeeID,dt.ClientIDs
FROM cteSplit s
INNER JOIN cteUniqueClient uc ON s.ClientID = uc.ClientID
INNER JOIN #DemoTable dt ON dt.EmployeeID = s.EmployeeID
OR dt.ClientIDs IS NULL
Last but not least, ALZDBA is absolutely correct... the CSV column called ClientIDs is an abomination so far as databases go. It really should be repaired and, if you take the time to read the section of the Tally Table article I recommended, you now know how to do that. In fact, the cteSplit code in the example above also does that.
Don't balk at making a Tally Table... it has uses people haven't even thought of, yet...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2008 at 6:51 am
I just fixed the $*$#&! smiley faces that showed up in the above code... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply