My queries locking up the server

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

My queries locking up the server

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The query would produce an exponential result set. Are you sure you want that?
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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."
Post Reply