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