Join using Alias with no ID column

1

Hello I have 2 tables One Eligibility and one Hospital. The Eligibility table has the last name , first name in separate columns and Hospital has them both in one column with a comma in between. I want to join if the names are equal to each other.

This is failing

Select H.Name,M.MemberLastname + ','+ M.MemberFirstName as Names
from Hospital h inner join Eligibility M on H.name = Names

Please help

2

You need to do the concatenation in the criteria as the column "Names" has not been defined (like in Access).

SELECT H.Name....
FROM Hospital H 
INNER JOIN Eligibility M ON H.Name = M.MemberLastname + ',' + M.MemberFirstName;
1 Like
3

You cannot join on the column name in that manner - you can use a derived table or CTE...or you put the calculation in the join expression:

SELECT ...
  FROM Hospital h
 INNER JOIN (SELECT *
                  , m1.MemberLastName + ',' + m.MemberFirstName As Names
               FROM Eligibility m1) m ON m.Names = h.name

SELECT ...
  FROM Hospital h
 INNER JOIN Eligibility m ON m.MemberLastName + ',' + m.MemberfirstName = h.name
4

Try something like:

CREATE TABLE NamesWithComma(Names varchar(61));
GO
INSERT dbo.NamesWithComma
        (Names)
    VALUES
        ( 'Jones, Robert' )
      , ( 'James, Jesse' );
CREATE TABLE NamesInSeperateColumns(FistName varchar(30), LastName varchar(30));
GO
INSERT dbo.NamesInSeperateColumns
        (FistName, LastName)
    VALUES
        ( 'Robert' , 'Jones' )        
      , ( 'Jesse' ,  'James' );
SELECT
     nisc.FistName
   , nisc.LastName
FROM 
   dbo.NamesInSeperateColumns nisc
JOIN dbo.NamesWithComma nwc
   ON nwc.Names LIKE '%'+nisc.FistName
   AND nwc.Names LIKE nisc.LastName+'%';
DROP TABLE dbo.NamesInSeperateColumns;
GO
DROP TABLE dbo.NamesWithComma;
GO
5

Thank you.

6

Couple of things to be aware of IME

JOINing on a concatenated string will run like a dog :frowning: If the number of rows you have is small it won't be a problem ... unless the number of rows will grow/scale (over time).

Its fragile. Are the names really maintained absolutely identically in both tables? No ambiguity with "John Smith" etc?

I always prefer to see some sort of ID in these cases, so that the Name itself is not the key. Typos get fixed, People change name on marriage and so on ... all sorts of pitfalls.

Hopefully none of that applies in your case, but just thought I'd raise it ... just in case :slight_smile: