What database are you using? I've never see that "+" notation before...
One thing I can suggest (without actually helping you) is to use
ANSI-style joins, which are done in the FROM clause, and not in the
WHERE clause. I find these to be much more expressive and sometimes
they are the only way to do something tricky, and you're getting close
to that now.
The idea is that instead of using "FROM a, b WHERE a.id = b.id" you
instead do "FROM a JOIN b ON (a.id = b.id)", and you leave the WHERE
clause to be only for non-joining constraints. It can really clear up
ambiguity (for the person reading the code, including the person who
wrote it), and it does away with special notations for OUTER joins,
which is what I suspect that "+" is for. (Outer joins look like "FROM a
LEFT OUTER JOIN b ON (a.id = b.id)", etc.)
As to your specific problem, how are the notes_to_the_record and
notes_to_the_record_shared tables related to each other and to the user?
I can't tell if your "id" field is the user id or the note id.
Mark
-----Original Message-----
From: Daniel Kessler [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 23, 2007 9:00 AM
To: CF-Talk
Subject: database JOIN question
I have a table (notes_to_the_record) of meeting notes
(fields:n_r_id,id,meeting_name,date_added,follow_up).
A new feature is that the meeting note is shared to a set of users.
So since multiple users can be selected, I made a new table called
notes_to_the_record_shared (n_r_shared_id,n_r_id,id).
In both those, id references a People table, so it's the person's ID
Here are the two tables:
http://hhp.umd.edu/home/db_display.cfm (table of notes)
http://hhp.umd.edu/home/db_display_shared.cfm (shared table)
When I do the search, I make sure that the person can only see their own
notes, so ID = #session.user.id#. Now with the notes ability to be
shared, I'm not sure how to expand the search to be "any notes with my
id in this table and any notes with my id in this second table" and join
correctly to have all the information.
I have it set up so that there is a record (n_r_id = 441) that is shared
to a person with id 3
I was told in a DB forum "Use outer join. table1.id = table2.id(+)".
So I'm doing that and it doesn't seem to be working. Here's the SQL:
SELECT n.meeting_name,n.type,n.note_date,n.notes,n.type_describe
FROM notes_to_the_record n,notes_to_the_record_shared s WHERE
n.ID = #session.user.id# AND
n.id = s.id(+)
ORDER BY UPPER(n.meeting_name) ASC
Please help - I'm really at a stand-still here.
--
Daniel Kessler
College of Health and Human Performance
University of Maryland
Suite 2387 Valley Drive
College Park, MD 20742-2611
Phone: 301-405-2545
http://hhp.umd.edu
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267278
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
