CODEWITHSUNDEEP
sql
user826323
user826323

Reputation: 2338

sql - compare two tables

I have two tables, table1 and table2. They both have same columns, program_id, scheduled_time and timezone_id.

I want to retrieve the rows whose have same program_id and timezone_id, but have different scheduled_time from table1.

So here is the sql that I tried:

select t1.* 
from table1 t1, table2 t2 
where t1.program_id = t2.program_id 
  and t1.timezone_id = t2.timezone_id 
  and t1.scheduled_time != t2.scheduled_time;

But I still see rows that have same program_id, scheduled_time and timezone_id.

Could someone fix the sql?

Thanks.

Upvotes: 1

Views: 2985

Answers (3)

Kermit
Kermit

Reputation: 34054

This should work. I would recommend always specifying a column list instead of using SELECT *.

SELECT t1.program_id, t1.scheduled_time, t1.timezone_id
FROM table1 t1
INNER JOIN table2 t2 ON 
    t2.program_id = t1.program_id AND
    t2.timezone_id = t1.timezone_id
WHERE
    t2.scheduled_time <> t1.scheduled_time

Upvotes: 0

marc_s
marc_s

Reputation: 754240

Try something like this:

SELECT
    t1.* 
FROM
    table1 t1
INNER JOIN
    table2 t2 ON t1.program_id = t2.program_id AND t1.timezone_id = t2.timezone_id 
WHERE
     t1.scheduled_time <> t2.scheduled_time

Basically, do an INNER JOIN on the common columns (program_id and timezone_id) and make sure the scheduled_time column has a different value.

Also: in SQL use the <> operator - != is for C# / VB.NET ....

Upvotes: 2

Robert
Robert

Reputation: 25753

Try to use subquery:

select t1.* 
from table1 t1
where exists
(
  select 1
  from table2 t2
  where t1.program_id = t2.program_id 
  and t1.timezone_id = t2.timezone_id 
  and t1.scheduled_time != t2.scheduled_time;
)

Upvotes: 2

Related Questions