January 26, 2015 at 3:44 pm
Hello,
I need to create a SQL statement with the evaluation of two options with the following logic:
IF TABLEAVALUEA <> TABLEBVALUEA
{select TABLEAVALUEA,TABLEBVALUEA,TABLEBVALUEC,
from TABLEA
join TABLEB on TABLEAVALUEA = TABLEBVALUEA
[ELSE
{DONT DO ANYTHING } ]
what will be the best approach using t-sql commands?
Thank, you
January 26, 2015 at 5:00 pm
Your question doesn't make any sense.
If you have 10 records in TABLEA and 10 in TABLEB then what records do you want to compare by this condition "TABLEAVALUEA <> TABLEBVALUEA" ?
Also, do you want to see records when VALUEAs are equal or not equal?
"IF TABLEAVALUEA <> TABLEBVALUEA"
"on TABLEAVALUEA = TABLEBVALUEA"
January 26, 2015 at 8:29 pm
montserrat.deza (1/26/2015)
Hello,I need to create a SQL statement with the evaluation of two options with the following logic:
IF TABLEAVALUEA <> TABLEBVALUEA
{select TABLEAVALUEA,TABLEBVALUEA,TABLEBVALUEC,
from TABLEA
join TABLEB on TABLEAVALUEA = TABLEBVALUEA
[ELSE
{DONT DO ANYTHING } ]
what will be the best approach using t-sql commands?
Thank, you
I think you're describing something that can't happen. Your join requires that the values be equal and you're wanting to take action when those equal value are not equal.
January 27, 2015 at 5:15 am
Brian Hibbert (1/26/2015)
montserrat.deza (1/26/2015)
Hello,I need to create a SQL statement with the evaluation of two options with the following logic:
IF TABLEAVALUEA <> TABLEBVALUEA
{select TABLEAVALUEA,TABLEBVALUEA,TABLEBVALUEC,
from TABLEA
join TABLEB on TABLEAVALUEA = TABLEBVALUEA
[ELSE
{DONT DO ANYTHING } ]
what will be the best approach using t-sql commands?
Thank, you
I think you're describing something that can't happen. Your join requires that the values be equal and you're wanting to take action when those equal value are not equal.
I should have asked, what are you trying to accomplish? If you are trying to correct a case where all rows in table B should have a matching row in table A (or vice versa) then you should probably set a foreign key on the table and enforce it to prevent the occurrence of the error.
If you're looking for cases where table A doesn't have an entry in table B you could do something like:
SELECT TABLEAVALUEA
FROM TABLEA
WHERE TABLEAVALUEA NOT IN (SELECT TABLEBVALUEA FROM TABLEB)
(Or the opposite if you're looking for TABLEB values that aren't in TABLEA)
Then take action on those records. You can't match them in code with TABLEB values because the relationship TABLEAVALUEA = TABLEBVALUEB doesn't exist for those records so there is no join that makes sense (unless there is a relationship for other columns). You may be able to match them manually and set the value with appropriate UPDATE statements, but that depends upon what other columns are available and how much other interrelation there is between the tables.
If this is, as I suspect, a case where there is a missing relationship between tables, then you will begin to understand why many DBAs will cringe when they hear someone say, "The application will enforce the table relationships to improve performance. We don't need foreign keys."
January 27, 2015 at 6:50 am
As already pointed out your logic is flawed
e.g. One interpretation of the flawed logic is to find all combinations of VALUEA in both tables where they do not match
SELECT TABLEAVALUEA,TABLEBVALUEA,TABLEBVALUE
FROM TABLEA
CROSS JOIN TABLEB
WHERE TABLEAVALUEA <> TABLEBVALUEA
To get a good answer you need to post DDL to create tables and populate them with sample data and what the expect output should look like
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2015 at 1:59 pm
Thank you so much!! It worked fine
🙂
January 27, 2015 at 2:26 pm
David Burrows (1/27/2015)
As already pointed out your logic is flawede.g. One interpretation of the flawed logic is to find all combinations of VALUEA in both tables where they do not match
SELECT TABLEAVALUEA,TABLEBVALUEA,TABLEBVALUE
FROM TABLEA
CROSS JOIN TABLEB
WHERE TABLEAVALUEA <> TABLEBVALUEA
To get a good answer you need to post DDL to create tables and populate them with sample data and what the expect output should look like
That could be written as an inner join.
SELECT TABLEAVALUEA,TABLEBVALUEA,TABLEBVALUE
FROM TABLEA
JOIN TABLEB ON TABLEAVALUEA <> TABLEBVALUEA
January 27, 2015 at 2:29 pm
Luis I tested it and it worked!! thank you for your recommendation but I
did it already.
January 27, 2015 at 2:39 pm
montserrat.deza (1/27/2015)
Luis I tested it and it worked!! thank you for your recommendation but Idid it already.
Internally, it makes no difference. Both queries should create the same execution plan.
I just wanted to add it as an FYI. 😉
February 8, 2015 at 8:02 pm
Your join requires that the values be equal and you're wanting to take action when those equal value are not equal.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply