SQL Query Modification Request

Question

SQL Query Modification Request

carlton 84646

SSC Eights!

Points: 902
More actions
December 21, 2018 at 5:01 am

#373360

Hello Community,
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.
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 an 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 the above query to produce a result if TableA and TableB, look like the following:
TableA
TableB
As you can see there is not only a match on ABC, but also on B
Please let me know if you need further information
Cheers

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99009 More actions · Answer 1

Not sure I really understand your goal here I'm afraid. Sample data (as text) and your expected results will be really helpful here.Could you try to elaborate on your logic for your new query as well please?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

On a separate note, your IsDeleted flag should be of datatype BIT.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 3

Thanks for reaching out guys.
Sorry for the poorly worded question.
The expected result should look like the following:

The expected results DO NOT look like this

carlton 84646 SSC Eights! Points: 902 More actions · Answer 4

Sorry, that is not what the expected results should look like please ignore last post. I will repost

Phil Parkin SSC Guru Points: 245813 More actions · Answer 5

carlton 84646 - Friday, December 21, 2018 6:01 AM
Thanks for reaching out guys.
Sorry for the poorly worded question.
The expected result should look like the following:
Phil, thanks for pointing the Is_Deleted error. I will change it to bit

Please can you provide setup scripts for table A and table B (structure and data population)?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 6

carlton 84646

SSC Eights!

Points: 902

December 21, 2018 at 6:09 am

#2017048

The expected results should look like the following:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

carlton 84646 - Friday, December 21, 2018 6:09 AM
The expected results should look like the following:

This will only take moments to do, if you can first post up the scripts to create and populate TableA and TableB.
Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

carlton 84646 SSC Eights! Points: 902 More actions · Answer 8

Hi Phil, thanks for getting in touch again. The above are just taken from two .csv files called TableA and TableB and imported into a SQL database. The sql script I provided is applied to the databases and the expected results are as described above. There really isn't any structure or data population as such.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 9

ChrisM@Work - Friday, December 21, 2018 6:12 AM
carlton 84646 - Friday, December 21, 2018 6:09 AM
The expected results should look like the following:
This will only take moments to do, if you can first post up the scripts to create and populate TableA and TableB.
Thanks.

Hi, I think this is what you're after

carlton 84646 SSC Eights! Points: 902 More actions · Answer 10

Please let me know if those were the files you were after?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

carlton 84646 - Friday, December 21, 2018 6:16 AM
ChrisM@Work - Friday, December 21, 2018 6:12 AM
carlton 84646 - Friday, December 21, 2018 6:09 AM
The expected results should look like the following:
This will only take moments to do, if you can first post up the scripts to create and populate TableA and TableB.
Thanks.
Hi, I think this is what you're after

No. Many of us won't open Excel attachments, as they represent a security risk.
I'm looking for CREATE TABLE statements, followed by INSERT statements to populate the tables with the sample data.
It has to be done in order to test that the query works. It provides us with data to run a query against.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 245813 More actions · Answer 12

Take a look at SGMunson's response on this page – the structure, not the content.

That will give you an idea of how to provide a setup script.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 13

ChrisM@Work - Friday, December 21, 2018 6:20 AM
carlton 84646 - Friday, December 21, 2018 6:16 AM
ChrisM@Work - Friday, December 21, 2018 6:12 AM
carlton 84646 - Friday, December 21, 2018 6:09 AM
The expected results should look like the following:
This will only take moments to do, if you can first post up the scripts to create and populate TableA and TableB.
Thanks.
Hi, I think this is what you're after
No. Many of us won't open Excel attachments, as they represent a security risk.
I'm looking for CREATE TABLE statements, followed by INSERT statements to populate the tables with the sample data.
It has to be done in order to test that the query works. It provides us with data to run a query against.

OK, I'm going to totally honest with you I don't know how to do as you suggested. I was just hoping that you would be able to view the two Tables I posted at the start of this thread and help compile a query from that. For convenience the two tables appear as follows:
TableA

TableB

Is not possible to look at the two simple tables above and help me from there?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

carlton 84646 - Friday, December 21, 2018 6:28 AM
ChrisM@Work - Friday, December 21, 2018 6:20 AM
carlton 84646 - Friday, December 21, 2018 6:16 AM
ChrisM@Work - Friday, December 21, 2018 6:12 AM
carlton 84646 - Friday, December 21, 2018 6:09 AM
The expected results should look like the following:
This will only take moments to do, if you can first post up the scripts to create and populate TableA and TableB.
Thanks.
Hi, I think this is what you're after
No. Many of us won't open Excel attachments, as they represent a security risk.
I'm looking for CREATE TABLE statements, followed by INSERT statements to populate the tables with the sample data.
It has to be done in order to test that the query works. It provides us with data to run a query against.
OK, I'm going to totally honest with you I don't know how to do as you suggested. I was just hoping that you would be able to view the two Tables I posted at the start of this thread and help compile a query from that. For convenience the two tables appear as follows:
TableA
TableB
Is not possible to look at the two simple tables above and help me from there?

CREATE TABLE TableA (A VARCHAR(2), B VARCHAR(3), C VARCHAR(3), D VARCHAR(3), E VARCHAR(3), F VARCHAR(3))
INSERT INTO A (A, B, C, D, E, F) VALUES ('xx', 'bb', 'cc'....),('AD', 'ADF', ...), and so on.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden