CODEWITHSUNDEEP
sqlsql-serverjoin
Piyush Sing
Piyush Sing

Reputation: 45

Match the column values between two tables in SQL Server

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.

Upvotes: 0

Views: 161

Answers (4)

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

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

Upvotes: 0

Piyush Sing
Piyush Sing

Reputation: 45

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 1

ROY
ROY

Reputation: 73

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.

Upvotes: 0

Related Questions