Re: SQL query puzzler

leblancsx_at_corning.com wrote:
>
> I've been thinking about how to do this for some time:
>
> Say I have two tables, USER and COMPUTER.
> USER looks like this:
> user_nm
> user_id
> COMPUTER looks like this:
> computer_nm
> upper_user_id
> lower_user_id
>
> I want to get ALL users and computers (return for this query will be
> fed to a crosstab window) and indicate somehow (with Y or N or 1 or 0)
> whether user_id is between upper_user_id and lower_user_id or not.
>
> How can I do this? An outer join doesn't seem to do the trick for me.
> And I'm not sure of the syntax for a 'between' kind of join and being
> outer, if you know what I mean:
>
> ...WHERE user_id between lower_user_id and upper_user_id (+)
>

Hi,

This one works for me:

select user_nm, computer_nm, "Y"
from user, computer
where user_id>=lower_user_id and user_id<=upper_user_id

union

select user_nm, computer_nm, "N"
from user, computer
where not (user_id>=lower_user_id and user_id<=upper_user_id)

Be careful with "user" as a table name. On some systems it may be a keyword.

Let me know if it has solved your problem.

Cheers,

Victor

Software Development & Consulting

Nijmegen, the Netherlands
tel/fax: +31-24-3787058

e-mail: sokovin_at_NOSPAM.sci.kun.nl Received on Tue Jul 21 1998 - 06:11:13 CDT