Daniel,
Not sure, cause I can't test it, but maybe something like this:
SELECT
n.meeting_name,
n.type,
n.note_date,
n.notes,
n.type_describe
FROM
notes_to_the_record n
LEFT OUTER JOIN
notes_to_the_record_shared s
ON
(
n.n_r_id = s.n_r_id
AND
s.id = #session.user.id#
)
WHERE
n.ID = #session.user.id#
OR
s.id = #session.user.id#
ORDER BY
UPPER(n.meeting_name) ASC
This will create a left outer join on the notes table on the notes id.
Then, you are selecting all notes where the user id of the note is the
session id OR the user id of the share is the session id.
The ON statement of the join should exclude and shares that don't
involve the session user. However, the "left outer" nature of the join
should at least keep the notes portion of the join (and NULL the share
values).
I could be way of though :)
......................
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-----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:267280
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
