Query Help - 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 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-24 : 13:02:39

I have table like this

Select * from usr
UID UName Blocked Added
----------- ---------- -------------------------------------------------- --------------------------------------------------
1 A 2,3,4,5 6
2 B 1,4 5
3 C 1,2,5,6 4
4 D 1,3,4,5 3

select * from usr Where uid = 2

UID UName Blocked Added
----------- ---------- -------------------------------------------------- --------------------------------------------------
2 B 1,4 5

The Blocked 1,4 sholud not come in the result set while i give the uid 2
ie
Select * from usr Where ..... and uid = 2

I need the result like

UID UName Blocked Added
2 B 1,4 5
3 C 1,2,5,6 4

How can i do this.

Thanks
Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 13:13:57

Why not 4 too?

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 13:15:39

Also, you have 4 columns in your header and about 5-7 columns in the data you are posting?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-24 : 13:29:51

because 4 is in blocked list of uid

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-24 : 13:31:36

quote:
Originally posted by dinakar

Also, you have 4 columns in your header and about 5-7 columns in the data you are posting?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Is that wrong..?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 13:33:41

quote:
Originally posted by CSK

quote:
Originally posted by dinakar

Also, you have 4 columns in your header and about 5-7 columns in the data you are posting?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Is that wrong..?

It is not wrong. It is hard for us to understand what values are in what columns. If you could separate out the data more clearly it would be helpful.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 13:34:12

It's a CSV list...

UID   UName     Blocked    Added 
----- --------  ---------  ------
1     A         2,3,4,5    6

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 13:35:04

CSK, please explain your business rules more clearly.
Why is UID 3 also fetched when selecting UID = 2?

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-24 : 13:39:49

Assume like this
i will give uid 2, the corresponding blocked for the uid (1,4) excluded from the result set

BR:
The login user will block any other user. That list will be added in blocked column. Will i serach the user id the blocked list user id sholud not come in the result set

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-24 : 13:41:23

quote:
Originally posted by dinakar

quote:
Originally posted by CSK

quote:
Originally posted by dinakar

Also, you have 4 columns in your header and about 5-7 columns in the data you are posting?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Is that wrong..?

It is not wrong. It is hard for us to understand what values are in what columns. If you could separate out the data more clearly it would be helpful.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Okay Dinakar! I will keep it my mind.
Thnaks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 13:53:06

[code]
create table Test (UID int, UName varchar(5),Blocked varchar(40), Added int)
insert into test select 1,'A', '2,3,4,5', 6 union all
select 2,'B','1,4',5 union all
select 3,'C','1,2,5,6', 4 union all
select 4,'D','1,3,4,5' ,3
select * from test
declare @userid int, @blocked varchar(40), @sql varchar(500)
set @userid = 2
select @blocked = blocked from test where uid = @userid
/*
select * from test where uid = @userid
union
select * from test where uid not in ( select uid from test where uid in (1,4))
*/
set @sql = 'select * from test where uid = ' + convert(varchar,@userid) + '
union
select * from test where uid not in ( select uid from test where uid in (' + @blocked + '))'
print @sql
exec(@sql)
drop table Test
[/code]

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 14:00:06

You should normalize your database!
However...

-- Prepare sample data
DECLARE @Sample TABLE ([UID] VARCHAR(2), UName VARCHAR(2), Blocked VARCHAR(10), Added VARCHAR(2))

INSERT	@Sample
SELECT	'1', 'A', '2,3,4,5', '6' UNION ALL
SELECT	'2', 'B', '1,4', '5' UNION ALL
SELECT	'3', 'C', '1,2,5,6', '4' UNION ALL
SELECT	'4', 'D', '1,3,4,5', '3'

-- Show the expected output
SELECT		s2.[UID],
		s2.UName,
		s2.Blocked,
		s2.Added
FROM		@Sample AS s1
INNER JOIN	@Sample AS s2 ON ',' + s1.Blocked + ',' NOT LIKE '%,' + s2.[UID] + ',%'
WHERE		s1.[UID] = 2

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-24 : 14:02:41

GREAT ITS WORKING FINE

THANKS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 14:03:30

quote:
Originally posted by Peso
....
LEFT JOIN @Sample AS s2 ON ',' + s1.Blocked + ',' NOT LIKE '%,' + s2.[UID] + ',%'

Whoa... thats a funny looking JOIN statement I am seeing for the first time..Can you explain a little Peso..It sure does seem to work

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 14:05:21

I changed it to an INNER JOIN.

All I do is searching for all records matching the blocked column. I add leading and trailing comma to ensure first and last value in CSV Added column are fetched to. Then I negate the search by putting a NOT there.

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 14:21:37

hmm...interesting.. very good Peso.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 14:32:02

Thanks.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 14:59:37

Just in case some record is self-referencing...

-- Prepare sample data
DECLARE @Sample TABLE ([UID] VARCHAR(2), UName VARCHAR(2), Blocked VARCHAR(10), Added VARCHAR(2))

INSERT	@Sample
SELECT	'1', 'A', '2,3,4,5', '6' UNION ALL
SELECT	'2', 'B', '1,5', '5' UNION ALL
SELECT	'3', 'C', '1,2,5,6', '4' UNION ALL
SELECT	'4', 'D', '1,3,4,5', '3'

-- Show the expected output
SELECT		s2.[UID],
		s2.UName,
		s2.Blocked,
		s2.Added
FROM		@Sample AS s2
INNER JOIN	@Sample AS s1 ON ',' + s1.Blocked + ',' NOT LIKE '%,' + s2.[UID] + ',%'
WHERE		s1.[UID] = 2
		AND ',' + s2.Blocked + ',' NOT LIKE '%,' + s2.[UID] + ',%'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources