MySQL experts: question

[Varius]

Is this possible? (LEFT JOIN question)
I have two tables, I'll simplify them for the sake of this example:

table1:
campaign_id int unsigned not null default 0 key

table2:
month tinyint not null default 0 key
year smallint not null default 0 key
campaign_id int unsigned not null default 0 key
revenue double(5,2) not null default 0.00

What I"m trying to achieve is to get all the campaign_ids from table1, regardless of if they have a row in table2 or not.

This part I'm getting with:

"SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE (t2.campaign_id IS NULL OR t2.campaign_id IS NOT NULL)";

This will return me all campaign_ids from table1.

Here is my trouble:

If I want to add in my WHERE, a clause about the month/year, I always get back 0 results.

ie.

"SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE (t2.campaign_id IS NULL OR t2.campaign_id IS NOT NULL) AND t2.year=2004 AND t2.month=12";

* Is it possible for me to do this in one query? The results I want are simple, I want each campaign_id in table1 returned, with the revenue they earned IN THAT date range (if any).

Thx in advance !!

[korzon]

Instead of that stupid ass left join use "distinct".

[M_M]

SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE (t2.year=2004 AND t2.month=12) OR t2.campaign_id IS NULL

[DrGuile]

hmm, just a stupid question, but why do you have a month field and a year field... ??

why not a date field?

[Varius]

Originally posted by DrGuile
hmm, just a stupid question, but why do you have a month field and a year field... ??

why not a date field?

I use int 10 fields usually for unix timestamps....but I made it simple for this example =)

[Varius]

Originally posted by M_M
SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE (t2.year=2004 AND t2.month=12) OR t2.campaign_id IS NULL

Thanks!! This works, but now I have a final argument for the WHERE clause to add that's not working.

table1 also has this field:
uid int unsigned not null key

So how can I get the above, matching UID as well?

I tried this:

SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE ((t2.year=2004 AND t2.month=12) OR t2.campaign_id IS NULL) AND t1.uid=851

however it only returns me the rows from table1 which are NOT in table2.

[Varius]

Originally posted by korzon
Instead of that stupid ass left join use "distinct".

Either you didn't understand my question, or you don't know MySQL very well.....

[M_M]

Originally posted by Varius
Thanks!! This works, but now I have a final argument for the WHERE clause to add that's not working.

table1 also has this field:
uid int unsigned not null key

So how can I get the above, matching UID as well?

I tried this:

SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE ((t2.year=2004 AND t2.month=12) OR t2.campaign_id IS NULL) AND t1.uid=851

however it only returns me the rows from table1 which are NOT in table2.

SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE (t2.year=2004 AND t2.month=12 AND t1.uid=851) OR t2.campaign_id IS NULL

[Varius]

Originally posted by M_M
SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE (t2.year=2004 AND t2.month=12 AND t1.uid=851) OR t2.campaign_id IS NULL

Tried that one too but doesn't work. That one returns ALL rows from table1, because of the OR I suspect....

[Varius]

Ok I think I've almost got it.

I need a way though to specify WHERE (month !=12 AND year !=2004) .....anyone ???

ie.

month=5, year=2004 * should come up
month=12, year=2002 * should come up
month=12, year=2004 * shouldn't come up

Right now using WHERE (month !=12 AND year !=2004), none of the above return rows.

If I can get the above into a $where, the other query will work like this:

SELECT t1.campaign_id, t2.revenue FROM table1 t1 LEFT JOIN table2 t2 ON t1.campaign_id=t2.campaign_id WHERE t1.uid=851 AND ((t2.year=2004 AND t2.month=12) OR t2.campaign_id IS NULL OR $where)