Searching tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Searching tables

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 11:36:30

This subject probably isn't correct, but I'm not sure how else to describe what I want to do.

I have two tables.
Table 1:
Act_Num.....Code
1...........33
2...........23
3...........22
4...........26
5...........20

Table 2:
Part_ID.....Act_Listings
1927..........33 14
5819..........23 26

What I need to do is identify records from Table 2 that do not have an Activity in the Act_Listing field contained in Table 1. So for Part_ID=1927, code 33 exists in Table 1, but code 14 doesn't, so that record should be selected.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-06-03 : 11:39:39

so does act_listings has numbers separated by a space?

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 11:45:42

Yes. I know I need to use some form of this code for that:

CHARINDEX(Table2.Code, Table1.Act_Listings) = 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 12:28:22

Will there only be two activities in Act_Listings in Table2? Show us a data example of each possible format for that column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 12:33:32

No, there can be anywhere between 1 and 4 or maybe even 5. The format should be activity numbers separated by spaces eg. "24 36 33". Regardless of how they codes are separated, I thought using CHARINDEX would work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 13:06:21

Use a split function to split your column: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Use space as your delimiter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 13:11:25

Create a function like this:-

CREATE FUNCTION dbo.ParseValues
(
@Str  varchar(1000)
)
RETURNS @RESULT TABLE
(
ID int IDENTITY(1,1),
Value int
)
AS
BEGIN
DECLARE @Number varchar(10)

WHILE @Str IS NOT NULL
BEGIN
SELECT @Number=CASE WHEN CHARINDEX(' ',@Str) > 0 THEN LEFT(@Str,CHARINDEX(' ',@Str)-1)
               ELSE @Str
               END,
      @Str=CASE WHEN CHARINDEX(' ',@Str) > 0 THEN SUBSTRING(@Str,CHARINDEX(' ',@Str)+1,LEN(@Str)) 
               ELSE NULL
               END
INSERT INTO @RESULT ([Value]) 
SELECT CAST(@Number AS int)

END

RETURN

END

use function like this:-


SELECT main.Part_ID,main.Act_Listings
FROM  
(SELECT a.Part_ID,a.Act_Listings,b.Value
FROM Table2 a
CROSS APPLY dbo.ParseValues (Act_Listings) b
)main
LEFT JOIN Table1 tmp
ON tmp.Code=main.Value
WHERE tmp.Act_Num IS NULL

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 13:11:34

How would the CHARINDEX function not accomplish what I want to do, possibly more easily?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 13:20:01

CHARINDEX is already being used in the functions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 13:23:35

My reply was regarding your reply.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 13:27:41

I don't understand. The functions are already using CHARINDEX, both from the link I posted and the function that visakh16 posted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 13:28:32

Oh I didn't see your link somehow. n/m

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 13:33:54

Okay. I tried implementing the code visakh16 suggested and it doesn't appear to be working. Here are the actual tables and column names:

ACTIVITY_CORE_LISTING
ACTV_CORE.....ACTV_NAME
1...............33
2...............23
3...............22
4...............26
5...............20
6...............28
7...............42
8...............24
9...............14
12..............16
11..............25
13..............15
14..............18

Activity_List
PRTCPNT_ID......Activity List
1000059............33
1000086............33 38
1001881............20 38
1001906............24
1002403............24
1002539............20 38
1002828............20 38
1002870............33
1004340............24
1005639............24
1005644............33 38

And here is the query I have, altered from visakh16's code:

SELECT main.PRTCPNT_ID,main.[Activity List]
FROM  
(SELECT a.PRTCPNT_ID,a.[Activity List],b.Value
FROM Activity_List a
CROSS APPLY dbo.ParseValues ([Activity List]) b
)main
LEFT JOIN ACTIVITY_CORE_LISTING tmp
ON tmp.ACTV_NAME=main.Value
WHERE tmp.actv_core IS NULL

Here is the result:
PRTCPNT_ID.....Activity List
1000059..........33
1000059..........33
1000086..........33 38
1000086..........33 38
1000086..........33 38
1000086..........33 38
1000086..........33 38
1000086..........33 38
1001881..........20 38
1001881..........20 38
1001881..........20 38
1001881..........20 38
1001881..........20 38
1001881..........20 38

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 13:46:11

Try grouping:-

SELECT main.PRTCPNT_ID,main.[Activity List]
FROM  
(SELECT a.PRTCPNT_ID,a.[Activity List],b.Value
FROM Activity_List a
CROSS APPLY dbo.ParseValues (LTRIM(RTRIM([Activity List]))) b
)main
LEFT JOIN ACTIVITY_CORE_LISTING tmp
ON tmp.ACTV_NAME=main.Value
WHERE tmp.actv_core IS NULL
GROUP BY main.PRTCPNT_ID,main.[Activity List]

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 13:52:26

Awesome. That appears to work.

Unfortunately, I just found out that I only need to display records where NO activities in the Activity List field exist in the ACTIVITY_CORE_LISTING table. So if a record has an Activity List value of "33 38", then it should NOT appear, but if it has a value of "35" or "38 35" then it SHOULD appear.

Not sure how to alter the code I have...

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-03 : 14:55:47

Nevermind. I found a much easier way to do it based on the view that Activity_Listing is created. Thanks for the parse info, though! I'm sure it will come in handy in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources