[thelist] SQL multiple Joins?

[Jason Handby]

Hi Tris,


> Is it possible to have multiple joins..

The short answer is "yes".

However, the slightly longer answer is, as with anything, that you will
only get the results you expect if you use them right :-)


> Ont eh query below. it failes.. Buuuut, if I limit it to one join, it
> works great..
> My database logs transactions and I'm trying to produce a report that
> shows each transaction total, within a certain period.. (Oct and sept
> so far..)
> 
> but it's cfalling over..


> 
> SELECT
> 	 SUM(activitiesSep.points) as sepPoints
> 	,SUM(activitiesOct.points) as octPoints
> 	,users.ID
> 	,users.Username
> 	,users.Password
> 	,users.Name
> 	,users.Company
> 	,users.Address1
> 	,users.Address2
> 	,users.Address3
> 	,users.Address4
> 	,users.Address5
> 	,users.Postcode
> 	,users.Email
> 	,users.CustomerType
> 	,users.Active
> 	,users.LogIns
> 	,users.GAURef
> 	,users.JRRef
> 	,users.GAUMember
> 	,users.GAUContact
> 	,users.RegForm
> 	,users.Created
> 	,users.Updated
> 
> FROM `users`
> 
> RIGHT JOIN `activities` as `activitiesSep` on
> 	activitiesSep.UserID = users.ID
> WHERE activitiesSep.`Timestamp` >= '1188604800' AND
> activitiesSep.`Timestamp` <= '1191196799'
> 
> RIGHT JOIN `activities` as `activitiesOct` on
> 	activitiesOct.UserID = users.ID
> WHERE activitiesOct.`Timestamp` >= '1191196800' AND
> activitiesOct.`Timestamp` <= '1193875199'
> 
> GROUP BY users.ID


OK, so one pretty nice way to do this would be to use subqueries,
perhaps like this:


SELECT
	 (SELECT SUM(points) FROM activities
		WHERE activities.`Timestamp`  >= '1188604800'
		AND activities.`Timestamp` <= '1191196799'
		AND activities.UserID = users.ID
	) AS sepPoints,
	 (SELECT SUM(points) FROM activities
		WHERE activities.`Timestamp` >= '1191196800'
		AND activities.`Timestamp` <= '1193875199'
		AND activities.UserID = users.ID
	) AS octPoints,
	,users.ID
	,users.Username
	,users.Password
	,users.Name
	,users.Company
	,users.Address1
	,users.Address2
	,users.Address3
	,users.Address4
	,users.Address5
	,users.Postcode
	,users.Email
	,users.CustomerType
	,users.Active
	,users.LogIns
	,users.GAURef
	,users.JRRef
	,users.GAUMember
	,users.GAUContact
	,users.RegForm
	,users.Created
	,users.Updated


I've just tested something like this in MS-SQL and it works fine, so
hopefully it should give you something to go on.




Jason