Looking for a specific SQL query

Working with SQL Server (Microsoft)
I have a table T1and a table T2
Table T1 contains rows with a column A1 and a column A2 (and other columns also, PK etc...)
Table T1 contains rows with a column B1 and a column B2 (B1 is the primary key)
Column A1 is foreign on T1, column B1
Columns A2 and B2 have the same "meaning", a decimal(3,1) value
There are many rows in T1 with same value for column A1
I want to know with one SQL select, if there are some rows in T1 for which the "corresponding B2 column in T2, joined via the column A1 to A2, that have a different A2 value than the B2

Is this possible in one single select ?
LVL 1
LeTayAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should be possible, yes, but it's not 100% clear from your explanation

see if this is a starter for you:
select *
  from table1 t1
  join table2 t2
     on t1.A1 = t2.b1
 where t1.A2 <> t2.B2

if not, please show with data samples what you are looking for
0
 
LeTayAuthor Commented:
Looks okay ....
Will try and tell you
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Try:
SELECT tt.B1, A.A1, tt.B2, A.A2
FROM #t2 AS tt
OUTER APPLY (SELECT pk1, A1, A2
FROM #T1 AS t
WHERE tt.B1 = t.A1 AND tt.B2 <> t.A2) As A

-- ----------based on the following data ----
create table #T1(PK1 int identity, A1 int, A2 decimal(3,1))
create table #T2(B1 int identity, B2 decimal(3,1))  
drop table #t2
insert #T2(B2) values
(1.2)
, (1.3)
, (1.4)
, (1.9)

insert #T1(A1, A2) values
(1, 1.2)
, (1, 1.1)
, (1, 1.4)
, (1, 1.5)
, (2, 1.2)
, (2, 1.3)
, (3, 1.4)
, (3, 1.5)

select * from #T1;
select * from #T2;

Open in new window


Produces:
B1      A1     B2      A2
1	1	1.2	1.1
1	1	1.2	1.4
1	1	1.2	1.5
2	2	1.3	1.2
3	3	1.4	1.5
4	NULL	1.9	NULL

Open in new window


Note: If T2 has a record like the last row above and it is missing in T1, it identifies as NULL (This is considered mismatch as well).

Mike
0
 
LeTayAuthor Commented:
Didn't know that syntax (or forgot !)
Many thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.