MySQL Join Query for With Multiple Columns

Question

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?

please post you code and table structure in sqlfiddle that we can solve problem. — Gopal Joshi
– Gopal Joshi, Commented Feb 17, 2014 at 4:24
This should be correct, unless you have duplicates in the tables. — Adriaan Stander
– Adriaan Stander, Commented Feb 17, 2014 at 4:25

Gordon Linoff · Accepted Answer · 2014-02-17 04:35:47Z

1

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).

edited Feb 17, 2014 at 4:35

answered Feb 17, 2014 at 4:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Join Query for With Multiple Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related