0

I would like to know how to compare two database table records and return the results into separate table.

For example, I have a total of 6 columns in each table A B C D E F. However, I'm only comparing on the records in columns A, B, & C

If there is a match in in each table of columns A B C I would like the results to be outputted to new table (.csv file etc) for the row that matched

Let me illustrate:

In Table A you can see 6 columns, A B C D E F. The table has the following rows:

Table A

Table A

Table B also has 6 columns A B C D E F

Table B

From the tables you can see that only one row has match on A B & C i.e xx, PP, dd.

I would like that row to be outputted to a table with all the records in that row ie. xx PP dd TYH ADF AD

Improve this question
2
  • 1
    Do your tables have UNIQUE constraints on (A, B, C), or there can be multiple rows with the same combination? Commented Dec 20, 2018 at 21:12
  • Please add your expected output data for the given input data. Commented Dec 21, 2018 at 0:14

2 Answers 2

Reset to default
2

Far from being complicated this is SQL bread and butter... you are looking for a join condition e.g.

select A.*, B.*
from TableA A
inner join TableB B on A.A = B.A and A.B = B.B and A.C = B.C

where A.* is all the columns from table A (TableA) and B.* is all the columns from table B (TableB)

Edit: Based on your clarified requirement for a separate row from tables A & B, try the following:

select convert(bit,1) 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(bit,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
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

@TheVillageIdiot, I forgot to mention that I would like the output from both table 1 and table 2.
@Dale, I thought I need to do a UNION, or DISTINCT .. but then again I'm a newbie
@DaleBurrell, your query is closer to what I need. However, I would need second results on the row immediately below. At the moment the second results in on the same row but with columns A1, B1, C1, D1, E1, F1. I would like the second results on row immediately below.
Also, can you let me know what select A.*,B.* does?
@DaleBurrell, Im smiling from ear-to-ear. Thanks so much. I just need one other thing added to each row, but I'm just thinking how to phrase the question.
|
2

Not clear if you want the output from table 1 or table 2. Though the query will be quite simple.

SELECT t.A, t.B, t.C, t.D, t.E, t.F
FROM  TableA t
INNER JOIN TableB tt ON (t.A = tt.A AND t.B = tt.B AND t.C = tt.C)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.