Anyone know how to do a join where one table will have a single record and the other table will have multiple records, and then to combine the multiple records? E.g., I have a table called Users (name, id, etc) and a table called UserTags (userid, tagid). I want to join Users and UserTags and get a single object where the various tagids (a user can have multiple tags) are returned in the join such that I can check if the any of those tagids match a list of tagids

E.g. something like this $"SELECT DISTINCT Users.UserId FROM Users INNER JOIN UserTags ON Users.UserId = UserTags.UserId WHERE UserTags.TagId IN ({tagParameters})"; (this is in C# so the parameters get added in the next line but they will be a list of the tags Im checking against)

Essentially I just want to get a list of all the userIds who have a tag that matches with some list of tags

@ROODAY Please consider following these suggestions and ask your question on the main site. Here it's just good morning and cricket chirps

Ah sounds good, will do!

Morning

Morning

A chairde - Morning all!

@Vérace-getVACCINATEDNOW the OP said:

> EDIT: Here "last" means with the highest version corresponding to non-null value.

So you can use MAX() for the A (non-null) column but I don't think the use in columns B and C qualify as correct.

Not that I like the question. Too little details

Or maybe the OP has a specific version non-nullable column (as per latest edit) as that should be used for ordering / defining "last" in other columns.

@ypercubeᵀᴹ I did include a COALESCE option - ('--') if all columns are NULL, it really doesn't matter what string is there - even the empty one - maybe I should have put more emphasis on the NULL issue?

My point is that using MAX is wrong.

because for example column B may have values: 1-null-3-2-null.

Then the "last" would be 2, not 3.

@ypercubeᵀᴹ - I see your point. NULLs are tricky little b**&%s! :-) Maybe we all should have listened to Date? Last in this case is indeed not MAX() - I'll put this into my answer!

@ypercubeᵀᴹ - the question definitely implies an ORDER in the columns (going "down") - the last entry being the MAX() - the OP's edit EDIT: Here "last" means with the highest version corresponding to non-null value. - so, as I said to mustaccio, I believe that I've answered it as asked! :-)

But I'll mention it!

I agree that the OP implies some order.

But he seems to imply that the same order be used to find the last non-null in each column.

@Vérace-getVACCINATEDNOW I believe this is what they want

"last non-null value in each column when the table is ordered by column version."

Your repeated mention of COALESCE() lead me to believe that you had a few Irish coffees today ;)

@ypercubeᵀᴹ - no such luck re. Irish Coffees! :-( Maybe I'm just flailing about trying to compensate for the fact that my answer was way too complex - rabbit holes and all that! I was working on another problem which is far more complex. :-)

It's this one - I believe that the accepted answer is far too simple - what happens if multiple decimal points have been added for example? And or other text? As I'm going to say in my answer (manuscript in preparation), if you allow users to f**$ up, they'll make a really good job of it! I'll post the link here if you're interested?

Manuscript in press - should be out in 1hr or so...

select
    (select a from tab where a is not null order by version desc limit 1) as a,
    (select b from tab where b is not null order by version desc limit 1) as b,
    (select c from tab where c is not null order by version desc limit 1) as c ;

or something like the above for the (previous) q in discussion

I know you prefer CTEs ;)

@Vérace-getVACCINATEDNOW I suppose they answered with the assumption that the data is similar to the provided example. Indeed, if there might be multiple decimal points, it would need more complicated answer.

@ypercubeᵀᴹ I'm working on it - it's a tricky problem... why does PostgreSQL not support backreferences in regexes? I would have thought that there'd be a suitable library they could cannibalise?

regexes is something I avoid

@ypercubeᵀᴹ If regexes were better - it would be a cinch - or at least, easier! Why avoid them - they're like a Swiss-Army knife for strings? But unbelievably contorted and arcane - a nerd's wet-dream! Am I the only one who thinks that adding all of this string-manipulation functionality to databases has/is only going to lead to shittier and shittier database designs? :-)

regexes, triggers, nulls, .... We had a list some years ago, similar to Dante's Inferno. The 9 circles of SQL Hell ;)

@Vérace-getVACCINATEDNOW feel free to add my suggestion in your answer by the way.

This is implied whenever I post here or comment.

@ypercubeᵀᴹ grand - will do when I have a moment, thanks! Yes, I know you like posting by proxy! :-)

@EvanCarroll - I'll be benchmarking tomorrow - I'll keep you posted!

@Vérace-getVACCINATEDNOW you could use it as your own answer

@McNets Gràcies, but no, thank you! I learnt heaps from that code, but I'm not going to post stuff that I haven't written myself - small snippets with acknowledgement yes, but not an entirely new answer. I'm happy, anyway I'm not too interested in StackOverflow - too many smelly developers! :-)

@Vérace-getVACCINATEDNOW you teach what you learn, no matter where, no matter how

@McNets Tired - just about to have dinner - I'll be signing off for the day... bona nit a tots! :-)