result from 2 tables - Solved - 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)

result from 2 tables - Solved

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2009-12-14 : 08:23:08

Hello again,

i am having trouble finding a solution for the following problem.

i have 2 tables:

table 1 contains in the first column some names and on the second column some values.

table 2 contains in the first column some names and on the second column some other names.

table_1:

1st_cola 2nd_cola

abc-----1
bcd-----5
cde-----3
def-----6
efg-----9

table_2:

1st_colb 2nd_colb

abc-----xxx1
abc-----xxx2
bht-----xxx1
bht-----xxx2
bht-----xxx3
efg-----xxx2
efg-----xxx4
efg-----xxx1
... and so on.

table_2 contains all the names from table_1 no matter what.

Now, for every name from table_1 i want to view all the names from table 2 wich contains all the structure from table_2.

for example: from table_1 the abc name has defined in table 2 xxx1 and xxx2. the result in the example given has to be :

1st_colc 2nd_colc

abc-----bht
abc-----efg

abc should not be included in the results. also can this be done at the same time for all the names from table_1?

Any help is kindly apreciated. Thanks in advance.

Best wishes.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-14 : 08:31:00

This work?

Select

a.1st_col , b.1st_col from table_1 a inner join table_2 b ON a.2nd_col = right(b.2nd_col, 1)

Where a.1st_Col <> b.1st_col

EDIT: If column 2 in table b can get into double digits you will have to search the string for numeric characters and then match from there. Also if the 2nd column is a string you may have to convert the numeric substring to an integer value, depends on what your columns are defined as.

tempus
Starting Member

47 Posts

Posted - 2009-12-14 : 08:37:42

table_1 : 1st_col is nvarchar (35) and 2nd_col is numeric (18,2)
table_2 : 1st_col is nvarchar (35) and 2nd_col is also nvarchar (35)

table 1 contains a lot of names starting with a and table 2 contains all the possible names starting with a.

hope this helps.

thanks for your answer.

it doesnt seem to work, and ive edited the first post column names for more precise information.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-14 : 09:46:17

Select

a.1st_col , b.1st_col from table_1 a inner join table_2 b ON a.2nd_col = right(b.2nd_col, (1 +len(b.2nd_col) - patindex('%[0-9]%',b.2nd_col))

Where a.1st_Col <> b.1st_col

right(b.2nd_col, (1 +len(b.2nd_col) - patindex('%[0-9]%',b.2nd_col)) Still needs a Convert(Numeric(18,2, ...) on it If I get a free moment Ill look up the syntax.

tempus
Starting Member

47 Posts

Posted - 2009-12-14 : 09:55:30

DP978 thanks for the answers , can you please specify wich 1st_col from what table? its a bit confusing (my mistake because of the example). i have modified in the 1st post the names of the columns from the 1st and 2nd table for a bit more exact script.

table 1
1st_cola
2nd_cola

table 2
1st_colb
2nd_colb

Thank in advance.

PS: its confusing because a.2nd_col i think its the number column which i don't think it should be, right?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-14 : 09:59:36

quote:
Originally posted by DP978

Select

a.1st_cola , b.1st_colb from table_1 a inner join table_2 b ON a.2nd_cola = right(b.2nd_colb, (1 +len(b.2nd_colb) - patindex('%[0-9]%',b.2nd_colb))

Where a.1st_Cola <> b.1st_colb

That clear anything up?

EDIT: Removed unneccesary Select

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 11:35:02

I think I have a slightly different interpretation of the requirements...

declare @table_1 table ([1st_col] nvarchar(35), [2nd_col] numeric(18,2))
declare @table_2 table ([1st_col] nvarchar(35), [2nd_col] nvarchar(35))

insert @table_1
          select 'abc', 1
union all select 'bcd', 5 
union all select 'cde', 3
union all select 'def', 6
union all select 'efg', 9

insert @table_2
          select 'abc', 'xxx1'
union all select 'abc', 'xxx2'
union all select 'bht', 'xxx1'
union all select 'bht', 'xxx2'
union all select 'bht', 'xxx3'
union all select 'efg', 'xxx2'
union all select 'efg', 'xxx4'
union all select 'efg', 'xxx1'

; with t1 as (
    select a.[1st_col] as x, b.[1st_col] as y, count(*) as Count
    from @table_2 a inner join @table_2 b on a.[2nd_col] = b.[2nd_col]
    group by a.[1st_col], b.[1st_col])
select a.x, a.y from t1 a inner join (select x, Count from t1 where x = y) b on a.x = b.x and a.Count = b.Count and a.x != a.y

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-14 : 12:21:50

Might be, It was early this morning when I read it, I took it as column 2 of Table 1 needed to match the right most number from column 2 of table 2...

Still getting used to the structure of questions and answers on here, plus I am gaining the knowledge day by day, so I definately don't always have the best ways :)

tempus
Starting Member

47 Posts

Posted - 2009-12-15 : 03:04:25

DP978 , that was not what i needed : for every name from table_1 i want to view all the names from table 2 wich contains all the structure from table_2

Thank you very much for your help , i really apreciate for spending time to solve my problem.

also Ryan, first of all thank you for your help. It works as i copy paste your script and run it. The example i first wrote was a simple one, because the actual script is a bit more complicated. when i replace and run it into my script i get endless results wich i don't think its corect.

in the all the script of yours i don't see any match bettwen 1st table and 2nd table.
now i deleted this from your script:

declare @table_1 table ([1st_col] nvarchar(35), [2nd_col] numeric(18,2))

insert @table_1
select 'abc', 1
union all select 'bcd', 5
union all select 'cde', 3
union all select 'def', 6
union all select 'efg', 9

------

and the result is the same without those above, so i understand that you didn't use the 1st table at all. i need the results to be based on the 1st table names.

the whole script looks like this (the tables are allready created):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*

exec [dbo].[btnomovestock]
@data_start = '2009-06-01',
@data_end = '2009-11-30'

*/
ALTER procedure [dbo].[btnomovestock]
@data_start as datetime,
@data_end as datetime
AS
----
delete from BTNOMOVESTOCK
----
INSERT INTO BTNOMOVESTOCK (cod_produs,denumire_produs,q,del1,del2,del3)
SELECT
SC01001 COD_PRODUS,
SC01002 NUME_PRODUS,
--SC07009 DEP,
SUM (CASE WHEN SC07002 < @data_start then SC07004 else 0 end) stoc_articol,
SUM (CASE WHEN SC07002 < @data_start then SC07004*SC07005 else 0 end) VAL_articol,
SUM (CASE WHEN SC07001 = '01' AND SC07004 <> 0 and SC07002 BETWEEN @data_start AND @data_end THEN SC07004 ELSE 0 END) STOC,
SUM (CASE WHEN SC07001 = '01' AND SC07004 <> 0 and SC07002 BETWEEN @data_start AND @data_end THEN SC07004*SC07005 ELSE 0 END) VAL
FROM SC070100, SC010100
WHERE SC07003 = SC01001 AND
SC07009 IN ('10','01','021','031','041') AND
SC07003 LIKE 'PF%'

GROUP BY SC01002, SC01001--, SC07009

having SUM (CASE WHEN SC07001 = '01' AND SC07004 <> 0 and SC07002 BETWEEN @data_start AND @data_end THEN SC07004 ELSE 0 END) =0 AND
SUM (CASE WHEN SC07002 < @data_start then SC07004 else 0 end) <>0

ORDER BY SC01001

--------------------------------------

INSERT INTO BTRETETECLASE (produs, clasa)
select MP61002, MP61005 FROM MP610100 WHERE MP61005 LIKE 'CL%' AND MP61002 NOT LIKE 'R%';

--------------------------------------

with t1 as (

select a.produs as x, b.produs as y, count(*) as Count
from BTRETETECLASE a inner join BTRETETECLASE b on a.clasa = b.clasa
group by a.produs, b.produs)
select a.x, a.y from t1 a inner join (select x, Count from t1 where x = y) b on a.x = b.x and a.Count = b.Count and a.x != a.y

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

so practicaly i only need from the 1st table to match the first column with the 2nd table. ignore all the other columns from the 1st table.

i think were very close and that is thanks to you guys, much apreciated. would be very happy if you could help me to the end also since im a bit traped.

Thanks in advance, and please excuse my late response.

tempus
Starting Member

47 Posts

Posted - 2009-12-16 : 04:01:46

Dear Ryan ,

i think i got it,

your script :

with t1 as (
select a.[1st_col] as x, b.[1st_col] as y, count(*) as Count
from @table_2 a inner join @table_2 b on a.[2nd_col] = b.[2nd_col]
group by a.[1st_col], b.[1st_col])
select a.x, a.y from t1 a inner join (select x, Count from t1 where x = y) b on a.x = b.x and a.Count = b.Count and a.x != a.y

works as intended , the only thing i did was a aditional update in the second table on a 3rd column with all the names from table 1. so i modified the columns in your script and now its works exactly as i wanted to.

thank you very much for your help.

Best wishes to everyone and happy holidays.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-16 : 05:41:44

Thanks for the feedback, tempus

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources