Re: [SQL] Join question
Finally got it to work. I used 2 separate selects and a union. So one of the selects was like my original left outer joined select and then I unioned it with one that got the missed nulls from the other table. Thanks for all the advice. Edward W. Rouse -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent: Tuesday, August 19, 2008 2:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I was trying to do something like this, but couldn't get it to work. I am trying to follow the example you provided, but don't understand how id and oid relate to the example tables and which table is pr1 and pr2. Also my data has to match 2 constraints, not 1 (though I'm guessing that I could just add the other without changing anything else). And you have pr2.dato in the inner select but not the outer one. Is there a reason for that. As of now I am thinking I will have to break this up into more than one statement. Edward W. Rouse -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lennin Caro Sent: Tuesday, August 19, 2008 11:59 AM To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse Subject: Re: [SQL] Join question --- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > From: Edward W. Rouse <[EMAIL PROTECTED]> > Subject: Re: [SQL] Join question > To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org > Date: Tuesday, August 19, 2008, 1:35 PM > I have tried left, right outer and inner. > > > > Edward W. Rouse > > > > From: Daniel Hernandez [mailto:[EMAIL PROTECTED] > Sent: Monday, August 18, 2008 12:30 PM > To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] > Subject: Re: [SQL] Join question > > > > have you tried a right Join? > > > Daniel Hernndez. > San Diego, CA. > "The more you learn, the more you earn". > Fax: (808) 442-0427 > > > -Original Message- > From: "Edward W. Rouse" [EMAIL PROTECTED] > Date: 08/15/2008 09:48 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Join question > > I have 2 tables, both have a user column. I am currently > using a left join from table a to table b because I need to > show all users from table a even those not having an entry > in table b. The problem is I also have to include items from > table b with that have a null user. There are some other > criteria as well that are simple where clause filters. So as > an example: > > > > Table a: > > Org|user > > A| emp1 > > B| emp1 > > B| emp2 > > B| emp3 > > C| emp2 > > > > Table b: > > Org|user|color > > A |emp1|red > > A |emp1|blue > > A |null|pink > > A |null|orange > > B |emp1|red > > B |emp3|red > > B |null|silver > > C |emp2|avacado > > > > If I: > > > > select org, user, count(total) > > from a left join b > > on (a.org = b.org and a.user = b.user) > > where a.org = ‘A’ > > group by a.org, a.user > > order by a.org, a.user > > > > I get: > > > > Org|user|count > > A|emp1|2 > > A|emp2|0 > > A|emp3|0 > > > > But what I need is: > > > > A|emp1|2 > > A|emp2|0 > > A|emp3|0 > > A|null|2 > > > > Thanks, > > Edward W. Rouse also like this... select id1,dato1, count(id2) from ( select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right outer join pr2 on (pr1.id = pr2.oid) ) a group by id1,dato1 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
I was trying to do something like this, but couldn't get it to work. I am trying to follow the example you provided, but don't understand how id and oid relate to the example tables and which table is pr1 and pr2. Also my data has to match 2 constraints, not 1 (though I'm guessing that I could just add the other without changing anything else). And you have pr2.dato in the inner select but not the outer one. Is there a reason for that. As of now I am thinking I will have to break this up into more than one statement. Edward W. Rouse -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lennin Caro Sent: Tuesday, August 19, 2008 11:59 AM To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse Subject: Re: [SQL] Join question --- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > From: Edward W. Rouse <[EMAIL PROTECTED]> > Subject: Re: [SQL] Join question > To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org > Date: Tuesday, August 19, 2008, 1:35 PM > I have tried left, right outer and inner. > > > > Edward W. Rouse > > > > From: Daniel Hernandez [mailto:[EMAIL PROTECTED] > Sent: Monday, August 18, 2008 12:30 PM > To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] > Subject: Re: [SQL] Join question > > > > have you tried a right Join? > > > Daniel Hernndez. > San Diego, CA. > "The more you learn, the more you earn". > Fax: (808) 442-0427 > > > -Original Message- > From: "Edward W. Rouse" [EMAIL PROTECTED] > Date: 08/15/2008 09:48 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Join question > > I have 2 tables, both have a user column. I am currently > using a left join from table a to table b because I need to > show all users from table a even those not having an entry > in table b. The problem is I also have to include items from > table b with that have a null user. There are some other > criteria as well that are simple where clause filters. So as > an example: > > > > Table a: > > Org|user > > A| emp1 > > B| emp1 > > B| emp2 > > B| emp3 > > C| emp2 > > > > Table b: > > Org|user|color > > A |emp1|red > > A |emp1|blue > > A |null|pink > > A |null|orange > > B |emp1|red > > B |emp3|red > > B |null|silver > > C |emp2|avacado > > > > If I: > > > > select org, user, count(total) > > from a left join b > > on (a.org = b.org and a.user = b.user) > > where a.org = ‘A’ > > group by a.org, a.user > > order by a.org, a.user > > > > I get: > > > > Org|user|count > > A|emp1|2 > > A|emp2|0 > > A|emp3|0 > > > > But what I need is: > > > > A|emp1|2 > > A|emp2|0 > > A|emp3|0 > > A|null|2 > > > > Thanks, > > Edward W. Rouse also like this... select id1,dato1, count(id2) from ( select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right outer join pr2 on (pr1.id = pr2.oid) ) a group by id1,dato1 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
--- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > From: Edward W. Rouse <[EMAIL PROTECTED]> > Subject: Re: [SQL] Join question > To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org > Date: Tuesday, August 19, 2008, 1:35 PM > I have tried left, right outer and inner. > > > > Edward W. Rouse > > > > From: Daniel Hernandez [mailto:[EMAIL PROTECTED] > Sent: Monday, August 18, 2008 12:30 PM > To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] > Subject: Re: [SQL] Join question > > > > have you tried a right Join? > > > Daniel Hernndez. > San Diego, CA. > "The more you learn, the more you earn". > Fax: (808) 442-0427 > > > -Original Message- > From: "Edward W. Rouse" [EMAIL PROTECTED] > Date: 08/15/2008 09:48 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Join question > > I have 2 tables, both have a user column. I am currently > using a left join from table a to table b because I need to > show all users from table a even those not having an entry > in table b. The problem is I also have to include items from > table b with that have a null user. There are some other > criteria as well that are simple where clause filters. So as > an example: > > > > Table a: > > Org|user > > A| emp1 > > B| emp1 > > B| emp2 > > B| emp3 > > C| emp2 > > > > Table b: > > Org|user|color > > A |emp1|red > > A |emp1|blue > > A |null|pink > > A |null|orange > > B |emp1|red > > B |emp3|red > > B |null|silver > > C |emp2|avacado > > > > If I: > > > > select org, user, count(total) > > from a left join b > > on (a.org = b.org and a.user = b.user) > > where a.org = ‘A’ > > group by a.org, a.user > > order by a.org, a.user > > > > I get: > > > > Org|user|count > > A|emp1|2 > > A|emp2|0 > > A|emp3|0 > > > > But what I need is: > > > > A|emp1|2 > > A|emp2|0 > > A|emp3|0 > > A|null|2 > > > > Thanks, > > Edward W. Rouse also like this... select id1,dato1, count(id2) from ( select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right outer join pr2 on (pr1.id = pr2.oid) ) a group by id1,dato1 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
Already tried making two queries and then outputting the UNION of the results? The second one could be something like this... SELECT org,null,COUNT(color) FROM b WHERE user IS NULL AND org = 'a' GROUP BY org Best, Oliveiros - Original Message - From: Edward W. Rouse To: pgsql-sql@postgresql.org Sent: Tuesday, August 19, 2008 2:36 PM Subject: Re: [SQL] Join question I thought of that, but it does violate table constraints. Edward W. Rouse From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 2:00 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question I don't understand your count(total) expression... It doesnt work, because apparently you dont have any "total" column... Apparently, you meant count(color) The problem is that you are grouping by a.org,a.user and on table "a" u actually dont have any "null" users... Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then substitute your LEFT OUTER JOIN condition by this one : from a left join b on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null ))) Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ... I know This is not a very elegant solution, but seems to give the results you need Best, Oliveiros - Original Message - From: Daniel Hernandez To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Monday, August 18, 2008 5:30 PM Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. "The more you learn, the more you earn". Fax: (808) 442-0427 -Original Message- From: "Edward W. Rouse" [EMAIL PROTECTED] Date: 08/15/2008 09:48 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = ‘A’ group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
I have tried left, right outer and inner. Edward W. Rouse From: Daniel Hernandez [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 12:30 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. "The more you learn, the more you earn". Fax: (808) 442-0427 -Original Message- From: "Edward W. Rouse" [EMAIL PROTECTED] Date: 08/15/2008 09:48 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = ‘A’ group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
I thought of that, but it does violate table constraints. Edward W. Rouse From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 2:00 PM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] Join question I don't understand your count(total) expression... It doesnt work, because apparently you dont have any "total" column... Apparently, you meant count(color) The problem is that you are grouping by a.org,a.user and on table "a" u actually dont have any "null" users... Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then substitute your LEFT OUTER JOIN condition by this one : from a left join b on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null ))) Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ... I know This is not a very elegant solution, but seems to give the results you need Best, Oliveiros - Original Message - From: Daniel Hernandez <mailto:[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Monday, August 18, 2008 5:30 PM Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. "The more you learn, the more you earn". Fax: (808) 442-0427 -Original Message- From: "Edward W. Rouse" [EMAIL PROTECTED] Date: 08/15/2008 09:48 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = ‘A’ group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
I don't understand your count(total) expression... It doesnt work, because apparently you dont have any "total" column... Apparently, you meant count(color) The problem is that you are grouping by a.org,a.user and on table "a" u actually dont have any "null" users... Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then substitute your LEFT OUTER JOIN condition by this one : from a left join b on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null ))) Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ... I know This is not a very elegant solution, but seems to give the results you need Best, Oliveiros - Original Message - From: Daniel Hernandez To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Monday, August 18, 2008 5:30 PM Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. "The more you learn, the more you earn". Fax: (808) 442-0427 -Original Message- From: "Edward W. Rouse" [EMAIL PROTECTED] Date: 08/15/2008 09:48 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = ‘A’ group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
have you tried a right Join?Daniel Hernndez.San Diego, CA."The more you learn, the more you earn".Fax: (808) 442-0427-Original Message-From: "Edward W. Rouse" [EMAIL PROTECTED]: 08/15/2008 09:48 AMTo: [EMAIL PROTECTED]: Re: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A | emp1 B | emp1 B | emp2 B | emp3 C | emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = ‘A’ group by a.org, a.user order by a.org, a.user I get: Org|user|count A |emp1|2 A |emp2|0 A |emp3|0 But what I need is: A |emp1|2 A |emp2|0 A |emp3|0 A |null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
At 12:20 PM 8/15/2008, [EMAIL PROTECTED] wrote: Date: Fri, 15 Aug 2008 13:46:14 -0400 From: "Edward W. Rouse" <[EMAIL PROTECTED]> To: Subject: Re: Join question Message-ID: <[EMAIL PROTECTED]> I did try that, but I can't get both the values from table a with no entries in table b and the values from table b with null entries to show up. It's either one or the other. Edward W. Rouse Might have luck with applying some additional WHERE clause criteria to your full outer join. So if you don't want certain types NULL's in table b, restrict against that in WHERE clause? I could be misunderstanding the whole thing though.. Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
I did try that, but I can't get both the values from table a with no entries in table b and the values from table b with null entries to show up. It's either one or the other. Edward W. Rouse -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Broersma Sent: Friday, August 15, 2008 1:10 PM To: Edward W. Rouse Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > The problem is I also have to include > items from table b with that have a null user. There are some other criteria > as well that are simple where clause filters. So as an example: instead of left join try FULL OUTER JOIN. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > The problem is I also have to include > items from table b with that have a null user. There are some other criteria > as well that are simple where clause filters. So as an example: instead of left join try FULL OUTER JOIN. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
Sigh, I messed up the tables a bit when I typed the example, org A was supposed to have entries for all 3 users in table a just like org B does, not just the one. Sorry for the confusion. Edward W. Rouse From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent: Friday, August 15, 2008 12:48 PM To: pgsql-sql@postgresql.org Subject: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = 'A' group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
On Aug 21, 2007, at 12:48 , [EMAIL PROTECTED] wrote: SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = c.b_id GROUP by a.x; Shouldn't affect performance, but another way to write this which you may find more readable is to list your join conditions with the joins rather than grouping them all in the WHERE clause: SELECT a.x, max(b.x) FROM a JOIN c USING (a_id) JOIN b USING (b_id) GROUP BY a.x; You can also write this using NATURAL JOIN which joins on like-named columns. SELECT a.x, max(b.x) FROM a NATURAL JOIN c JOIN b USING (b_id) GROUP BY a.x; You can't use NATURAL JOIN to join b because you've got columns named x in both a and b that you're *not* joining on. Another nice thing about the USING and NATURAL JOIN syntax is that the result includes only one column for the joined columns, rather than two. In this case, there would be only one b_id and one a_id column in the result set. Using ON or putting the join condition in the WHERE clause puts two b_id and two a_id columns in the result set. Does anyone know a way I could restructure this query to get only one b for each a in a faster way? You might want to ask on the pgsql-performance list as well, as people there are generally interested in improving query performance. One thing they'll ask you for is the output of EXPLAIN ANALYZE for your query. Here's an alternative, but I don't know how it'd compare in terms of performance: EXPLAIN ANALYZE SELECT DISTINCT ON (a.x) a.x, b.x FROM a NATURAL JOIN c JOIN b USING (b_id); Give that a shot. (DISTINCT ON is a non-standard PostgreSQL extension, if non-portable syntax is something you're looking to avoid.) EXPLAIN ANALYZE shows you how the planner decided to proceed with the query, which can be useful when comparing alternatives, so you can compare using DISTINCT ON with your own query using max. Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Join question
oops... I meant "DISTINCT ON ( a_id )" --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > SELECT a.x, b.x > FROM ( SELECT DISTINCT ON ( a_id ) a_id, b_id ^^ >FROM c ) AS c( a_id, b_id ) > INNER JOIN a > ON c.a_id = a.id > INNER JOIN b > ON c.b_id = b.id; > > Regards, > Richard Broersma Jr. > > > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Join question
--- [EMAIL PROTECTED] wrote: > create table c ( >a_id int, >b_id int > ); > > I am doing a query like this: > > SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id = > c.b_id GROUP by a.x; > > I only need to get one row from b for each row in a, and it really > doesn't matter which one. I use max() to get a single value from table > b. There are generally be dozens to hundreds of rows in b for each row > in a. The problem is when I have a query with tens of thousands of rows > in a that the join with b will have millions of rows, and is really > slow. The group by effectively reduces the results down to what I want, > but it still has to process the millions of rows. Does anyone know a > way I could restructure this query to get only one b for each a in a > faster way? CREATE INDEX table_c_foreign_key ON c ( a, b ); SELECT a.x, b.x FROM ( SELECT DISTINCT( a_id ) a_id, b_id FROM c ) AS c( a_id, b_id ) INNER JOIN a ON c.a_id = a.id INNER JOIN b ON c.b_id = b.id; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Join question
Phillip Smith wrote: Whoops, I forgot the JOIN conditions! Fixed below -Original Message- From: Phillip Smith [mailto:[EMAIL PROTECTED] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h ON l.invoice_number = h.invoice_number UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS s JOINheaders AS h ON s.invoice_number = h.invoice_number ORDER BY invoice_number, item Forgot all about union - I had two individual views, one for invoice+parts, one for invoice+sundries... didn't think of union to combine the two views together. Perfect solution, you've done it for me again Phillip, thanks. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Join question
Whoops, I forgot the JOIN conditions! Fixed below -Original Message- From: Phillip Smith [mailto:[EMAIL PROTECTED] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h ON l.invoice_number = h.invoice_number UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS s JOINheaders AS h ON s.invoice_number = h.invoice_number ORDER BY invoice_number, item ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Join question
This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS s JOINheaders AS h ORDER BY invoice_number, item ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] JOIN question with multiple records
Scott, Casey wrote: I have 2 tables. One containing information about servers, and the other containing information about IP addresses. E.G. Server table: namemac mac2 - SERVER1 00:0d:56:ba:ad:92 SERVER2 00:0d:56:ba:ad:93 00:0d:56:ba:ad:96 SERVER3 00:0d:56:ba:ad:94 SERVER4 00:0d:56:ba:ad:95 00:0d:56:ba:ad:97 I think you've got the design of this table wrong. It looks like you're leaving mac2 NULL where the server has only one network-card. This is wrong - mac2 is not "unknown" it is "card not present" or similar (and the type of the column should then be not mac-address but mac-address-and-not-present). I'm also not sure how you will handle the case when a server has 3 network-cards. Also, if you want to know which server has a specific mac-addr then you'll need to check two columns with your current design. If possible I'd suggest reworking the table to something like: (name, card-id, mac-addr) and you'd then have: SERVER2 0 00:0d:56:ba:ad:93 SERVER2 1 00:0d:56:ba:ad:96 ... Then a crosstab function / case statement can reformat your query output as required. SELECT servers.name,addresses.ipaddr,servers.application_mgr FROM servers LEFT JOIN addresses ON addresses.mac = servers.mac OR addresses.mac = servers.mac2 Well, if you can't change the structure of your tables you could do something like: SELECT ... FROM servers s LEFT JOIN addresses a1 ON s.mac = a1.mac LEFT JOIN addresses a2 ON s.mac = a2.mac The crucial bit is aliasing the "addresses" table twice. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] join question
On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <[EMAIL PROTECTED]> wrote: >OK, that works great, but I was told that I should avoid sub-selects when >possible for performance reasons. >> >> select member.memberId, member.name from member left outer join >> (select * from payment where yearPaid=2002) as a using (memberId) where >> yearPaid is null; Nathan, if you want a version without a subselect, try SELECT m.memberId, m.name FROM member AS m LEFT OUTER JOIN payment AS p ON p.yearPaid=2002 AND m.memberId=p.memberId WHERE p.memberId IS NULL; though I don't know whether it is faster. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join question
OK, that works great, but I was told that I should avoid sub-selects when
possible for performance reasons. Also, I used so much mental energy trying
to find a solution that would do either task using a join that I would be
very curious if anyone had a solution.
The setup:
>> I have a table with members and a table with payments. Each payment is
>> related to a member by memberID and each payment has (among other things)
>> a year paid.
The problem:
>> I would like to be able to get a list of members who have not paid for a
>> given year.
Two possible solutions, both using sub-selects:
> select member.memberId, member.name from member where not exists (select
> * from payment where payment.memberId=member.memberID and
> payment.yearPaid=2002);
>
> select member.memberId, member.name from member left outer join
> (select * from payment where yearPaid=2002) as a using (memberId) where
> yearPaid is null;
In addition to my interest in finding a join that could do that, I'm curios
about a couple other things.
My understanding is that exists is optimized so that the first version would
be faster than the second.
"using (memberID)" would be the same as "on member.memberID =
payment.memberID", right?
Thanks!
->Nathan
11/26/2002 8:11:53 AM, Stephan Szabo <[EMAIL PROTECTED]> wrote:
>
>On Fri, 22 Nov 2002, Nathan Young wrote:
>
>> Hi all.
>>
>> I have a table with members and a table with payments. Each payment is
>> related to a member by memberID and each payment has (among other things)
a
>> year paid.
>>
>> I can create a join to find a list of members who have paid for a given
year
>> (2002 in this case):
>>
>> select member.memberID,member.name from member, payment where
>> payment.memberID = member.memberID and payment.yearPaid = 2002
>>
>> I would like to be able to get a list of members who have not paid for a
>> given year.
>
>Well, I believe either of these two will do that:
>
> select member.memberId, member.name from member where not exists (select
> * from payment where payment.memberId=member.memberID and
> payment.yearPaid=2002);
>
> select member.memberId, member.name from member left outer join
> (select * from payment where yearPaid=2002) as a using (memberId) where
> yearPaid is null;
>
>> I would also like to combine the two criteria, for example to generate a
list
>> of members who have paid for 2002 but not 2003.
>
>I think these would do that:
>
>select member.memberID,member.name from member, payment where
> payment.memberID = member.memberID and payment.yearPaid = 1999
> and not exists (select * from payment where
> payment.memberId=member.memberId and yearPaid=2002);
>
>select member.memberId, member.name from member inner join (select
> * from payment where yearPaid=2002) as a using (memberId) left outer join
> (select * from payment where yearPaid=2003) as b using (memberId) where
> b.yearPaid is null;
>
>
>
>
---
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
---
Nathan Young
N. C. Young Design
(530)629-4176
http://ncyoung.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join question
On Fri, 22 Nov 2002, Nathan Young wrote: > Hi all. > > I have a table with members and a table with payments. Each payment is > related to a member by memberID and each payment has (among other things) a > year paid. > > I can create a join to find a list of members who have paid for a given year > (2002 in this case): > > select member.memberID,member.name from member, payment where > payment.memberID = member.memberID and payment.yearPaid = 2002 > > I would like to be able to get a list of members who have not paid for a > given year. Well, I believe either of these two will do that: select member.memberId, member.name from member where not exists (select * from payment where payment.memberId=member.memberID and payment.yearPaid=2002); select member.memberId, member.name from member left outer join (select * from payment where yearPaid=2002) as a using (memberId) where yearPaid is null; > I would also like to combine the two criteria, for example to generate a list > of members who have paid for 2002 but not 2003. I think these would do that: select member.memberID,member.name from member, payment where payment.memberID = member.memberID and payment.yearPaid = 1999 and not exists (select * from payment where payment.memberId=member.memberId and yearPaid=2002); select member.memberId, member.name from member inner join (select * from payment where yearPaid=2002) as a using (memberId) left outer join (select * from payment where yearPaid=2003) as b using (memberId) where b.yearPaid is null; ---(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
I think you meant: select profile.name from profile,attribute where ( profile.id = attribute.containerId) and ( profile.state =' 1020811' or ( attribute.name = 'marketsegment' and attribute.value = '1020704'); > select profile.name from profile,attribute where > ((profile.state='1020811') or ((attribute.name='marketsegment') and > (attribute.value='1020704') and (profile.id=attribute.containerId))); > > Why doesn't this last query return just one row? > > TIA > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] join question
On Fri, 18 Oct 2002, Frank Morton wrote: > For the SQL gurus, a query where I'm not getting the expected > results. Trying to write it using sql compatible with both postgres > and mysql. > > There are two tables: > > table = profile > int id > char name > > table = attribute > int id > int containerId > char name > char value > > Multiple attribute rows correspond to a single profile row where > attribute.containerId = profile.id > > These two queries result in one row being returned, which is > the expected result: > > select name from profile where ((profile.state='1020811')); > > select profile.name from profile,attribute where > (((attribute.name='description') and (attribute.value='1020704') and > (profile.id=attribute.containerId))); > > But, I thought this next query would just be a simple way to combine the two > queries with an "or" operator, still returning one row, actually returns > ALL rows of attribute: > > select profile.name from profile,attribute where > ((profile.state='1020811') or ((attribute.name='marketsegment') and > (attribute.value='1020704') and (profile.id=attribute.containerId))); > > Why doesn't this last query return just one row? Because for each combination of rows from profile and attribute where profile.state='1020811' the where clause is statisfied. I'm not sure what you're precisely trying to get out, since unless the row that matches each of the clauses is the same I don't see how you'd only get one row out with an or. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join question - requesting for a simple C program where it can INSERT data into database as reference
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
#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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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] join question - three tables, two with foreign keys to
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 amount from employee as emp, employee_leave as lv where emp.id= lv.employee_id union select emp.name, null, null, pay.amount from 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 NULL NULL NULL. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join question - three tables, two with foreign keys to the first
On Fri, 14 Jun 2002 00:13:22 +1000, "Dmitri Colebatch" <[EMAIL PROTECTED]> 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_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; 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_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: [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])
