How about using left outer join: SELECT [emailAlerts].[emailAlert_ID], [emailAlerts].[Category_id], [CATEGORIES].[Category_name], [emailAlerts].[email], [emailAlerts].[Category_id], [emailAlerts].[ADVERT_TYPE], [emailAlerts].[Name] FROM emailAlerts left outer join CATEGORIES on [CATEGORIES].[Category_id])=[emailAlerts].[Category_id] That should give you all records with corresponding categories, and those without any matching categories HTH Dan -----Original Message----- From: Kola Oyedeji [mailto:[EMAIL PROTECTED]] Sent: 01 June 2001 16:00 To: CF-Talk Subject: SQl:how to NOT get a cross join hi hope someone here can give me a hand with this:(sorry for the cross post) I have a table called categories, the key bieng an int, with a field for the category name. Another table called adverts, an advert has a category feild which is a foreign key which links to the category table. I realised i could create a view which would join the two tables so i would have a virtual table which would have the category name field. BUT using this: SELECT [emailAlerts].[emailAlert_ID], [CATEGORIES].[Category_id], [CATEGORIES].[Category_name], [emailAlerts].[email], [emailAlerts].[Category_id], [emailAlerts].[ADVERT_TYPE], [emailAlerts].[Name] FROM CATEGORIES, emailAlerts WHERE ((([CATEGORIES].[Category_id])=[emailAlerts].[Category_id])) OR emailAlerts.Category_id = 0; I find i get duplicates with say all the records with all the categories like a cartesian product kind of thing because there is no category with the ID of 0 the query returns records with this value with every value in the category table. many thanks in advance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
