[SQL] Coalesce/Join/Entries may not exist.
I've got three tables, I'll shorten the columns down just so you get the idea: lists --- id|order_id list_results id|lid|total orders id|max All of the columns are int's. What I'm trying to do is something like: select (o.max-coalesce(sum(lr.total),0)) from orders o,list_results lr where lr.l_id in (select l.id from lists l, orders o where l.order_id=X and o.id=l.order_id) group by o.max This would, in theory, return a number which should be o.total-sum(lr.total) The problem is, there may not be any data in list_results OR lists regarding the order ID. If data from list_results.total exists, and is referencing lists.id, which in turn is referencing orders.id through lists.order_id, return o.max-lr.total. If data from list_results or lists DOESN'T exist, I would just want to go ahead and return orders.max. I was hoping the coalesce would be able to do this, but it doesn't. The subquery is in there because frankly I'm not sure how to do multiple left joins, which I think would have to exist. The easy way out for me here I think would be to make list_results.order_id and leave lists out of it, but then I'd have redundant data in two tables. Any suggestions on this one? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Label Security
I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran into something I've never seen before (honestly, due to my lack of experience in Oracle) and was curious if Postgres supported anything similar. The DBA that set up Oracle appears to have enabled Oracle Label Security, which looks as though it offers per-row security levels. So, say we have the table 'test', user 'Nancy' does a "select * from test" and only will be shown rows she has permission to. Joe will get the same, and the superuser can see everything. Does Postgres offer anything like this, maybe even through third party software ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Label Security
Ok, so for example, say I add another column to the tables I want to have the row-level security on called 'security'. I would go ahead and designate different security levels for each user, (ex. Jane security 1000, Bill 2000, Joe 3000). Then, if I only want X user with security 1000 to view Y row, I set Y.security to 1000. Then, I give these users no access to the table, and create views for EACH user saying something to the effect of "select * from z where security=securitylevel", and grant access to the views only to the user itself. Couldn't a user then go into the console themselves and create a view giving them full access to the table? Or, maybe I'm way off on this whole thing On Jan 26, 2004, at 1:06 PM, Bruno Wolff III wrote: On Mon, Jan 26, 2004 at 12:45:40 -0800, James Taylor <[EMAIL PROTECTED]> wrote: I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran into something I've never seen before (honestly, due to my lack of experience in Oracle) and was curious if Postgres supported anything similar. The DBA that set up Oracle appears to have enabled Oracle Label Security, which looks as though it offers per-row security levels. So, say we have the table 'test', user 'Nancy' does a "select * from test" and only will be shown rows she has permission to. Joe will get the same, and the superuser can see everything. Does Postgres offer anything like this, maybe even through third party software You can do this with views, but there isn't a turn key set up to do this. You can give someone access to a view without giving them direct access to underlying tables. A view can check the current username versus some data in the table being displayed (perhaps joined with some other tables that keep track of group membership). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
