I'm new to SQL so don't laugh if this is easy to spot but I'm having trouble with a select statement but I do not think it's the syntax etc, I'm convinced it must be due to some restrictions and limitations within SQL.

Could anyone shed some light? I've been 'googling' for two days now and can't find anything, much appreciated! thanks.


SELECT a.medno_id,
tblAction.action_shortdesc,
tblClassification.class_shortdesc,
tblType.type_shortdesc,
tblUnit.unit_shortdesc,
tblSystem.system_shortdesc,
a.med_oldserial,
a.med_builddate,
tblLibrary.lib_shortdesc,
a.med_f24bookno,
a.med_f24entry,
a.med_f102bookno,
a.med_f102entry,
tblTransport.trans_shortdesc,
a.med_courierno,
tblPOC.poc_surname,
a.med_title,
a.med_notes

FROM tblMedia a

INNER JOIN tblAction ON a.action_id = tblAction.action_id
INNER JOIN tblClassification ON a.class_id = tblClassification.class_id
INNER JOIN tblType ON a.type_id = tblType.type_id
INNER JOIN tblUnit ON a.unit_id = tblUnit.unit_id
INNER JOIN tblSystem ON a.system_id = tblSystem.system_id
INNER JOIN tblLibrary ON a.lib_id = tblLibrary.lib_id
INNER JOIN tblTransport ON a.trans_id = tblTransport.trans_id
INNER JOIN tblPOC ON a.poc_id = tblPOC.poc_id

WHERE a.medno_id = 327
ORDER BY a.med_effdate


I've spaced out the query so it's easy to read

I've started the query from scratch building my SQL adding one field at a time and it works perfect until it reaches a certain number of fields, it's as if SQL has a limit to no of fields it can return/be used in the select part of the statement. Are there any limitations I should know about? or am I being an idiot and doing something I shouldn't?

Hope this makes sense, thanks!

I've been doing more fiddling and it could be because some of the fields are NULL. It only seems to return a record if the id I am searching has a value in all the fields I search for. So therefore if any of the fields in the select part of my statement would return a NULL it doesn't work, otherwise it seems to return something.
This strikes me as wierd, is there a problem with NULLS? not sure where to start ! agh!

Use a different join instead of an inner join - inner joins ignore nulls. LEFT JOIN will include all rows in the left hand table, regardless of the NULLs in the right hand one. RIGHT JOIN is the opposite, with all the values from the right hand table listed even if the values in the left hand table are NULL. An OUTER JOIN (I think) does both, and will ensure all the records get listed in the join regardless of NULLS.