Thread: 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
Re: join question - requesting for a simple C program where it can INSERT data into database as reference
From
"joo"
Date:
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
join question - requesting for a simple C program where it can INSERT data into database as reference
From
Christoph Haller
Date:
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