Duplicate query - 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)

Duplicate query

Author

Topic

shohelic
Starting Member

6 Posts

Posted - 2007-01-24 : 01:01:49

I have a table:
----------------
Code
----------------
DHMOT1
DHMOT2
DHMOT3
DHMOT3_18
JSTWN1
JSTWN2
JSTWN2_18
JSTWN3
JSTWN4
BSTWN1_18
BSTWN2
BSTWN3
---------------
Now I need to know the codes which have "_18" and also have one without "_18"

The result will be like:
----------------
Code
----------------
DHMOT3_18
JSTWN2_18
---------------

Can anyone help me pls.

-Shohel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 01:10:03

select * from yourtable where code like '%_18'

Peter Larsson
Helsingborg, Sweden

shohelic
Starting Member

6 Posts

Posted - 2007-01-24 : 02:10:24

quote:
Originally posted by Peso

select * from yourtable where code like '%_18'

Peter Larsson
Helsingborg, Sweden

No Peter, I know this query but look carefully it have DHMOT3,DHMOT3_18(one have _18 and another not have). I want to fatch those data. If I use your syntex then it also return BSTWN1_18. But it is not valid, because it don't have any code without _18.

Could I make it clear?

-Shohel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 02:17:08

[code]-- prepare sample data
declare @t table (code varchar(20))

insert @t
select 'DHMOT1' union all
select 'DHMOT2' union all
select 'DHMOT3' union all
select 'DHMOT3_18' union all
select 'JSTWN1' union all
select 'JSTWN2' union all
select 'JSTWN2_18' union all
select 'JSTWN3' union all
select 'JSTWN4' union all
select 'BSTWN1_18' union all
select 'BSTWN2' union all
select 'BSTWN3'

-- show the result
SELECT t.Code
FROM @t AS t
INNER JOIN (
SELECT LEFT(Code, 6) AS Code
FROM @t
GROUP BY LEFT(Code, 6)
HAVING COUNT(*) > 1
) AS d ON t.Code LIKE d.Code + '_%'
ORDER BY t.Code[/code]

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-24 : 02:58:12

Or may be somthing like this ???


Select t.Code From 
(
	Select Code From @T 
	Where code Like '%[_]%'
) As t 
Inner Join 
(
	Select Code From @T 
	Where code Not Like '%[_]%'
) as f
On 
t.Code Like '%' + f.Code + '%'

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 03:03:32

Nice!
You could even drop the first %, like this

t.Code Like ~~'%' +~~ f.Code + '%'

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-24 : 03:16:30

quote:
Originally posted by Peso

Nice!
You could even drop the first %, like this

t.Code Like ~~'%' +~~ f.Code + '%'

Peter Larsson
Helsingborg, Sweden

Oh yeah..

Chirag

http://chirikworld.blogspot.com/

shohelic
Starting Member

6 Posts

Posted - 2007-01-24 : 04:46:26

Thanks,
Thanks everybody for your cooperation.

-Shohel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources