Postgres Pro
Downloads
Home   >   mailing lists

Thread: [newbie] Relations...

[newbie] Relations...

From
"John Moo"
Date:
I have a real stupid (propably) problem :)
I have two tables, one holding accounts:

CREATE TABLE accounts (
login varchar(32) primary key,
password varchar(32),
name varchar(128),
email varchar(64));

and one holding posts (to news system):

CREATE TABLE news (
author varchar(32) references accounts(login),
title varchar(32),
body text,
date timestamp default now(),
id serial);

...and I want using _one_ SQL SELECT get all news rows but with
accounts.name relation.
"SELECT name,title,body,date FROM accounts,news" doesn't work fine,
because it gets all rows from accounts table.

Thanks for help
John Moo

Re: [newbie] Relations...

From
"Jerzy Kut"
Date:
Hi!
I don't know if i understand of your problem but i think You try to do
natural join on referenced attributes from both tables. Case explained by
You looks like cross join. Try to do:

SELECT name,title,body,date FROM accounts,news WHERE author=login;

All should works fine.

But sorry if i give trivial solution - maybe i don't understand...

Best regards.

Jerzy Kut

"John Moo" <projects@bitnet-nospam.pl> wrote in message
news:9fj785$81j$1@news.tpi.pl...
> I have a real stupid (propably) problem :)
> I have two tables, one holding accounts:
>
> CREATE TABLE accounts (
> login varchar(32) primary key,
> password varchar(32),
> name varchar(128),
> email varchar(64));
>
> and one holding posts (to news system):
>
> CREATE TABLE news (
> author varchar(32) references accounts(login),
> title varchar(32),
> body text,
> date timestamp default now(),
> id serial);
>
> ...and I want using _one_ SQL SELECT get all news rows but with
> accounts.name relation.
> "SELECT name,title,body,date FROM accounts,news" doesn't work fine,
> because it gets all rows from accounts table.
>
> Thanks for help
> John Moo
>

Re: [newbie] Relations...

From
"John Moo"
Date:
"Jerzy Kut" <jerzyk@inquad.com> wrote in message
news:9fjjen$2p3p$1@news.tht.net...
> Hi!
> I don't know if i understand of your problem but i think You try to do
> natural join on referenced attributes from both tables. Case explained
by
> You looks like cross join. Try to do:
>
> SELECT name,title,body,date FROM accounts,news WHERE author=login;
>
> All should works fine.
>
> But sorry if i give trivial solution - maybe i don't understand...
>
> Best regards.
>
> Jerzy Kut

That's it !
Thanks a lot !

PS. It's good to be stuipid after all ;)

John Moo

Re: [newbie] Relations...

From
"Thalis A. Kalfigopoulos"
Date:
On Tue, 5 Jun 2001, John Moo wrote:

> I have a real stupid (propably) problem :)
> I have two tables, one holding accounts:
>
> CREATE TABLE accounts (
> login varchar(32) primary key,
> password varchar(32),
> name varchar(128),
> email varchar(64));
>
> and one holding posts (to news system):
>
> CREATE TABLE news (
> author varchar(32) references accounts(login),
> title varchar(32),
> body text,
> date timestamp default now(),
> id serial);
>
> ...and I want using _one_ SQL SELECT get all news rows but with
> accounts.name relation.
> "SELECT name,title,body,date FROM accounts,news" doesn't work fine,
> because it gets all rows from accounts table.

You have to add a condition to ask for the particular name i.e. add a WHERE clause.

Check http://www.postgresql.org/idocs/index.php?queries.html to refhresh your sql :-)


cheers,
thalis

>
> Thanks for help
> John Moo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>