On Thu, 3 Jun 2004 22:36:55 +0100 "Andrew Dixon" <[EMAIL PROTECTED]> wrote:
> Hi Everyone. > > I have the following SELECT statement the get information from two > tables. The statement only works when it finds a value for the > image_archive.circuit value in the circuits table. However, not all > entries in the database has a value in the this field, some are set > to 0 as the circuit for that image was not known or the image was > not a circuit. When you have an "unknown" value, the best value to use in a relational database instead of 0 or an empty string or anything else is NULL, that's what NULL was actually created for (well, one of the reasons anyway). Then your query would turn into a fairly simple outer join. FROM image_archive ia LEFT OUTER JOIN circuits c ON ia.id = c.circuit WHERE ia.id = 109 > SELECT image_archive.filename, > image_archive.year, > image_archive.month, > image_archive.driver_forename, > image_archive.driver_surname, > image_archive.team, > image_archive.event, > circuits.name as circuit_name, > image_archive.description, > image_archive.title, > image_archive.membership_no > FROM image_archive, circuits > WHERE image_archive.id = 109 > AND circuits.id = image_archive.circuit Maybe tack on: OR image_archive.circuit = 0? > How can I modify the statement to allow it to return a record when > the image_archive.circuit value is 0, but to return the circuit name > when the value is greater than 0. At the moment when the circuit > value is 0 no records are returned even though I know the rest of > the information is in the image_archive table. Hope that makes > sense. Thanks in advances for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
