CODEWITHSUNDEEP
mysqljoin
Jackson
Jackson

Reputation: 6851

MySQL Join Query for With Multiple Columns

I have a question that I think is simple enough but I seem to be having some trouble with it.

I have two tables. Each table has the exact same rows.

I am trying to perform a join on the two tables with the following query: 

SELECT t1.`* FROM `person` as t1 
      JOIN `person_temp` as t2 
      on t1.`date` = t2.`date` 
      and t1.`name` = t2.`name` 
      and t1.`country_id`= t2.`country_id`

The point of this query is to find all of the rows in t1 that match t2 where the combination of date,name, and country_id are the same (those three columns combined make a record unique). i don't think this query is correct for what I am trying to do because if I have the same exact data in both tables I am getting a much larger number of matches back.

Any ideas on how to edit this query to accomplish what I am trying to do?

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Don't use join. Use exists:

SELECT t1.`*
FROM `person` t1
where exists (select 1
              from `person_temp` as t2 
              where t1.`date` = t2.`date` 
                and t1.`name` = t2.`name` 
                and t1.`country_id`= t2.`country_id`
             );

For performance, you want a composite index on person_temp(date, name, country_id) (the columns can be in any order).

Upvotes: 1

Related Questions