> I'm responding to this old CF Talk message because I'm trying
> to figure out a SQL question. If you don't have any time for
> this, I totally understand!
>
> My problem is with left joins. I want to be able to specify
> conditions on fields that are in the left join but if I put
> a condition in the where clause, it knocks out all the rows
> with null values in that field.
>
> I was thinking that I could use a cursor to do a row specific
> query and enter that as the field value, but that seems really
> processor intensive.
Without understanding exactly what you want your recordset to look like, I
can't guarantee results, but I'll give it a shot. I'll assume that you've
got two tables, item and category. I'll assume that each item has exactly
one category, but some categories have no items. Finally, if I want to
retrieve all categories, and any items each might have, I'd build a left
join like this:
SELECT C.Category_Name,
I.Item_Name
FROM Category C
LEFT OUTER JOIN Item I ON C.Category_ID = I.Category_ID
If I want to filter on a field in the category table, I'd add a WHERE
clause:
WHERE C.Category_Name LIKE '#Form.Category_Name#%'
If I want to filter on a field in the item table, however, I'm going to end
up filtering both categories and items, because I won't be able to select
categories which don't have a matching item that meets my filtering
condition. Does that answer your question?
Finally, when I'm building queries, I often find it helpful to state, in
plain English, exactly what I want my recordsets to contain. I then convert
the English to SQL, in which I'm not nearly so fluent. If you can describe
your tables, and state your query in English, I can probably be more
helpful.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
