Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
NOT IN Q |
Fri, Mar 3 2006 2:27 AM | Permanent Link |
Dave | Hi
Let's say i have three databases: main.dat: AutoInc Index Field + Name Field + Search + ReturnField group.dat: AutoInc Index Field + Group Field link.dat: Links Main Index to Group Index Now if i want to set ReturnField to some value for all members in Group "TestGroup" (which has an index of 23) I can use: UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID IN (23)) But how can i set ReturnField for all Members who are NOT in the group "TestGroup" (23)? Naturally I can't use the following: UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID NOT IN (23)) Because that would return Names that are in "TestGroup" (23) if they are also in another group. Excuse my ignorance and thanks in advance for any advice. Regards Dave |
Fri, Mar 3 2006 6:48 AM | Permanent Link |
"Ralf Mimoun" | Dave wrote:
.... > But how can i set ReturnField for all Members who are NOT in the > group "TestGroup" (23)? > > Naturally I can't use the following: > > UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER > JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID NOT IN > (23)) Try a LEFT OUTER JOIN with the Link table and add a WHERE L2.GroupID IS NULL. Ralf |
Fri, Mar 3 2006 10:36 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Now if i want to set ReturnField to some value for all members in Group "TestGroup" (which has an index of 23) I can use: UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID IN (23)) >> Actually, you shouldn't use a LOJ if you want to exclude those records in main that don't have a matching record in link (given the IN (23) condition). << But how can i set ReturnField for all Members who are NOT in the group "TestGroup" (23)? UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID NOT IN (23)) Because that would return Names that are in "TestGroup" (23) if they are also in another group. >> Use an INNER JOIN with a <> condition: UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" INNER JOIN "link.dat" L2 ON (M2.Index <> L2.NameID) WHERE (L2.GroupID IN (23)) -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 6 2006 5:17 AM | Permanent Link |
Dave | Tim wrote:
> UPDATE "main.dat" M2 set ReturnField=123 > FROM "main.dat" > INNER JOIN "link.dat" L2 ON (M2.Index <> L2.NameID) > WHERE (L2.GroupID IN (23)) Thanks Tim, but have i got this code correct? This sets every record in main.dat as 123. Dave |
Mon, Mar 6 2006 7:30 AM | Permanent Link |
"John Hay" | Dave
> main.dat: AutoInc Index Field + Name Field + Search + ReturnField > group.dat: AutoInc Index Field + Group Field > link.dat: Links Main Index to Group Index > > > Now if i want to set ReturnField to some value for all members in Group "TestGroup" (which has an index of 23) I can use: > > UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID IN (23)) > > > But how can i set ReturnField for all Members who are NOT in the group "TestGroup" (23)? > How about UPDATE "main.dat" SET ReturnField=123 WHERE Index NOT IN (SELECT NameId FROM "link.dat" WHERE GroupId = 23) John |
Mon, Mar 6 2006 12:11 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Thanks Tim, but have i got this code correct? This sets every record in main.dat as 123. >> It shouldn't. Attached is the table creation script and SQL that I used. -- Tim Young Elevate Software www.elevatesoft.com Attachments: linkgroup.sql linkgroupreverse.sql |
Mon, Mar 6 2006 8:51 PM | Permanent Link |
Dave | Thanks John and Tim,
Tim, you were right, it does work. However I went with John's suggestion because the INNER JOIN method failed (in that every record was set) if there were multiple items in the In clause (e.g. WHERE (GroupID in (30,23)). It seems just as quick too. Regards Dave |
Tue, Mar 7 2006 3:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Tim, you were right, it does work. However I went with John's suggestion because the INNER JOIN method failed (in that every record was set) if there were multiple items in the In clause (e.g. WHERE (GroupID in (30,23)). It seems just as quick too. >> Ahh yes, that would be an issue and I should have known that. In that case, yes the sub-select IN would work best. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |