Re: [SQL] join question - three tables, two with foreign keys to the first
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? ta again dim - Original Message - From: "Dmitri Colebatch" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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 > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] join question - three tables, two with foreign keys to the first
> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Obtaining primary key information from pg system tables
Kris, I'm assuming you dont want to do this, but just in case you're not aware of the option - the JDBC API exposes this information. hth dim - Original Message - From: Kris To: [EMAIL PROTECTED] Sent: Saturday, June 22, 2002 8:30 PM Subject: [SQL] Obtaining primary key information from pg system tables Hi, I am trying to generate some xml metadata from the pg system tables for my java code generation utility. I have can get most information, but i would like to be able to tell which column is a primary key or which one is a foreign key (i dont need to know which table the foreign key references at this point). Which tables do i need reference, could anyone provide an example query? Kris
