INNER JOIN not working

[EchoFool]

Hey

 

I have an inner join that keeps returning 0 rows when it should not - im sure its just my approach to this is incorrect so was hopeing some one could correct what i got wrong firslty heres my query:

 

SELECT 
        buildings.Disabled,buildings.BuildingName,buildings.Cost,buildings.Bills,buildings.Size,buildings.RecordID
FROM 
        buildings
INNER JOIN 
        research
ON 
       buildings.ResearchID=research.ResearchID
WHERE 
       (buildings.ResearchID='0' OR (buildings.ResearchID>0 AND research.Completed='1' AND research.UserID='$UserID')) 

ORDER BY buildings.RecordID ASC

 

What its suppose to do, is load buildings from the buildings table, which has a ResearchID field of either zero or an id above 0.

 

If the research id is 0 then it doesn't need to check the research table related to the user, so those buildings should load regardless. And if it does have a ResearchID above 0 then check the research table to check the research is completed by the user and if so - load the building.

 

But currently no rows return at all when it should, hope you can help see what i got wrong!

[kickstart]

Hi

 

Is here a ResearchID of '0' on the research table? If not then you will never get any records returned where ResearchID is 0 (you would need a LEFT OUTER JOIN for that).

 

Can't see anything else obvious without looking at the data.

 

All the best

 

Keith

[EchoFool]

Hi

 

Is here a ResearchID of '0' on the research table? If not then you will never get any records returned where ResearchID is 0 (you would need a LEFT OUTER JOIN for that).

 

Can't see anything else obvious without looking at the data.

 

All the best

 

Keith

 

There are IDs defaulted to 0 but not in the research table. 0 means it doesnt require it to be linked to the research tabele.

[kickstart]

Hi

 

In which case on you join no row will be brought back when buildings.ResearchID='0' (so no point in having it in the WHERE clause).

 

If you do want them brought back then:-

 

SELECT buildings.Disabled,buildings.BuildingName,buildings.Cost,buildings.Bills,buildings.Size,buildings.RecordID
FROM buildings
LEFT OUTER JOIN research
ON buildings.ResearchID=research.ResearchID
WHERE (buildings.ResearchID='0' OR (buildings.ResearchID>0 AND research.Completed='1' AND research.UserID='$UserID')) 
ORDER BY buildings.RecordID ASC

 

All the best

 

Keith