Thread: Table joining problem.

Hi,

 I am not really novice, but not good either. I have a problem if the
table is empty, my select does not return anything then.
I have three tables,
1. users(nick varchar(),number int serial)
2. teams(owner references users)
3. challange(who references users)
I want to get all the users, who has a team, but not yet in the challange
table:
select users.nick, users.number from users, teams, challange where
teams.owner=users.number and challange.who!=users.number;
It does not return anything if the challange table is empty. If I insert a
line into it, it is ok. Can anyone help me out?

select users.nick, users.number from users, teams where
 teams.owner=users.number;
works correctly ofcourse, but I _must_ check the challange table too.

Any help is greatly appreciated,
Laszlo Boszormenyi,jr.

For MS SQL Server I would do the following:

select * from users
inner join teams on users.x = teams.x
left join challange on users.y = challange.y
where challange.z is null

I'm not sure if PostgreSQL supports the above; perhaps someone could comment?

-----Original Message-----
From: GCS [mailto:gcs@c64.rulez.org]
Sent: 26 May 2001 10:23
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Table joining problem.

Hi,

 I am not really novice, but not good either. I have a problem if the
table is empty, my select does not return anything then.
I have three tables,
1. users(nick varchar(),number int serial)
2. teams(owner references users)
3. challange(who references users)
I want to get all the users, who has a team, but not yet in the challange
table:
select users.nick, users.number from users, teams, challange where
teams.owner=users.number and challange.who!=users.number;
It does not return anything if the challange table is empty. If I insert a
line into it, it is ok. Can anyone help me out?

select users.nick, users.number from users, teams where
 teams.owner=users.number;
works correctly ofcourse, but I _must_ check the challange table too.

Any help is greatly appreciated,
Laszlo Boszormenyi,jr.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

>  I am not really novice, but not good either. I have a problem if the
> table is empty, my select does not return anything then.
> I have three tables,
> 1. users(nick varchar(),number int serial)
> 2. teams(owner references users)
> 3. challange(who references users)
> I want to get all the users, who has a team, but not yet in the challange
> table:

Perhaps:

select user.nick, users.number from users, teams
  where users.number = teams.owner and
  not exists (select * from challenge where challenge.who = users.number);

Regards,

Giles