October 13, 2008 at 5:22 am
Hi!
I have 2 tables:
CREATE TABLE [dbo].[Tab1](
[Cpt] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('2')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('235')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('234')
and
CREATE TABLE [dbo].[Tab2](
[Tel] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO [dbo].[Tab2] ([Tel]) VALUES ('2345')
INSERT INTO [dbo].[Tab2] ([Tel]) VALUES ('2300')
INSERT INTO [dbo].[Tab2] ([Tel]) VALUES ('24868')
The select:
SELECT Tab2.Tel
FROM Tab1 join Tab2 on Tab2.Tel LIKE Tab1.Cpt + '%'
gives:
23456
23456
24566
2368
but i need:
23456
24566
2368
(the value from tab2 that matches but without repetitions)
Thanks
October 13, 2008 at 5:29 am
SELECT distinct #Tab2.Tel
FROM #Tab1 join #Tab2 on #Tab2.Tel LIKE #Tab1.Cpt + '%'
October 13, 2008 at 5:38 am
Sorry, i wanted to simplify the question, but it didn't work :D.
Let try from the beginning:
Hi!
I have 2 tables:
CREATE TABLE [dbo].[Tab1](
[Cpt] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('2')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('235')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('234')
and
CREATE TABLE [dbo].[Tab2](
[Tel] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
[total] [int] NULL)
INSERT INTO [dbo].[Tab2] ([Tel],[total]) VALUES ('2345',1)
INSERT INTO [dbo].[Tab2] ([Tel],[total]) VALUES ('2300',2)
INSERT INTO [dbo].[Tab2] ([Tel],[total]) VALUES ('24868',3)
I need to do a query like this one:
SELECT SUBSTRING(Tab2.Tel, 1, 2) AS tel, COUNT(Tab2.total) AS total
FROM Tab2 join Tab1 on Tab2.Tel LIKE Tab1.Cpt + '%'
GROUP BY SUBSTRING(Tab2.Tel, 1, 2)
the result is :
233
241
i need
232
241
(i need this join "join Tab1 on Tab2.Tel LIKE Tab1.Cpt + '%'" to work like a filter)
Sorry again for the misunderstanding
October 13, 2008 at 5:46 am
I have a solution but i don't like it (my 2 tables are very big)
SELECT SUBSTRING(Tab2.Tel, 1, 2) AS tel, COUNT(Tab2.total) AS total
FROM Tab2
where Tab2.tel in(SELECT DISTINCT Tab2.Tel
FROM Tab1 INNER JOIN
Tab2 ON Tab2.Tel LIKE Tab1.Cpt + '%')
GROUP BY SUBSTRING(Tab2.Tel, 1, 2)
October 13, 2008 at 8:05 am
dana_turcanu1981 (10/13/2008)
Hi!I have 2 tables:
CREATE TABLE [dbo].[Tab1](
[Cpt] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('2')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('235')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('234')
and
CREATE TABLE [dbo].[Tab2](
[Tel] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO [dbo].[Tab2] ([Tel]) VALUES ('2345')
INSERT INTO [dbo].[Tab2] ([Tel]) VALUES ('2300')
INSERT INTO [dbo].[Tab2] ([Tel]) VALUES ('24868')
The select:
SELECT Tab2.Tel
FROM Tab1 join Tab2 on Tab2.Tel LIKE Tab1.Cpt + '%'
gives:
23456
23456
24566
2368
but i need:
23456
24566
2368
(the value from tab2 that matches but without repetitions)
Thanks
Dana
The values of 23456, 24566 and 2368 don't exist in the sample data, and running your own query against your own sample data generates something completely different. This makes it really difficult, impossible even, to figure out what you actually want, and also puts people off from answering your question. Can I suggest you repost with the results you get from your sample data, and give a more comprehensive explanation of your requirements? I'm sure this is a straightforward query.
Extending the sample data set with a few more rows in each table would be exceptionally helpful.
set nocount on
DROP TABLE #Tab1
CREATE TABLE #Tab1 ([Cpt] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO #Tab1 ([Cpt]) VALUES('2')
INSERT INTO #Tab1 ([Cpt]) VALUES('235')
INSERT INTO #Tab1 ([Cpt]) VALUES('234')
DROP TABLE #Tab2
CREATE TABLE #Tab2 (
[Tel] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
[total] [int] NULL)
INSERT INTO #Tab2 ([Tel],[total]) VALUES ('2345',1)
INSERT INTO #Tab2 ([Tel],[total]) VALUES ('2300',2)
INSERT INTO #Tab2 ([Tel],[total]) VALUES ('24868',3)
SELECT #Tab1.Cpt, #Tab2.Tel
FROM #Tab1
INNER JOIN #Tab2 ON #Tab2.Tel LIKE #Tab1.Cpt + '%'
Results:
Cpt Tel
--- -----
22345
22300
224868
234 2345
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2008 at 12:36 am
set nocount on
DROP TABLE #Tab1
CREATE TABLE #Tab1 ([Cpt] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO #Tab1 ([Cpt]) VALUES('2')
INSERT INTO #Tab1 ([Cpt]) VALUES('235')
INSERT INTO #Tab1 ([Cpt]) VALUES('234')
INSERT INTO #Tab1 ([Cpt]) VALUES('4')
INSERT INTO #Tab1 ([Cpt]) VALUES('44')
DROP TABLE #Tab2
CREATE TABLE #Tab2 (
[Tel] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO #Tab2 ([Tel]) VALUES ('2345')
INSERT INTO #Tab2 ([Tel]) VALUES ('2300')
INSERT INTO #Tab2 ([Tel]) VALUES ('24868')
INSERT INTO #Tab2 ([Tel]) VALUES ('35448')
INSERT INTO #Tab2 ([Tel]) VALUES ('5638')
INSERT INTO #Tab2 ([Tel]) VALUES ('66638')
INSERT INTO #Tab2 ([Tel]) VALUES ('4568')
INSERT INTO #Tab2 ([Tel]) VALUES ('4444')
INSERT INTO #Tab2 ([Tel]) VALUES ('4432')
SELECT SUBSTRING(#Tab2.Tel, 1, 2) AS tel, COUNT(*) AS total
FROM #Tab2 join #Tab1 on #Tab2.Tel LIKE #Tab1.Cpt + '%'
GROUP BY SUBSTRING(#Tab2.Tel, 1, 2)
Result:
233
241
444
451
Need
232 (because there are only 2 Tel beginning with 23: 2345 and 2300)
241
442
451
I need Tab1 not for creating combinations between Cpt and Tel, but to make a filter and choose those Tel that matches "Tab2.Tel LIKE #Tab1.Cpt + '%'"
October 16, 2008 at 9:08 am
The solution is easy - use COUNT with DISTINCT:
DECLARE @Tab1 TABLE (
Cpt varchar(50) COLLATE Cyrillic_General_CI_AS NULL
)
INSERT INTO @Tab1 (Cpt) VALUES('2')
INSERT INTO @Tab1 (Cpt) VALUES('235')
INSERT INTO @Tab1 (Cpt) VALUES('234')
INSERT INTO @Tab1 (Cpt) VALUES('4')
INSERT INTO @Tab1 (Cpt) VALUES('44')
DECLARE @Tab2 TABLE (
Tel varchar(50) COLLATE Cyrillic_General_CI_AS NULL
)
INSERT INTO @Tab2 (Tel) VALUES ('2345')
INSERT INTO @Tab2 (Tel) VALUES ('2300')
INSERT INTO @Tab2 (Tel) VALUES ('24868')
INSERT INTO @Tab2 (Tel) VALUES ('35448')
INSERT INTO @Tab2 (Tel) VALUES ('5638')
INSERT INTO @Tab2 (Tel) VALUES ('66638')
INSERT INTO @Tab2 (Tel) VALUES ('4568')
INSERT INTO @Tab2 (Tel) VALUES ('4444')
INSERT INTO @Tab2 (Tel) VALUES ('4432')
SELECT SUBSTRING(Tel, 1, 2) AS tel, COUNT(DISTINCT Tel) AS total
FROM @Tab2 AS T2 join @Tab1 AS T1 on T2.Tel LIKE T1.Cpt + '%'
GROUP BY SUBSTRING(Tel, 1, 2)
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply