Обсуждение: join question - three tables, two with foreign keys to the first

hey all,

here's my situation.  three tables:

create table employee (id integer primary key, name varchar(32));
create table employee_leave (id integer primary key, employee_id integer,
from_date date, to_date date, constraint emp_leave_fk foreign key
(employee_id) references employee (id));
create table employee_pay (id integer primary key, employee_id integer,
amount integer, constraint emp_pay_fk foreign key (employee_id) references
employee (id));

and some sample data:

insert into employee (id, name) values (1, 'dim');
insert into employee_leave (id, employee_id, from_date, to_date) values (2,
1, '10-05-2002', '14-05-2002');
insert into employee_leave (id, employee_id, from_date, to_date) values (1,
1, '10-06-2002', '14-06-2002');
insert into employee_pay(id, employee_id, amount) values (1, 1, 100);
insert into employee_pay(id, employee_id, amount) values (2, 1, 100);

and I want to retrieve the information for an employee (all pay, and all
leave) in one query....   here's what I've got

select emp.name, lv.from_date, lv.to_date, pay.amount
from employee as emp
left outer join employee_leave as lv on emp.id = lv.employee_id
left outer join employee_pay as pay on emp.id = pay.employee_id
where emp.id = 1

problem is that I dont get the null values I expect.... I want to be able to
iterate through the resultset and determine if the record is from the leave
table or pay table - but because I dont get null results, I cant....

any pointers/suggestions would be welcome.

cheers
dim

maybe just to qualify, I get this:

select emp.name, lv.from_date, lv.to_date, pay.amount
from employee as emp
left join employee_leave as lv on emp.id = lv.employee_id
left join employee_pay as pay on emp.id = pay.employee_id
where emp.id = 1;
name | from_date  |  to_date   | amount
------+------------+------------+--------dim  | 2002-10-05 | 2002-05-14 |    100dim  | 2002-10-05 | 2002-05-14 |
100dim | 2002-10-06 | 2002-06-14 |    100dim  | 2002-10-06 | 2002-06-14 |    100
 
(4 rows)

but would expect the results to be
name | from_date  |  to_date   | amount
------+------------+------------+--------dim  | 2002-10-05 | 2002-05-14 |   (null)dim  | 2002-10-05 | 2002-05-14 |
(null)dim |       (null)      |      (null)        |    100dim  |       (null)      |      (null)        |    100
 
(4 rows)

am I missing something?

ta again
dim

----- Original Message -----
From: "Dmitri Colebatch" <dim@bigpond.net.au>
To: <pgsql-sql@postgresql.org>
Sent: Friday, June 14, 2002 12:13 AM
Subject: [SQL] join question - three tables, two with foreign keys to the
first


> hey all,
>
> here's my situation.  three tables:
>
> create table employee (id integer primary key, name varchar(32));
> create table employee_leave (id integer primary key, employee_id integer,
> from_date date, to_date date, constraint emp_leave_fk foreign key
> (employee_id) references employee (id));
> create table employee_pay (id integer primary key, employee_id integer,
> amount integer, constraint emp_pay_fk foreign key (employee_id) references
> employee (id));
>
> and some sample data:
>
> insert into employee (id, name) values (1, 'dim');
> insert into employee_leave (id, employee_id, from_date, to_date) values
(2,
> 1, '10-05-2002', '14-05-2002');
> insert into employee_leave (id, employee_id, from_date, to_date) values
(1,
> 1, '10-06-2002', '14-06-2002');
> insert into employee_pay(id, employee_id, amount) values (1, 1, 100);
> insert into employee_pay(id, employee_id, amount) values (2, 1, 100);
>
> and I want to retrieve the information for an employee (all pay, and all
> leave) in one query....   here's what I've got
>
> select emp.name, lv.from_date, lv.to_date, pay.amount
> from employee as emp
> left outer join employee_leave as lv on emp.id = lv.employee_id
> left outer join employee_pay as pay on emp.id = pay.employee_id
> where emp.id = 1
>
> problem is that I dont get the null values I expect.... I want to be able
to
> iterate through the resultset and determine if the record is from the
leave
> table or pay table - but because I dont get null results, I cant....
>
> any pointers/suggestions would be welcome.
>
> cheers
> dim
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

On Fri, 14 Jun 2002 00:13:22 +1000, "Dmitri Colebatch"
<dim@bigpond.net.au> wrote:
>select emp.name, lv.from_date, lv.to_date, pay.amount
>from employee as emp
>left outer join employee_leave as lv on emp.id = lv.employee_id
>left outer join employee_pay as pay on emp.id = pay.employee_id
>where emp.id = 1
>
>problem is that I dont get the null values I expect....

Dmitri,

why do you expect nulls?
SELECT ... FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2;

returns nulls for the b-columns in the select list, if you have a row
in t1 with a value t1.col1, that does not appear as col2 in any row of
t2.  In your example, however, you select a single row from emp with
id = 1, and there are two rows in lv with employee_id = 1 and two rows
in pay with employee_id = 1.

And I doubt, you want to get the same row from lv more than once, only
because there are multiple matches in pay, and vice versa.  Add lv.id
and pay.id to your SELECT to see what I mean.  You may expect to get 4
rows, but what you get is not 2+2, but 2*2.  Add some more rows and
test again.  Isn't there any relationship between lv and pay?

I don't know if I understand your problem.  Propably you want:

SELECT emp.name, lv.from_date, lv.to_date, NULL AS amount FROM employee AS emp LEFT OUTER JOIN employee_leave AS lv ON
emp.id= lv.employee_idWHERE emp.id = 1
 
UNION ALL
SELECT emp.name, NULL AS from_date, NULL AS to_date, pay.amount FROM employee AS emp LEFT OUTER JOIN employee_pay AS
payON emp.id = pay.employee_idWHERE emp.id = 1;
 

or, if lv and pay are unrelated, why not two queries?

SELECT emp.name, lv.from_date, lv.to_date FROM employee AS emp LEFT OUTER JOIN employee_leave AS lv ON emp.id =
lv.employee_idWHEREemp.id = 1;
 
SELECT emp.name, pay.amount FROM employee AS emp LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_idWHERE
emp.id= 1;
 

HTH.
ServusManfred

On Fri, 14 Jun 2002, Dmitri Colebatch wrote:

> maybe just to qualify, I get this:
>
> select emp.name, lv.from_date, lv.to_date, pay.amount
> from employee as emp
> left join employee_leave as lv on emp.id = lv.employee_id
> left join employee_pay as pay on emp.id = pay.employee_id
> where emp.id = 1;
>
>  name | from_date  |  to_date   | amount
> ------+------------+------------+--------
>  dim  | 2002-10-05 | 2002-05-14 |    100
>  dim  | 2002-10-05 | 2002-05-14 |    100
>  dim  | 2002-10-06 | 2002-06-14 |    100
>  dim  | 2002-10-06 | 2002-06-14 |    100
> (4 rows)
>
> but would expect the results to be
>
>  name | from_date  |  to_date   | amount
> ------+------------+------------+--------
>  dim  | 2002-10-05 | 2002-05-14 |   (null)
>  dim  | 2002-10-05 | 2002-05-14 |   (null)
>  dim  |       (null)      |      (null)        |    100
>  dim  |       (null)      |      (null)        |    100
> (4 rows)
>
> am I missing something?

I don't see why you'd expect that.
Both rows in employee_leave match and both rows
in employee_pay match.  They're not unrelated joins,
you're asking to join employee with employee_leave
and then join the results of that with employee_pay.

Perhaps you want a union? Something like:select emp.name, lv.from_date, lv.to_date, null as amountfrom employee as emp,
employee_leaveas lv where emp.id=lv.employee_id
 
unionselect emp.name, null, null, pay.amountfrom employee as emp, employee_pay as pay where emp.id=pay.employee_id

If you want to get a row for an employee even when they
have neither leave nor pay, you can use left joins above,
but that'll give you some rows that'll be like
<name> NULL NULL NULL.

> why do you expect nulls?

probably because my sql is extremely rusty (o:

> SELECT ... FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2;
>
> returns nulls for the b-columns in the select list, if you have a row
> in t1 with a value t1.col1, that does not appear as col2 in any row of
> t2.  In your example, however, you select a single row from emp with
> id = 1, and there are two rows in lv with employee_id = 1 and two rows
> in pay with employee_id = 1.

yes, as Stephan Szabo wrote:
>> Both rows in employee_leave match and both rows
>> in employee_pay match.  They're not unrelated joins,
>> you're asking to join employee with employee_leave
>> and then join the results of that with employee_pay.

that makes perfect sense.  What I wanted is what you have given below (I
think - I've only looked quickly so far).  I suppose I want to do the left
outer join on leave, and a left outer join on pay - I dont want to join the
results of the first join with the second - as the pay and leave tables are
unrelated - except for the fact that they both have a fk to emp.

> And I doubt, you want to get the same row from lv more than once, only
> because there are multiple matches in pay, and vice versa.  Add lv.id
> and pay.id to your SELECT to see what I mean.  You may expect to get 4
> rows, but what you get is not 2+2, but 2*2.  Add some more rows and
> test again.  Isn't there any relationship between lv and pay?

no relationship.  what I wanted is: - for each row in employee, select all matching records in pay - for each row in
employee,select all matching records in leave - if no records match, select the matching record in employee alone.
 

from memory, oracle would do this by sql somehting like:

select emp.name, lv.from_date, lv.to_date, pay.amount
from employee as emp, employee_leave as lv, employee_pay as pay
where   emp.id = (+) lv.employee_id and   emp.id = (+) pay.employee_id
where emp.id = 1

(although I can never remember the side that the + goes on....)

> I don't know if I understand your problem.  Propably you want:
>
> SELECT emp.name, lv.from_date, lv.to_date, NULL AS amount
>   FROM employee AS emp
>   LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id
>  WHERE emp.id = 1
> UNION ALL
> SELECT emp.name, NULL AS from_date, NULL AS to_date, pay.amount
>   FROM employee AS emp
>   LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id
>  WHERE emp.id = 1;

yes, I think this is what I want.... which is effectively just the same as
below yes?

> or, if lv and pay are unrelated, why not two queries?

I was wanting to only have one trip to the database.  I've always been
taught to avoid multiple db trips where possible.....

thanks for your help - much appreciated.

cheers
dim

>
> SELECT emp.name, lv.from_date, lv.to_date
>   FROM employee AS emp
>   LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id
>  WHERE emp.id = 1;
> SELECT emp.name, pay.amount
>   FROM employee AS emp
>   LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id
>  WHERE emp.id = 1;
>
> HTH.
> Servus
>  Manfred
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

My problem is, i am writing a simple retrieval program using C, to retrieve
a set of records from database. Then i'll make certain calculation based on
the data that i have retrieved and write it on a new database. I have follow
the sample program  to do the retrieval and it works. But i don't seems to
have any samples to do an INSERT to the database? please anyone who have
such simple or working programs where it can INSERT to any database , please
reply to me as a reference.


here is the sample program that i used to do my retrieval:


#include <stdio.h>
#include "libpq-fe.h"

void
exit_nicely(PGconn *conn)
{   PQfinish(conn);   exit(1);
}

main()
{   char       *pghost,              *pgport,              *pgoptions,              *pgtty;   char       *dbName;   int
       nFields;   int         i,               j;
 


   PGconn     *conn;   PGresult   *res;
   pghost = NULL;   pgport = NULL;   pgoptions = NULL;
   pgtty = NULL;   dbName = "template1";

   conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

   if (PQstatus(conn) == CONNECTION_BAD)   {       fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
  fprintf(stderr, "%s", PQerrorMessage(conn));       exit_nicely(conn);   }
 

   res = PQexec(conn, "BEGIN");   if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
   {       fprintf(stderr, "BEGIN command failed\n");       PQclear(res);       exit_nicely(conn);   }

   PQclear(res);

   res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from event");   if (!res || PQresultStatus(res) !=
PGRES_COMMAND_OK)  {       fprintf(stderr, "DECLARE CURSOR command failed\n");       PQclear(res);
exit_nicely(conn);  }   PQclear(res);   res = PQexec(conn, "FETCH ALL in mycursor");   if (!res || PQresultStatus(res)
!=PGRES_TUPLES_OK)   {       fprintf(stderr, "FETCH ALL command didn't return tuples
 
properly\n");       PQclear(res);       exit_nicely(conn);   }

   nFields = PQnfields(res);   for (i = 0; i < nFields; i++)       printf("%-15s", PQfname(res, i));   printf("\n\n");

   for (i = 0; i < PQntuples(res); i++)   {       for (j = 0; j < nFields; j++)           printf("%-15s",
PQgetvalue(res,i, j));       printf("\n");   }   PQclear(res);
 

   res = PQexec(conn, "CLOSE mycursor");   PQclear(res);
   res = PQexec(conn, "COMMIT");   PQclear(res);

   PQfinish(conn);
   return 0;

}
Thanks,
joo

joo, 
it's just like 
res = PQexec(conn, "BEGIN");
do 
res = PQexec(conn, "insert into events values('blabla', ...)");

if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
tells you if your command was ok 

if you do an update or delete 
atoi(PQcmdTuples(res)) 
tells you how many rows are affected 

I hope this helps. 
Regards, Christoph 
> My problem is, i am writing a simple retrieval program using C, to retrieve
> a set of records from database. Then i'll make certain calculation based on
> the data that i have retrieved and write it on a new database. I have follow
> the sample program  to do the retrieval and it works. But i don't seems to
> have any samples to do an INSERT to the database? please anyone who have
> such simple or working programs where it can INSERT to any database , please
> reply to me as a reference.
> 
> 
> here is the sample program that i used to do my retrieval:
> 
> 
> #include <stdio.h>
> #include "libpq-fe.h"
> 
> void
> exit_nicely(PGconn *conn)
> {
>     PQfinish(conn);
>     exit(1);
> }
> 
> main()
> {
>     char       *pghost,
>                *pgport,
>                *pgoptions,
>                *pgtty;
>     char       *dbName;
>     int         nFields;
>     int         i,
>                 j;
> 
> 
> 
>     PGconn     *conn;
>     PGresult   *res;
> 
>     pghost = NULL;
>     pgport = NULL;
>     pgoptions = NULL;
> 
>     pgtty = NULL;
>     dbName = "template1";
> 
> 
>     conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
> 
> 
>     if (PQstatus(conn) == CONNECTION_BAD)
>     {
>         fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
>         fprintf(stderr, "%s", PQerrorMessage(conn));
>         exit_nicely(conn);
>     }
> 
> 
>     res = PQexec(conn, "BEGIN");
>     if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> 
>     {
>         fprintf(stderr, "BEGIN command failed\n");
>         PQclear(res);
>         exit_nicely(conn);
>     }
> 
> 
>     PQclear(res);
> 
> 
>     res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from event");
>     if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
>     {
>         fprintf(stderr, "DECLARE CURSOR command failed\n");
>         PQclear(res);
>         exit_nicely(conn);
>     }
>     PQclear(res);
>     res = PQexec(conn, "FETCH ALL in mycursor");
>     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
>     {
>         fprintf(stderr, "FETCH ALL command didn't return tuples
> properly\n");
>         PQclear(res);
>         exit_nicely(conn);
>     }
> 
> 
>     nFields = PQnfields(res);
>     for (i = 0; i < nFields; i++)
>         printf("%-15s", PQfname(res, i));
>     printf("\n\n");
> 
> 
>     for (i = 0; i < PQntuples(res); i++)
>     {
>         for (j = 0; j < nFields; j++)
>             printf("%-15s", PQgetvalue(res, i, j));
>         printf("\n");
>     }
>     PQclear(res);
> 
> 
>     res = PQexec(conn, "CLOSE mycursor");
>     PQclear(res);
> 
>     res = PQexec(conn, "COMMIT");
>     PQclear(res);
> 
> 
>     PQfinish(conn);
> 
>     return 0;
> 
> }
> Thanks,
> joo