SQL Query Modification Request
-
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:
TableATableB
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
-
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 -
On a separate note, your IsDeleted flag should be of datatype BIT.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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
-
Sorry, that is not what the expected results should look like please ignore last post. I will repost
- carlton 84646 - Friday, December 21, 2018 6:01 AMThanks 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)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The expected results should look like the following:
- carlton 84646 - Friday, December 21, 2018 6:09 AMThe 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 ShawFor 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 - 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.
- ChrisM@Work - Friday, December 21, 2018 6:12 AMcarlton 84646 - Friday, December 21, 2018 6:09 AMThe 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
-
Please let me know if those were the files you were after?
- carlton 84646 - Friday, December 21, 2018 6:16 AMChrisM@Work - Friday, December 21, 2018 6:12 AMcarlton 84646 - Friday, December 21, 2018 6:09 AMThe 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 ShawFor 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 -
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - ChrisM@Work - Friday, December 21, 2018 6:20 AMcarlton 84646 - Friday, December 21, 2018 6:16 AMChrisM@Work - Friday, December 21, 2018 6:12 AMcarlton 84646 - Friday, December 21, 2018 6:09 AMThe 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:
TableATableB
Is not possible to look at the two simple tables above and help me from there?
- carlton 84646 - Friday, December 21, 2018 6:28 AMChrisM@Work - Friday, December 21, 2018 6:20 AMcarlton 84646 - Friday, December 21, 2018 6:16 AMChrisM@Work - Friday, December 21, 2018 6:12 AMcarlton 84646 - Friday, December 21, 2018 6:09 AMThe 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:
TableATableB
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 ShawFor 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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply