Avatar of Member_2_7966113
Member_2_7966113
 asked on

SQL Query Modification

Hello Experts,

Can someone help modify a SQL script.

The following script will compare columns A B C from TableA and TableB and if it sees a match will output the rows as shown in the following image.
output
select convert(VARCHAR,0) Is_Deleted, A.A, A.B, A.C, A.D, A.E, A.F
from TableA A
inner join TableB B on B.A = A.A and B.B = A.B and B.C = A.C
union all
select convert(VARCHAR,1) Is_Deleted, B.A, B.B, B.C, B.D, B.E, B.F
from TableB B
inner join TableA A on A.A = B.A and A.B = B.B and A.C = B.C
order by A, B, C

However, I would like the code modified. I still need sql to find a match on columns A B C but only produce the same output if Column B have the same record, of say bb. At moment, the query matching if all Columns are the same. So because there is a match of xx PP dd the query is providing an output, but I only need the output if the columns were xx bb and dd.

So I only want an output if TableA and TableB look like the following,

TableA
ta
TableB
tb
As you can see there is a match on xx bb dd

Please let me know if you need further information
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ste5an

I don't really get it, but this is also due to missing table DDL and sample data to test it.

But it sounds like you should look into EXCEPT and INTERSECT instead of UNION as set operators.
Member_2_7966113

ASKER
Hi ste5an,

Thanks for reaching out.

Please find attached the sample data to test it.
TableA.csv
Member_2_7966113

ASKER
I forgot table b
TableB.csv
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Member_2_7966113

ASKER
The expected results should look like the following:

expected
Member_2_7966113

ASKER
please, please let me know if you need further information
Member_2_7966113

ASKER
I have reattached the files as .xls.

I hope this helps
TableA.xls
TableB.xls
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Member_2_7966113

ASKER
Portle, thanks for your patience. I'm going to test your suggestion. If it doesn't work, I will try and spell exactly what I would like
Member_2_7966113

ASKER
Portle, that is exactly what I'm looking for - you're a star.

I'm about to post related question, and just so I don't make the same mistake as I did in the way I phrased this question, can you let me know what you didn't understand about this question.
Member_2_7966113

ASKER
Absolutely amazing .... thank you so much
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Member_2_7966113

ASKER
PortlePaul, I know this question is closed but I was wondering if I could ask you a related question to the fabulous answer?

As you the columns from this question appear as:

columns
However, can you let me know what I would need to do to modify the script with the following new column names@

Is_Deleted      FirstName      Lastname      MiddleName      MaidenName      Country
PortletPaul

Well I don't really know what you had in mind when you "converted" to a, b, c, d, e and f
so I can't tell you which one is which

does it work left to right?
a becomes FirstName  
b becomes Lastname    
c becomes MiddleName    
d becomes MaidenName      
e becomes Country
f becomes ???

but honestly how would I know?
Member_2_7966113

ASKER
Very quick question:

Can you let me know what the following code does from the script you provided?

select
            a.a

Open in new window

[b]with cmn as (
    select
            a.a[/b]
    from tableA a
    inner join tableB b on a.a = b.a and a.b = b.b and a.c = b.c
    and a.b = 'bb'
    )
select 0 as is_deleted, a.a, b, c, d, e, f from TableA a inner join cmn on a.a = cmn.a
union all
select 1 as is_deleted, b.a, b, c, d, e, f from TableB b inner join cmn on b.a = cmn.a

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ste5an

See T-SQL Fundamentals from Itizik Ben-Gan.
PortletPaul

a.a

the first "a" is a table alias (shortcut for the table the column comes from), the second "a" is the column name

with cmn as (
...
)

This is creating a "common table expression" which has been named "cmn"

See SQL Server documentation online: WITH common_table_expression

This named common table expression can be treated as if it were a real table for the query beneath it, that is why later the query uses:

... inner join cmn on ...

If you have more basic SQL queries you could start here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/queries?view=sql-server-2017
PortletPaul

In future may I suggest you do not use column names like   a   b   c   ...
 
Instead use real column names, it is actually easier for us as well.

I also suggest using real table names.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23