Re: Join question - Mailing list pgsql-sql
| From | Edward W. Rouse |
|---|---|
| Subject | Re: Join question |
| Date | |
| Msg-id | 064501c90200$9baa61e0$d2ff25a0$@com Whole thread Raw |
| In response to | Re: Join question ("Oliveiros Cristina" <oliveiros.cristina@marktest.pt>) |
| List | pgsql-sql |
I thought of that, but it does violate table constraints.
Edward W. Rouse
From: Oliveiros Cristina [mailto:oliveiros.cristina@marktest.pt]
Sent: Monday, August 18, 2008 2:00 PM
To: pgsql-sql@postgresql.org; erouse@comsquared.com
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
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" [erouse@comsquared.com]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join questionI 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