the problme is in the where clause. the condition on the right side of the
OR statemnet will always test true.. or .. i should say is potentially
testing true. which is giving you your cartisian product.
i suggest you use either a left join or an inner join (depending on wether
you want nulls) in the from clause
then put your other conditions in the where clause.
ideally, it should look something like this:
select *
from categories left join emailAlerts on
categories.categoryID=emailAlerts.categoryID
where condition=value
On Fri, 1 Jun 2001, Kola Oyedeji wrote:
>
> 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
