CODEWITHSUNDEEP
sql-serverouter-join
Swaz
Swaz

Reputation: 1

Matching two columns with common values and fetching similar values

Probably a very basic question. I have two tables(SQL Server 2012) Events and User Details. First table is having a column which has user logins say 'xyz'. Second one has a column having user logins 'xyz', 'xyz-a', 'xyz-b' etc all with same User IDs.

If the user logins with suffix are present/absent in the second table, some flags have to be set accordingly. How can it be fetched?

Much appreciate any help. Thanks a lot !!

Upvotes: 0

Views: 70

Answers (2)

Arleigh Reyna
Arleigh Reyna

Reputation: 369

If the "-" is always the delimiter in the second table you can do below. The case statement first looks at if the "-" is present in the string, it looks only to the left of the dash, if the dash does not exist then it look at the entire login.

SELECT *
FROM
    EventsTable E
    JOIN UserDetailsTable U
        ON  E.LoginID = CASE WHEN CHARINDEX('-',U.LoginID) > 0 THEN LEFT(U.LoginID,CHARINDEX('-',U.LoginID)-1) ELSE U.LoginID END

Upvotes: 0

sandeep rawat
sandeep rawat

Reputation: 4957

You can use like in join condition and CONCAT

    SELECT * FROM Events a INNER JOIN User_Details B ON a.userlogins  LIKE CONCAT( B.username, '%');

Upvotes: 1

Related Questions