[SQL] join question
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.
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.
Thanks in advance!
--->Nathan
---
(([^/]+)/([^/]+)){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
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]
[SQL] transactions within stored procedures
Hi. Please correct me if this is off topic for the group. On looking through the available postgres mailing lists, this seemed the most appropriate for my question. Transactions are important to my application. I was scripting in perl with autocommit turned off, issuing commits when appropriate. As I moved more of my logic into stored procedures, I looked for documentation on how transactions work in stored procedures and I couldn't find any! I'm using pl/pgsql right now but would happily use pg/perl... By trial and error I established that a SP wraps everything it does in one transaction, including anything from stored procedures it calls. So here is my question: Is there any way for me to control when transactions are committed or rolled back within stored procedures? Is there an equivalent to turning autocommit off, in other words doing each statement in its own transaction? Or is there a command to let me commit whenever I want to? Also, can I roll back a transaction based on programmatically defined logic, or only on a database error? Thanks in advance... ->Nathan -- ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ Nathan Young [EMAIL PROTECTED] (805) 686-2830
[SQL] synchronizing databases
Hi. Again, I'm posting to this list because none of the other postgres lists seem quite right for the topic. Excuse me if I'm off topic. I'm writing an application and I'd like to have the data that application uses to be uninterrupted in case the database server fails (fail over). Likewise, when I'm ready to bring the crashed server back online I'll need to be able to synchronize it with the one that's been working so that I can go back to having fail over capability. I know that oracle has a fail safe DB server, MsSQL has some form of replication, and I can do it on the hardware level with a mirrored drive array. I'd like to avoid shelling out the cash for these solutions and was exploring the potential for an application/database layer solution. I'd also gladly accept open source solutions like linux software raid or clustering from anyone who's used them in this context! I've thought of a couple possible solutions. I'm sure that this problem has been tackled before and I'd appreciate feedback from anyone with experience. In the first scenario, I make the update, and also write a log entry that will allow me to re-create that update. I do this as a transaction first on server x, then on server y. The log entry has a key field that allows me to check server x's log against that of server y, and if discrepancies exist, I can bring the server that is missing the transaction up to date with a synchronization program that runs periodically. There are problems with this. I have to return success after x has been updated even if the update to y fails, because the update HAS happened, and under normal conditions y will get synchronized. But if x crashes immediately afterwards, then y will never show that the update happened, but the outside world will have seen it as a success. A more complex solution is to open the transaction on x then update the record in y with a flag that shows that an update MIGHT have occurred. If that update to y succeeds, then I can commit the transaction on x (at this point I must return success to the outside world) and start another transaction on y. This time I synchronize y with x and remove the flag in one transaction. If x crashes at any time, transactions are rejected until the system fails over to y. If y hiccups during a transaction on x so that y doesn't get flagged, then I return failure and don't commit to x. If my application crashes before synchronizing y and x fails right after that, then I have to freeze all records in y that have flags until I can get their real status back from x (if that's recoverable) or I'm left with orphans that I'll have to resolve using real world info. Meanwhile I have scripts that can create a new y from an x, which I use in case y goes down or in case x goes down and y has to BECOME x. Is there a more elegant solution to this? Thanks in advance! --->Nathan -- ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ Nathan Young [EMAIL PROTECTED] (805) 686-2830
