Limiting results on table joins

[jenni]

I have a query that is basically this:

 SELECT count(*) 
 FROM table1 as t1
 LEFT JOIN table2 as t2 on t2.foo=t1.foo
 group by t1.bar

The basic information is in table 1, but I need additional information from table 2. The problem is that there may be more than one row in table 2 with the same "foo". The difference is that only one item would be flagged as active.

I can't just do WHERE t2.status=1 (active) because this is a historical report and we may need to count those that are inactive.

I also can't change the field that I group by because even though I am joining on one field, I need it grouped by "bar".

Is there any way to change the join statement so that it will always only pull one record from table 2? If there is more than one, it should only pull the active one.

Thanks to anyone who is willing to tackle this one...

[paulnaj]

Hi Jenni,

You seem to be asking to both get 'all' records from t2 ...

I can't just do WHERE t2.status=1 (active) because this is a historical report and we may need to count those that are inactive.

... and for 'active-only' records later ...

If there is more than one, it should only pull the active one.

Can't do both 😉

Paul.

[Lars_Berg]

I don't understand what you are trying to accomplish. Could you give some sample data and the expected result?

[jenni]

Lars, sorry for not responding to your request. It was just too complicated to explain the initial query.

What we ended up doing was adding a new status called Historical Record. Items with that status are not included in the query. That way, we are able to clean up the items so that we can have more than one record in table 2, but only one that would be returned in the query. Sounds retarded, but it actually works beautifully.

Could anyone tell me how to mark a thread as resolved?