Inner Join multiple tables

qwprince · July 21st, 2003, 03:04 PM

I have 3 tables ..
   User - Details of tenant (UserID -Primary key)
   Administrator - Details of Administrator (AdminID - Primary)
   Messages - The Messages Collection
                   SenderID
                   ReceiverID

   the idea is the User as well as the Administrator can message any
  of the other Users.. now from the Messages Table i want to use
  INNER JOIN in such way that i get details of User if the msg is
  from another User or if from the Administrator the details of the
  Admin ..

  how do i do this other than INNER Join .... can i append to the
  recordset after initially searching for the detsils of
  Administrator and then later on appending the records if from a
  User

Imar · July 21st, 2003, 03:10 PM

Hi there,

There may be ways to do this with a query (you could use a UNION to combine the results of the User query with the Admin quey. Only one of them will return a record, so you'll end up with the one record you need).

However, IMO, you should reconsider your design. After all, an Administrator is just another user, albeit that he has more privileges.
If I were you, I'd drop the Administrator table, create a Rights or AccessLevels table and then give the User table an AccessLevelID column. If you want, you can create an intermediate table that creates a many-to-many relation between the User and the AccessLevels table. This way, you'll have a flexible solution, because you have fine-grained control over the rights (a user can belong to multiple security groups).

HtH

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

qwprince · July 21st, 2003, 03:16 PM

well i did think of using the same table ... but here the user is not a person with name .. its like say an outlet with a lot more data
like location, outletID, manager, contact nos, .. etc .etc .. whereas
administrator is only a person with very minimal details compared to the user .. thats why ..

what were sayin abt the UNION ..

Imar · July 21st, 2003, 03:31 PM

It was just an example. There are other ways as well.

A UNION could work like this (notice that both tables should return the same number and type of columns):

SELECT ID, Name, LastName FROM Users INNER JOIN Message Bla bla bla WHERE Bla bla bla

UNION

SELECT ID, Name, LastName FROM Administrator INNER JOIN Message Bla bla bla WHERE Bla bla bla

The results of this queries will be combined, creating a result set with only one record (if the setup is OK)

(Note, there may be better solutions than this, but it's the best I can come up with now)

Alternatively, if you are using aa DBMS that supports Stored Procedures, like SQL Server, you can use an IF statement to switch between a query that gets a record from the User table, and a query that uses the Administrators table.

You could also create a minimum table with the columns that overlap in the User and Administrator tables. Then create a second table, like Outlet that you join on the User table, to provide more details about the user. That way, you can treat your objects the same way (both a User and an Administrator can send / receive messages, and are, in fact, users). "Real" users also have more related info attached to them in the form of the Outlet table.

HtH

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

qwprince · July 23rd, 2003, 12:24 PM

the UNION wont work over here .. as my tables are not the same type

how abt it if i go this way ..
say i have a field 'MsgType' identifying the message as from 'Admin' or 'User'

the message table will also hold the id of the admin or user
If the 'MsgType' is from 'Admin' it shud look for details i the Administrator Table for matching id else if from 'User' it shud look in the 'User' table and return the necessary

here the details returned will be minimal say Name & location of admin or user ..

how do i put all of this into one recordset ....

im kind'f of new to programming ...
the thing i cant figure out is how do i use a condition like 'if' ... this 'else' this .. kind of stuff in SQL ..

Imar · July 23rd, 2003, 02:33 PM

Hmmm, moving the problem to a MsgType table just moves the problem. You still need a way to dynamically link to two tables.

What database are you using? If you are using SQL Server for example, simply use an IF statement as in the following pseudo code:

Code:

If (@TYPE = 'ADMIN')
    SELECT The Fields You Need FROM the Admin Table
Else
    SELECT The Fields You Need FROM the USer Table
End

You say your User table and Admin table don't match. However, it's about the fields you retrieve that need to be the same. So

Code:

SELECT ID FROM Admin
Union
SELECT ID FROM User

should work.

Alternatively, let your client code / business logic layer decide. If the user is an Admin, fire away a query that retrieves stuff from the Admin table, otherwise retrieve stuff from the User table.

(P.S. I still would consider a redesign for your table structure. Two exact the same types (an entity that can send an e-mail) are best modelled in the same table....)

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.