[thelist] MYSQL - join a table, to itself?

Jason Handby jason.handby at corestar.co.uk
Fri Sep 21 07:17:40 CDT 2007


Hi Tris,


> Ello all!
> I've got a table that captures users activity on a points 
> spending site.
> 
> my statement table has the follwoing fields:
> 
> date, points, calcType, userId
> 
> calcType is boolean, where 0 = -  AND 1 = +
> 
> What I've been asked to do is simply show 3 peices of data:
> Total points added
> Total poitns spent
> Total points left over...
> 
> So, I've got a query
> SELECT SUM(points) as earned
> FROM `statement` as earnedTable
> WHERE
> earnedTable.calctype = 1
> 
> But I'm convinced there's gotta be away to join the table with itself
> and get the calcType = 0 total too...
> the final query, I just want 2 fields.. I can work out the 
> thrid myself...
> 
> So, any ideas?

You should just be able to do this:

SELECT
	(SELECT SUM(points) FROM `statement` WHERE calctype=1)
		AS pointsEarned,
	(SELECT SUM(points) FROM `statement` WHERE calctype=0)
		AS pointsSpent



Jason



More information about the thelist mailing list