Re: JOIN help
ah that's what I'm doing. I was doing it wrong, but went to a meeting and the wrong information displayed. I was aligning primary keys, you are right, and should've been doing a foreign key to a primary key. Thanks Deanna! >It's no the primary keys that need to be =. What you need is to have a >foreign key in the table that holds the email. So, for example, you'd >have: >empower_local_case_studies >studyid (PK) >name >etc > >empower_registration >registrationid (PK) >studyid (FK) >etc. > >Then, you'd join: >WHERE erc.studyid = er.studyid > >Does that make sense? > >On 6/7/05, daniel kessler <[EMAIL PROTECTED]> wrote: >> ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208844 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: JOIN help
>>Does the registration_id really match the >>local_case_study_id? -- it >>looks like you're joining the primary key on both >>tables... not that >>doing that would have to be invalid, but this doesn't seem >>like the >>sort of situation where I would expect that. > I thought that this is how I relate the tables. Really, > I'm just trying to have the email address from the > registration DB as part of the returned information. I > was following a tutorial and it said to make sure the > primary keys are =. Here's the tutorial I was using: > http://www.w3schools.com/sql/sql_join.asp > Is this not right? I'm always interested in learning. Ahh okay... you seem to have misread the tutorial a bit... I think Deanna Schneider already posted this, but here's my nutshell synopsis just in case you missed hers. The primary key (PK) on a given table uniquely identifies each record in that table with the purpose being that the PK can then be used for updates, deletions or foreign key references / constraints to that table. So when you want to join a second table, the 2nd table needs to have a foreign key (FK) column containing the same value as the PK column in the 1st table... So typically the join would look like this: cs.case_study_id = er.case_study_id You still want the registration_id (PK) in the ER table to uniquely identify the registration, but you need the extra er.case_study_id column (not a primary key) in order to create the cross-reference between ER and ECS... In simple terms, you can think of it as a parent-child sort of relationship (it's not, but bear with me), where the parent needs an identifying "name" (primary key) and the child also needs an identifying "name" (PK) so that when other people are talking about them, there's no confusion about whether the parent (Bruce) or the child (Brandon) is being referenced. Now when someoen asks "who is Brandon's parent?" of course, you don't answer "Brandon", you answer "Bruce". The foreign key column in the ER table is where you store that information about the other table -- the parent or, in this case "Bruce Lee". Although -- to complicate matters. :) It sounds like you may actually be wanting a 3-table schema where ECS contains case studies, ER contains registrants and Empower_Case_Study_Registrants (ECSR) could indicate which registrants are registered to which case studies (since you don't want multiple records for each registrant if a registrant can participate in multiple studies). In this case the ECSR table would contain only the two foriegn key columns to both the ECS and ER tables. cs.case_study_id = ecsr.case_study_id er.registrant_id = ecsr.registrant_id This is what's called a "many-to-many" relationship, as opposed to the "one-to-many" relationship I described a minute ago re: parents... and is actually more like human parent-child relationships, as in a well-designed database, a child would have 0 or more "guardians" and each guardian would have 0 or more children. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208829 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: JOIN help
It's no the primary keys that need to be =. What you need is to have a foreign key in the table that holds the email. So, for example, you'd have: empower_local_case_studies studyid (PK) name etc empower_registration registrationid (PK) studyid (FK) etc. Then, you'd join: WHERE erc.studyid = er.studyid Does that make sense? On 6/7/05, daniel kessler <[EMAIL PROTECTED]> wrote: > >You should probably qualify the columns in your where clause, and with > >table-names of this length, I would definately use aliases. See if > >this helps any. > > I was going to, but didn't remember right-off how to do so. I've done so > now, thanks. > > >SELECT ecs.*,er.email > >FROM empower_local_case_studies ecs, > > empower_registration er > >WHERE ecs.page = (param 1) AND ecs.project = (param 2) > > AND (ecs.local_case_study_id = er.registration_id) > >ORDER BY ecs.local_case_study_id ASC > > > >Does the registration_id really match the local_case_study_id? -- it > >looks like you're joining the primary key on both tables... not that > >doing that would have to be invalid, but this doesn't seem like the > >sort of situation where I would expect that. > > I thought that this is how I relate the tables. Really, I'm just trying to > have the email address from the registration DB as part of the returned > information. I was following a tutorial and it said to make sure the primary > keys are =. Here's the tutorial I was using: > http://www.w3schools.com/sql/sql_join.asp > > Is this not right? I'm always interested in learning. > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208793 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: JOIN help
>You should probably qualify the columns in your where clause, and with >table-names of this length, I would definately use aliases. See if >this helps any. I was going to, but didn't remember right-off how to do so. I've done so now, thanks. >SELECT ecs.*,er.email >FROM empower_local_case_studies ecs, > empower_registration er >WHERE ecs.page = (param 1) AND ecs.project = (param 2) > AND (ecs.local_case_study_id = er.registration_id) >ORDER BY ecs.local_case_study_id ASC > >Does the registration_id really match the local_case_study_id? -- it >looks like you're joining the primary key on both tables... not that >doing that would have to be invalid, but this doesn't seem like the >sort of situation where I would expect that. I thought that this is how I relate the tables. Really, I'm just trying to have the email address from the registration DB as part of the returned information. I was following a tutorial and it said to make sure the primary keys are =. Here's the tutorial I was using: http://www.w3schools.com/sql/sql_join.asp Is this not right? I'm always interested in learning. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208791 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: JOIN help
> I am a bit new to JOINs and I am having trouble with this > JOIN. I > don't get an error until the last AND and then it says: > [Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL > command not > properly ended > SELECT > empower_local_case_studies.*,empower_registration.email > FROM empower_local_case_studies,empower_registration > WHERE page= (param 1) AND project = (param 2) AND > empower_local_case_studies.local_case_study_id = > empower_registration.registration_id > ORDER BY empower_local_case_studies.local_case_study_id > ASC You should probably qualify the columns in your where clause, and with table-names of this length, I would definately use aliases. See if this helps any. SELECT ecs.*,er.email FROM empower_local_case_studies ecs, empower_registration er WHERE ecs.page = (param 1) AND ecs.project = (param 2) AND (ecs.local_case_study_id = er.registration_id) ORDER BY ecs.local_case_study_id ASC Does the registration_id really match the local_case_study_id? -- it looks like you're joining the primary key on both tables... not that doing that would have to be invalid, but this doesn't seem like the sort of situation where I would expect that. You might also want to try running this query in SQL+ ... Iirc the debugging in SQL+ is slightly improved over the error messages returned from Oracle to CF. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208786 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: JOIN help
Jochem, Thanks, that worked like a champ. I had a solution using 2 queries, but that wasn't really elegant. This should be much better. Thanks! Cedric > Subject: JOIN help > From: Jochem van Dieten <[EMAIL PROTECTED]> > Date: Sun, 16 Nov 2003 13:53:17 +0100 > Thread: http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&threadid=28709&forumid=4#144210 > > Pascal Peters wrote: > > SELECT Q.QueueID, XUQ.AccessID, Q.QueueName > > FROM Queues Q > > JOIN SecurityXrefUserQueues XUQ > > ON XUQ.QueueID = Q.QueueID > > WHERE XUQ.UserID = value="#url.uid#"> > > UNION > > SELECT Q.QueueID, NULL AS AccessID, Q.QueueName > > FROM Queues Q > > WHERE Q.QueueID NOT IN ( > > SELECT XUQ.QueueID > > FROM SecurityXrefUserQueues XUQ > > WHERE XUQ.UserID = value="#url.uid#"> > > ) > > How about: > SELECT > q.ID, > uq.AccessID, > AS > userID > FROM > queues q LEFT OUTER JOIN ( > SELECT uq.userID, uq.AccessID, uq.queueID > FROM userqueues uq > WHERE userID = > value="#url.uid#"> > ) uq ON q.ID = uq.queueID; > > Jochem > > -- > Who needs virtual reality > if you can just dream? > - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: JOIN help
Pascal Peters wrote: > SELECT Q.QueueID, XUQ.AccessID, Q.QueueName > FROM Queues Q > JOIN SecurityXrefUserQueues XUQ > ON XUQ.QueueID = Q.QueueID > WHERE XUQ.UserID = > UNION > SELECT Q.QueueID, NULL AS AccessID, Q.QueueName > FROM Queues Q > WHERE Q.QueueID NOT IN ( > SELECT XUQ.QueueID > FROM SecurityXrefUserQueues XUQ > WHERE XUQ.UserID = > ) How about: SELECT q.ID, uq.AccessID, AS userID FROM queues q LEFT OUTER JOIN ( SELECT uq.userID, uq.AccessID, uq.queueID FROM userqueues uq WHERE userID = value="#url.uid#"> ) uq ON q.ID = uq.queueID; Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: JOIN help
Cedric, I didn't examine the query too well in the first place. The outer join doesn't work because of the filter on the table you want nulls in. I would suggest a UNION instead of an outer join. I'm not sure it's the most efficient solution though. SELECT Q.QueueID, XUQ.AccessID, Q.QueueName FROM Queues Q JOIN SecurityXrefUserQueues XUQ ON XUQ.QueueID = Q.QueueID WHERE XUQ.UserID = UNION SELECT Q.QueueID, NULL AS AccessID, Q.QueueName FROM Queues Q WHERE Q.QueueID NOT IN ( SELECT XUQ.QueueID FROM SecurityXrefUserQueues XUQ WHERE XUQ.UserID = ) Pascal -Oorspronkelijk bericht- Van: Cedric Villat [mailto:[EMAIL PROTECTED] Verzonden: za 15/11/2003 17:12 Aan: CF-Talk CC: Onderwerp: Re:JOIN help No such luck. I had tried that earlier, but tried again just to make sure. A left and right join just return 1 row: UserID QueueID AccessID 2 1 0 I never get the NULL row. Any more ideas? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: JOIN help
> What I need is to select > User 1 and ALL Queues so I get a result like this: > > UserID QueueID AccessID > 1 2 0 > 1 1 NULL You could use an intermediate or temp table to return your desired result set like this: /* create temp table with one row for each queueid for each userid */ select u.id as userid, q.id as queueid into #t1 from users u, queues q GO /* this will provide the result you described */ select #t1.*, uq.accessid from #t1 left outer join userqueues uq on #t1.userid=uq.userid and #t1.queueid=uq.queueid order by #t1.userid, #t1.queueid GO drop table #t1 GO There are probably other, better ways to do this but I hope this may help you. Regards, Stephen [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: JOIN help
A left outer join? -Oorspronkelijk bericht- Van: Cedric Villat [mailto:[EMAIL PROTECTED] Verzonden: za 15/11/2003 2:52 Aan: CF-Talk CC: Onderwerp: JOIN help Here is my query now, but all it returns are rows in which there are entries in the UserQueues table. SELECT Q.QueueID, AccessID, Q.QueueName FROM Queues Q RIGHT JOIN SecurityXrefUserQueues XUQ ON XUQ.QueueID = Q.QueueID WHERE XUQ.UserID = #url.uid# Anyone have any idea how I can get the above result that I need? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
