When you say "nothing", do you mean NULL values? If so, you want an OUTER JOIN. This will return NULL values for the CertTypes columns where there is no matching CertType value. That said, I don't think you should be using SELECT *. You'd want to select a.CertType, but not c.CertType, and return data like: a.CertType a.Field1 c.Description 1 'something' 'something' 2 'something' 'something' NULL 'something' NULL 3 'something' 'something' SELECT a.CertType, a.Field1 c.Description FROM Gem AS a LEFT OUTER JOIN CertType AS c ON a.CertType = c.CertType However, if you are intending to use aggregate functions on columns from table C, then NULLs will not be included in things like AVG() etc. In which case you will probably want to use COALESCE() to return a non-null default value (eg 0) or similar. Cheers Ken ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ----- Original Message ----- From: "noah" <noah at tookish.net> To: <thelist at lists.evolt.org> Sent: Friday, February 28, 2003 4:51 PM Subject: Re: [thelist] SQL question : Hi Ken, : : Thanks for your help. : : At 12:21 AM 28/02/2003, you wrote: : >If a.CertType is not required, then what rows from Certtypes do you want to : >return when a.CertType is not set? : : Nothing from the CertTypes table, but I still want to return all the : records from the Gem table. The problem is that as soon as I introduce the : join between the Gem table and the CertTypes table, the query stops : returning all records in the Gem table where the CertType field is unset : (i.e., the field that I'm joining to the CertTypes table). : : >Perhaps if you could give us some sample data (a few rows from each table), : >plus what you want the expected output to be, we can help you write the : >query... : : Thanks -- it's actually quite convoluted, and I'm afraid that might further : confuse the issue (I simplified it significantly in asking my question). I : think the above explanation is better than my original one -- if not, let : me know and I'll put together the data. : : Here's the query again: : : >: SELECT * FROM Gem a, Shapes b, CertTypes c WHERE a.Shape = b.Shape AND : >: a.CertType = c.CertType AND NumberOfStones > 0 ORDER BY AverageSize : : Thanks again, : Noah : : -- : * * Please support the community that supports you. * * : http://evolt.org/help_support_evolt/ : : For unsubscribe and other options, including the Tip Harvester : and archives of thelist go to: http://lists.evolt.org : Workers of the Web, evolt !