Match the column values between two tables in SQL Server

Question

I have two tables:

ReservationName

 +-------+-------+-------+-------+ | Name | +-------+-------+-------+-------+ | Brad Pitt | | Morgan Freeman | | Bobby deniro | +-------+-------+-------+-------+

BookingDetails

 +-------+-------+-------+-------+ | Name | ID | Eid | +-------+-------+-------+-------+ | Brad Pitt | 1 | ab123 | | Morgan Freeman | 2 | pq123 | | Bobby deniro | 3 | rs123 | +-------+-------+-------+-------+

I have to match the names in ReservationName with BookingDetails .

If they are same (count and value) .

Above example of mine should return true as names are identical and count is 3. The aforementioned condition is part of If exists logic that I am using in a stored procedure.

You can use joins to match the names. What do you need exactly? — gaganshera
– gaganshera, Commented May 7, 2017 at 10:23
@gaganshera : if exists(Names and count is matched ) then set a scalar val to 1 — Piyush Sing
– Piyush Sing, Commented May 7, 2017 at 10:26
@gaganshera : Apologies for not making things clear. The name column has to be identical. Hope it clarifies. — Piyush Sing
– Piyush Sing, Commented May 7, 2017 at 10:30
Doing a join like SELECT *, count(*) nameCount FROM ReservationName t1 JOIN BookingDetails t2 ON (t1.Name = t2.Name) will return all the matched rows. I don't understand the exact requirement but I think thats what you want. — gaganshera
– gaganshera, Commented May 7, 2017 at 10:33

Gordon Linoff · Accepted Answer · 2017-05-07 11:19:33Z

One method to determine which names are not in both tables is to use full outer join:

select * from ReservationName rn full outer join BookingDetails bd on rn.name = bd.name where rn.name is null or bd.name is null;

You can use this in an if using exists:

if (exists (select 1 from ReservationName rn full outer join BookingDetails bd on rn.name = bd.name where rn.name is null or bd.name is null ) ) begin -- not matching code end;

Note: This version assumes (as in your question) that the names are unique in each table. If this is not the case, then ask another question with more information on how to handle duplicate names. Specifically address the question of what to do if the number of duplicates differs between the tables.

marc_s · Accepted Answer · 2017-05-07 10:48:55Z

As per my understanding, go with below Query:

SELECT COUNT(Name) FROM (SELECT R.Name FROM ReservationName R INNER JOIN BookingDetails B ON R.Name = B.Name) A

Please describe the problem in details if this is not the solution you are looking for.

Piyush Sing · Accepted Answer · 2017-05-07 11:34:22Z

if ( not exists ( SELECT Name FROM ReservationName EXCEPT SELECT Name FROM BookingDetails ) and not exists ( SELECT Name FROM ReservationName except SELECT Name FROM BookingDetails ) ) BEGIN SELECT 1 -- Name and count are matched END ELSE BEGIN SELECT 0 END

ahmed abdelqader · Accepted Answer · 2017-05-07 13:41:21Z

According to your requirements:-

Check count is matched.
Check name is matched.

Use the next IF code:-

IF ( /*check count is matched*/ (SELECT COUNT(Name) FROM ReservationName) = (SELECT COUNT(Name) FROM BookingDetails) AND /*check name is matched*/ (SELECT COUNT(Name) FROM ReservationName where Name NOT IN (SELECT Name FROM BookingDetails)) = 0 ) BEGIN SELECT 1 -- Name and count are matched END ELSE BEGIN SELECT 0 END

Getting this error . Conversion failed when converting the varchar value 'BRAD PITT' to data type int
@PiyushSing, The answer has been updated, waiting ur feedback.
Thanks @ahmed abdelqader : Between I have also suggested one answer couple of hours back and it too is working file.

Collectives™ on Stack Overflow

Match the column values between two tables in SQL Server

4 Answers 4

Comments

Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Related