[SOLVED] Help with two table query

[kjtocool]

I have two tables: weeks and actuals.

 

I need to write a query which selects all rows from the weeks table that aren't in the actuals table.  Both tables share a week_id which should be used for the comparison.

 

So I need something like:

 

SELECT * FROM weeks WHERE weeks.week_id doesn't exist in actuals ...

[remyaarun]

select * from weeks left join actuals on weeks.id = actuals.id;

 

Try out with this code.

[kjtocool]

I tried:

 

SELECT * FROM bonanza_weeks LEFT JOIN bonanza_actuals ON bonanza_weeks.week_id = bonanza_actuals.week_id

 

It doesn't work properly, it just returns every row in bonanza_weeks.

 

In each table, week_id is a unique value, only appearing once per table.

[remyaarun]

select * from weeks where weeks.week_id not in (select * from actuals where actuals.id=weeks.week_id)

 

try with this

[kjtocool]

SELECT *
FROM bonanza_weeks
WHERE bonanza_weeks.week_id 
NOT IN (
SELECT *
FROM bonanza_actuals
WHERE bonanza_actuals.week_id = bonanza_weeks.week_ID
)

 

Returns Error: #1241 - Operand should contain 1 column(s) 

[remyaarun]

SELECT * FROM bonanza_weeks

WHERE bonanza_weeks.week_id

NOT IN (SELECT bonanza_actuals.week_id FROM bonanza_actuals

        WHERE bonanza_actuals.week_id = bonanza_weeks.week_ID)

[kjtocool]

SELECT *
FROM bonanza_weeks
WHERE bonanza_weeks.week_id 
NOT IN 
(
SELECT bonanza_actuals.week_id
FROM bonanza_actuals
WHERE bonanza_actuals.week_id = bonanza_weeks.week_ID
)

 

Works perfectly.  Thanks a bunch!

[fenway]

That can be very slow, since it's a correlated subquery -- it has to run for EVERY record in the outer table...  a LEFT JOIN ... IS NULL would be better.