Maybe it's too late and I haven't slept enough this week, but I'm still having a problem understanding how Adverts relates to the rest of your table set, and what data set you are trying to obtain. It almost seems as if you have done a design of the Users/Groups/Users_Groups_Link, and now you are adding in an almost arbitrary table and trying to build a query to include it, without first having properly defined what its relationship is to the rest of the data. But then again, it may just be that I'm a bit dense(-r than usual) at the moment... :->
The way I understand it, you have: Users (i.e. individuals) Groups (organizational grouping of users) Two relationships with Users: 1: Users' Group Memberships: Many-Many (resolved with UGL). 2: Groups' Managers: One[|None] to One (not all users are managers, one user may only be manager for one group(?), and each group has exactly one manager (?). Adverts (are what?) Describe what they are, and how they relate to Users and Groups. Finally, explain what you are trying to obtain instead of showing a query that doesn't do what you want. It can be kind of hard to guess what someone is trying to do. Consider creating a very small dummy dataset with a handful of rows in each table, show it (or better yet, include INSERT statements). Finally, produce the desired result set (by hand) from the data set described. HTH, Tore. ----- Original Message ----- From: "Nick Middleweek" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Friday, October 18, 2002 7:18 AM Subject: RE: SQL Help with multiple INNER JOINS > Could this be the correct SQL second part in the UNION? > > SELECT DISTINCT Ad1.Identifier > FROM Groups > INNER JOIN Users_Groups_Link > ON Users_Groups_Link.FK_Groups = Groups.PK_ID > INNER JOIN Adverts Ad2 > ON Ad2.FK_Users = Users_Groups_Link.FK_Users > INNER JOIN Advert_Fields > ON Advert_Fields.FK_Adverts = Ad2.PK_ID > AND Advert_Fields.Data LIKE '%' + @data + '%' > INNER JOIN Adverts Ad1 > ON Ad1.PK_ID = Advert_Fields.FK_Adverts > WHERE Groups.Administrator = (SELECT Users.Forename FROM Users WHERE > Users.uID = @userid) > > > I think I'm doing the relations from Groups down to Adverts - Ad2, then > I think I'm relating from Ad2 to AdvertFields, doing the extra query and > relating back to Adverts - Ad1? > > Is this ok? Does it make sense to do that? > I think I'm going mad, this is messing with parts of my head that shouldn't > be touched. > > Still not sure if I've got it? Tea break... > > > Cheers, > Nick > > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to %%email.unsub%% > --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
