Trying to get my head around this one

I've come across this before in my own work. I call it a full inner join: you basically want to inner join across column 1 (so ignoring any non-matches) but full join across column 2 (showing matches from both sides) and can't remember now how I did it.

I feel like there is some simple combination of full join and where that would work, but my head is just not working

Best I can come up with so far is to use conditional window counts over both sides dbfiddle.uk/bTg2zXP8

dbfiddle.uk/LJtpX99J

@Charlieface Word

@Charlieface Is OP essentially just looking for the rows where the tables match on id and indicator but not on value OR the rows don't match by id and indicator?

@J.D. No, it seems they also want rows which don't match from table2, plus they want to ignore any rows which have no match at all by id

@J.D. Word what?

Yea I just re-read that and updated my question.

I'm still a little confused on their output example with row id = 13.

They only want that row when their is a match from another row with the same id (in the same table) but different indicator?...and then don't return the matching rows, only the other row that matches by id?

First think of an XOR join: this is basically a full join but excluding exact matches.

The tweak here is that you want to do this per matched set of id, so any set of id in one table for which no rows exist in the other is ignored.

The solution I wrote was very similar to ypercubeᵀᴹ's

@Zikato Almost identical to mine

I'm wondering if we used the same starting word

I'd be surprised, but we'll see

The non-join solution with helpful indexes

@PaulWhite Needs to query each table twice though.

@PaulWhite Looks neat. I'm assuming that Window Aggregate is batch mode?

@Charlieface Yes.

Argh my head really isn't functioning today. Maybe I stayed up late posting last night? Naah can't be

For reference

Well ChatGPT can't solve Wordle yet, confirmed: "Start with a word that has repeated letters: Another strategy is to choose a word that has repeated letters, such as "apple" or "banana.""

Bad starting word advice, banana is 6 letters, and its sentence structure is incorrect by putting the period inside the quotes with banana.

@PaulWhite Incidentally this is an example of what we discussed last week: ordering on a full-joined column. I like your solution, wish there was a better way of doing it though.

If performance isn't an issue, I'd probably go with option #1 with all its joins. The logic is more comprehensible.

The simple full join would be enough without that somewhat odd extra restriction.

Anyway.

Pretty, but frustrating

Was quite confident I had it in two

@PaulWhite dba.stackexchange.com/a/325195/220697

Does it matter if the foreign key drop and create is outside the transaction?

Also why the CATCH ROLLBACK THROW why not just SET XACT_ABORT ON which does that all for you?

@Charlieface It's largely a template and placeholder for anything specific the coder wants to do in response to an error condition. It's not a core part of the answer.

Not a big fan of Microsoft's version learn.microsoft.com/en-us/sql/t-sql/language-elements/… I've seen s many versions of that garbage swallow errors and mess up client-side data readers that I've gone completely off it except very specific cases.

My code is almost exclusively CRUD and reporting queries, so a simple XACT_ABORT plus a client-side catch works perfectly.

Obviously if you're using T-SQL as your only scripting language (seems popular on here) then you're going to have problems without error-handling.

I suppose you could always use Erland's error procedures

But I don't really care what people do with their error handling so long as they have some

Hmmm well I do. What I'm trying to say is: T-SQL error handling is so poor that it's usually better not to have it at all, and only rely on XACT_ABORT, instead catching and handling everything client side. If you are doing DBA scripts run from SSMS directly then yeah it's a bit different.

I suspect removing the identity property would be a DBA task, yes

@Charlieface What do you mean specifically? Dropping and recreating the FK is core to the switching. The whole thing needs to succeed or fail as a unit.

Are you asking about the locking? As I recall, adding a FK takes and holds Sch-M on source and target for the duration of the operation.

Wow Wordle was brutal today

And now:

Everything's a bit wobbly, even chat

Stack Overflow is offline

@PaulWhite True but it's not the end of the world if the foreign key isn't present for a short while. You can drop and create it outside of a transaction, and rely on the application to sort it out. It doesn't fundamentally change anything that the application needs to know about up to the minute

Well, you'd be upset if creating the FK runs for a long time then fails because someone added invalid data while the FK was missing. Rely on the application to sort it out LOL.

And yes, their question isn't about removing identity. It's a side point.

What a silly status page

@HannahVernon It is a bit, but to be fair, the messages say:

> If this turns into an extended outage, we will post details on the status page.

Seems to be back now

i am also back now

🥷💥💨😶‍🌫️

@PaulWhite I’m glad you were impressed

I'm sure he'll be impressed that you were glad