Double Inner Join not returning rows

[thefollower]

Hey,

 

I have a query with two inner joins or trying to anyway - but its not returning the rows correctly when it should find at least 1 row based on current data in my database.... this is what i tried:

 

<?php
$Get = mysql_query("
SELECT (army.Quantity+times.Quantity) AS TotalQuantity,military.Size 
  FROM army
INNER JOIN military ON army.ID=military.ID
INNER JOIN times ON army.ID=times.ID
	WHERE (times.UserID='$UserID' AND times.Type='Buy' AND times.Quantity>0)
		OR (army.Quantity > 0 AND army.UserID='$UserID')")
Or die(mysql_error());
?>

 

Currently army is empty, but times has 1 row with matching UserID and with a quantity of 500 and type set to "Buy" but mysql_num_rows returns 0. The idea is to get the total quantity the user has of a army ID which could be on either of the two: army or times table.

 

Hope you can help.

[cags]

I believe that an INNER JOIN will only return data if a row is found in both tables, if army is empty then you do not wish an INNER JOIN. To return results where one or more of the tables have a row I believe you should use FULL JOIN.

[thefollower]

What is the syntax of a full join ?

[kickstart]

Hi

 

MySQL doesn't support the FULL JOIN.

 

There are ways to simulate it though:-

 

http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

 

All the best

 

Keith