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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.