CODEWITHSUNDEEP
mysqlsql
124697
124697

Reputation: 21893

How to select from two tables based on a where clause

I am trying to select from two tables and join them where they have the same id but my query seems wrong that it searches for so long that it crashed workbench.

   SELECT s.id,
          s.title,
          s.votes,
          t.*
     FROM movies_two.movie s, 
          movies_one.movie t 
LEFT JOIN movies_two.movie 
       ON movies_one.id = s.id -- not sure which join to use
    WHERE s.votes = -1 AND
          t.numofvotes > 0;

I have two databases containing similar data. I am trying to select rows that from movies_one and movies_two that have the same id and where movies_one has votes = -1 and movies_two has movies > 0

Upvotes: 1

Views: 861

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You don't need a left join for this. Nor do you need a cross join (which is what the comma does in the from clause.

I think you want something like this:

SELECT *
FROM movies_one.movie m1 JOIN
     movies_two.movie m2
     ON m1.id = m2.id AND
        m1.votes = -1 AND 
        m2.numofvotes > 0;

I would also suggest that you use table aliases that are abbreviations for the table names. Your text description of what you wand and your query are quite different.

Upvotes: 2

Orlando Helmer
Orlando Helmer

Reputation: 503

Try this:

SELECT * 
  FROM movies_one.movie 
 WHERE numofvotes = -1
 UNION
SELECT * 
  FROM movies_two.movie 
 WHERE votes > 0

Upvotes: 0

Patricia
Patricia

Reputation: 2865

SELECT  s.id ,s.title, s.votes, t.*  
FROM movies_two.movie s 
JOIN movies_one.movie t
ON t.id = s.id  
WHERE s.votes =-1  
AND t.numofvotes >0;

Would be okay. You use a left join, when you only want the data in movies_two.movie - the left table. But here you want the data of both tables.

Upvotes: 1

Related Questions