Page 1 of 1

My queries locking up the server

Posted: Tue Oct 02, 2007 10:37 am
by impulse()
I can't seem to get my logic straight today so was hoping somebody could point out what I'm screwing up on. I have 2 table but I want to join one of them to the other twice. The first time I join it I want to count how many times columnA = 'X' and the second time I join it I want it to count how many times ColumnA = 'Y'.

So far I have:

Code: Select all

select count(*), a.Column, b.column, c.name
from tablea as a, tablea as b, names as c
where a.id = c.id
and b.id = c.id
group by c.name
I've tried different variations of this and I've spent a while building the query up from a basic query but it fails as soon as I bring the same table in twice.

Posted: Tue Oct 02, 2007 11:16 am
by feyd
The query would produce an exponential result set. Are you sure you want that?

Posted: Tue Oct 02, 2007 12:23 pm
by impulse()
Hopefully you're talking about my query, which I understand is poop.

What I'm trying to achieve should be possible I would've thought in a single query.

Posted: Tue Oct 02, 2007 12:28 pm
by feyd
Yes, I was talking about your posted query.

What you are wishing for should only require a single join. Something like WHERE foo IN('bar','bif') and a GROUP BY on "foo."