Re: Complex join query

On Wed, 29 May 2002, Gareth Kirwan wrote:

> Hello all,
> Let's dive straight in, eh?
>
> 1.
> Table: current_sessions
> Fields: Server (int Foreign Key servers.id)
> Client (int Foreign Key clients.id)
> status (vc(10))
>
> 2.
> Table: clients
> Fields: id (serial Pkey)
> username (UNIQUE)
>
> 3.
> Table: servers
> Fields: id (serial Pkey)
> username (UNIQUE)
>
> Normally, the username of the client should come from the clients table,
> hence you'd inner join plainly.
> IF the status is 'ADMIN' however - it means both the server and the client
> come from the servers table.

If you're actually using foreign keys for Client, you may have some other
problems with that structure since it seems like you've got a key pointing
off into the wrong table when status is ADMIN.

> HOW ( if at all ) can I pull this off in a join without having to
> restructure the tables?

I'd suggest using UNION ALL.
Get all the non admin ones with a join on client
UNION ALL
Get all the admin ones with another join on server