[SQL] List table with same column name
Hi! How do I list all the tables in the database which has a same column name?. Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > Hi! > How do I list all the tables in the database which has a same column name?. SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 and t1.relname > Thanks > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > Hi! > > How do I list all the tables in the database which has a same column name?. > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > and t1.relname0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 and t1.relname Dropped Columns needed for 7.3. 2> Do you really need to know that column a in table 1 also appears in table 1? Peter Childs > > > > > Thanks > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 3: 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] List table with same column name
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > Hi! > > > How do I list all the tables in the database which has a same column name?. > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > and t1.relname > That will not work. > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > and a2.attnum>0 and t1.relname a1.attisdropped=false and a2.attisdropped=false and t1.relname != > t2.relname; > > Why? > > Well two bugs. > 1> Dropped Columns needed for 7.3. > 2> Do you really need to know that column a in table 1 also appears in > table 1? Have you ever thought that x > Peter Childs > > > > > > > > > Thanks > > > > > > > > > ---(end of broadcast)--- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > -- > > -Achilleus > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > On Thu, 23 Oct 2003, Peter Childs wrote: > > > > > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > > > Hi! > > > > How do I list all the tables in the database which has a same column name?. > > > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > > and t1.relname > > > That will not work. > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > > and a2.attnum>0 and t1.relname > a1.attisdropped=false and a2.attisdropped=false and t1.relname != > > t2.relname; > > > > Why? > > > > Well two bugs. > > 1> Dropped Columns needed for 7.3. > > 2> Do you really need to know that column a in table 1 also appears in > > table 1? > > Have you ever thought that x > > > > Peter Childs > > > > > > > > > > > > > Thanks > > > > > > > > > > > > ---(end of broadcast)--- > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > -- > > > -Achilleus > > > > > > > > > ---(end of broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > -- > -Achilleus > > ---(end of broadcast)--- TIP 3: 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] List table with same column name
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > On Thu, 23 Oct 2003, Peter Childs wrote: > > > > > > > > > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > > > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > > > > > Hi! > > > > > How do I list all the tables in the database which has a same column name?. > > > > > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > > > and t1.relname > > > > > That will not work. > > > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > > > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > > > and a2.attnum>0 and t1.relname > > a1.attisdropped=false and a2.attisdropped=false and t1.relname != > > > t2.relname; > > > > > > Why? > > > > > > Well two bugs. > > > 1> Dropped Columns needed for 7.3. > > > 2> Do you really need to know that column a in table 1 also appears in > > > table 1? > > > > Have you ever thought that x > Yes but when I tested it due to pure intrest to told me that > column a in table 1 also appeared in table 1. Most strange. By adding x!= you probably influenced the ordering of the rows, which gave you the impression of "solving" the "bug", while actually the "bug" in the first place, was an effect of a too large xterm that gives the illusion of a left item to be the same as the right item of a adjacent line. Now seriously, if the meaning of x > Peter Childs > > > > > > > > > Peter Childs > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > ---(end of broadcast)--- > > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > > > > -- > > > > -Achilleus > > > > > > > > > > > > ---(end of broadcast)--- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > -- > > -Achilleus > > > > > > ---(end of broadcast)--- > TIP 3: 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 > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Fw: Error message during compressed backup
Dear Friends, While doing compressed backup for one of the database running at Postgres Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup other items --Command to backup $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz -u -F c --Error msg WARNING: owner of function "plpgsql_call_handler" appears to be invalid Could anyone tell me why I am getting this. I could able to do normal back and restore. Please shed some light. Regards Kumar
Re: [SQL] Fw: Error message during compressed backup
On Thursday 23 October 2003 10:57, Kumar wrote: > Dear Friends, > > While doing compressed backup for one of the database running at Postgres > Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup > other items > > --Command to backup > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f > /home/db_repository/testdb20031023.sql.tar.gz -u -F c > > --Error msg > WARNING: owner of function "plpgsql_call_handler" appears to be invalid In psql, do: \df+ plpgsql* This will show you the owner (among other details) - it should probably be "postgres". -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Left outer join and sub queries alias
Hello, I try to make work this request: select * from ( select * from personne_nom where personne_nom_pal=1 ) as tmp, personne left outer join tmp on ( personne.personne_id = tmp.personne_nom_personne_id ) where personne_id=57 The error on execution is: ERROR: Relation "tmp" does not exist Has anyone already had this problem? Thanks Thierry Les informations contenues dans ce message sont confidentielles et peuvent constituer des informations privilegiees. Si vous n etes pas le destinataire de ce message, il vous est interdit de le copier, de le faire suivre, de le divulguer ou d en utiliser tout ou partie. Si vous avez recu ce message par erreur, merci de le supprimer de votre systeme, ainsi que toutes ses copies, et d en avertir immediatement l expediteur par message de retour. Il est impossible de garantir que les communications par messagerie electronique arrivent en temps utile, sont securisees ou denuees de toute erreur ou virus. En consequence, l expediteur n accepte aucune responsabilite du fait des erreurs ou omissions qui pourraient en resulter. --- - --- The information contained in this e-mail is confidential. It may also be legally privileged. If you are not the addressee you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return e-mail. E-mail communications cannot be guaranteed to be timely secure, error or virus-free. The sender does not accept liability for any errors or omissions which arise as a result. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Left outer join and sub queries alias
On Thursday 23 October 2003 12:37, [EMAIL PROTECTED] wrote: > Hello, > > I try to make work this request: Try: select * from ( select * from personne_nom where personne_nom_pal=1 ) as tmp, left outer join personne on ( personne.personne_id = tmp.personne_nom_personne_id ) where personne_id=57 -- Richard Huxton Archonet Ltd ---(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] Query planner: current_* vs. explicit date
Thanks Tom (and others!) Right-on-the-money, as always... By giving it a definitive range I was able to coax query planner to use the index: SELECT id FROM trans_table WHERE trans_date >= (SELECT current_date::timestamptz) AND trans_date < (SELECT current_timestamp); gave me from midnight to the present... Aside from a slight amount of ugliness, the solution is quite adequate. The subselects shouldn't cause too much overhead, yes? BTW, This didn't work: SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND trans_date < current_timestamp; Which was a "nonconstant" version of the above. I think it still suffers from the timestamp >= unknown_value problem. CG --- Tom Lane <[EMAIL PROTECTED]> wrote: > being careful that both comparison values are nonconstant (don't use > 'infinity'::timestamp, for instance, even though that might seem like > a reasonable thing to do). The planner still has no idea how many rows > will be fetched exactly, but it does realize that this is a range > condition, and its default assumption about the number of matching rows > is small enough to encourage indexscan use. > > Of course this workaround assumes that you can pick an upper bound that > you are sure is past the end of the available values, but that's usually > not hard in the sort of context where you would have thought that the > one-sided inequality test is a sane thing to do anyway. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(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] Réf. : Re: [SQL] Left outer join
On Thursday 23 October 2003 14:39, [EMAIL PROTECTED] wrote: > Hello, > > It doesn't work... You don't say how. By the way, please try to reply to the list as well as directly to the person. > Have you examples on left outer join on a table that is > a part of a table? THanks, richardh=# select * from ta; a | b ---+- 1 | aaa 2 | bbb 3 | ccc (3 rows) richardh=# select * from tb; c | d ---+- 1 | fff 3 | ggg (2 rows) richardh=# SELECT a,b,c,d FROM (SELECT a,b FROM ta) AS one LEFT JOIN (SELECT c,d FROM tb) AS two ON a=c; a | b | c | d ---+-+---+- 1 | aaa | 1 | fff 2 | bbb | | 3 | ccc | 3 | ggg (3 rows) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Regular expression problem
Hi... I'm dealing with a regular expression in a check constraint for
many days i'm stuck with this...
what I'm doing is adding a check to an existing table on a field called
codigoex1 (varchar(9) )
check (codigoex1 ~* '[a-z]{2,2}')
and I can't get it to work!
I want to validate only input data such as "ar" "Us" "bR" and NOT
something like "rum" "a"
the previous reg. expr. only works like it were ~* '[a-z]{2}' , so, it
ignores the maximum length.
Tanks a lot!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Query Help
I'm interested in finding the minimim A.id such that the following holds: select A.charge , B.user_id , C.employee_id from A inner join B using (user_id) inner join C using (employee_id) except select X.charge , Y.user_id , Z.employee_id from X inner join Y using (user_id) inner join Z using (employee_id) -- I can't do the following, since the number of selected columns have to match: select A.id , A.charge , B.user_id , C.employee_id from A inner join B using (user_id) inner join C using (employee_id) except select X.charge , Y.user_id , Z.employee_id from X inner join Y using (user_id) inner join Z using (employee_id) Can someone help me with the query? Thanks in advance. __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Regular expression problem
Matias Surdi <[EMAIL PROTECTED]> writes:
> Hi... I'm dealing with a regular expression in a check constraint for many
> days i'm stuck with this...
>
> what I'm doing is adding a check to an existing table on a field called
> codigoex1 (varchar(9) )
>
> check (codigoex1 ~* '[a-z]{2,2}')
>
> and I can't get it to work!
> I want to validate only input data such as "ar" "Us" "bR" and NOT something
> like "rum" "a"
Use something like '^[a-z]{2}$'
Regards,
Manuel.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Query Help
On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > I'm interested in finding the minimim A.id such that the following holds: > > select A.charge > , B.user_id > , C.employee_id > from A > inner join B using (user_id) > inner join C using (employee_id) > > except > > select X.charge > , Y.user_id > , Z.employee_id > from X > inner join Y using (user_id) > inner join Z using (employee_id) > > -- > > I can't do the following, since the number of selected columns have to match: > > select A.id > , A.charge > , B.user_id > , C.employee_id > from A > inner join B using (user_id) > inner join C using (employee_id) > > except > > select X.charge > , Y.user_id > , Z.employee_id > from X > inner join Y using (user_id) > inner join Z using (employee_id) Maybe you can add a dummy field in the second half like this: except select -1 , X.charge , Y.user_id , Z.employee_id ??? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Error message during compressed backup
Dear Friends, While doing compressed backup for one of the database running at Postgres Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup other items --Command to backup $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz -u -F c --Error msg WARNING: owner of function "plpgsql_call_handler" appears to be invalid Could anyone tell me why I am getting this. I could able to do normal back and restore. Please shed some light. Regards Kumar
[SQL] Query Help using Except
I'm interested in finding the minimim A.id such that the following holds: select A.charge , B.user_id , C.employee_id from A inner join B using (user_id) inner join C using (employee_id) except select X.charge , Y.user_id , Z.employee_id from X inner join Y using (user_id) inner join Z using (employee_id) -- I can't do the following, since the number of selected columns have to match: select A.id , A.charge , B.user_id , C.employee_id from A inner join B using (user_id) inner join C using (employee_id) except select X.charge , Y.user_id , Z.employee_id from X inner join Y using (user_id) inner join Z using (employee_id) Can someone help me with the query? Thanks in advance. __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 ---(end of broadcast)--- TIP 3: 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
[SQL] A tricky sql-query...
We have a small association and the association has a cabin. Members of the association can rent a term to stay in the cabin but as the cabin has turned out to be very famous we have had to establish an application policy for that. It goes like this: 1. There's a seniority queue for this purpose (once you've got a term you'll be placed in the last position in the queue) 2. Members can apply for one or more of the terms 3. The top one member in this seniority queue gets the term he applies. 4. The second member in the queue gets the term he primarly applies unless it's not being taken by the first member. If this is the case then take his secondary quest. 5. The third member gets the term he's primarly applied unless it's not being taken by the first or the second applicant. If it is then try his secondary application. If that's taken as well then try his 3rd quest (if he has such) 6. and so on.. So, (if you didn't understand anything it's OK, pardon my poor English) if I have a table for the applies: CREATE TABLE apply_demo ( memberid integer, sen integer, priority integer, termid integer ); INSERT INTO apply_demo VALUES (2041, 115, 1, 15); INSERT INTO apply_demo VALUES (2041, 115, 2, 18); INSERT INTO apply_demo VALUES (2041, 115, 3, 19); INSERT INTO apply_demo VALUES (206, 120, 1, 13); INSERT INTO apply_demo VALUES (6571, 184, 1, 16); INSERT INTO apply_demo VALUES (123340, 213, 1, 4); INSERT INTO apply_demo VALUES (123340, 213, 2, 16); INSERT INTO apply_demo VALUES (123340, 213, 3, 9); INSERT INTO apply_demo VALUES (152946, 301, 1, 5); INSERT INTO apply_demo VALUES (152880, 302, 1, 13); INSERT INTO apply_demo VALUES (152880, 302, 2, 14); INSERT INTO apply_demo VALUES (181333, 332, 1, 17); INSERT INTO apply_demo VALUES (242502, 462, 1, 9); INSERT INTO apply_demo VALUES (246024, 473, 1, 18); INSERT INTO apply_demo VALUES (246024, 473, 2, 19); INSERT INTO apply_demo VALUES (246024, 473, 3, 13); INSERT INTO apply_demo VALUES (245954, 475, 1, 11); INSERT INTO apply_demo VALUES (245954, 475, 2, 12); INSERT INTO apply_demo VALUES (245954, 475, 3, 16); INSERT INTO apply_demo VALUES (245954, 475, 4, 8); INSERT INTO apply_demo VALUES (152972, 510, 1, 13); INSERT INTO apply_demo VALUES (152972, 510, 2, 4); INSERT INTO apply_demo VALUES (152972, 510, 3, 16); INSERT INTO apply_demo VALUES (152972, 510, 4, 22); INSERT INTO apply_demo VALUES (152972, 510, 5, 2); INSERT INTO apply_demo VALUES (254085, 537, 1, 8); INSERT INTO apply_demo VALUES (288842, 640, 1, 8); I'd need to get out something like this: termid | gotby + 2 | 3 | 4 | 123340 5 | 152946 6 | 7 | 8 | 254085 9 | 242502 10 | 11 | 245954 12 | 13 |206 14 | 152880 15 | 2041 16 | 6571 17 | 181333 18 | 246024 19 | 20 | 21 | 22 | 152972 (21 rows) I know you Gurus are busy and as you are, don't spend too much time on this because it has already been implemented with PL/PgSQL. But just out of the curiosity - and for the educational purposes :) - I'd like to know whether you can do this with a single sql-query? You can't have any recursion in an pure sql-query, can you? Regards, Timo ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] help on update subselect with joins
I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid, then an expanded version that will work on all orderids with a specific customerinvoiceid as a parameter. I'm sure appropriate joins will handle it, but I'm not making any headway, everything comes back with multiple tuple selected for update errors. Any help would be greatly appreciated. Thanks. UPDATE ordercharges INNER JOIN orders ON orders.orderid = ordercharges.orderid SET orderchargeasbilled = (SELECT .065*orderchargeasbilled FROM ordercharges WHERE ordercharges.orderid='123456' AND orderchargecode = 'SALE') WHERE ordercharges.orderchargecode='S&H' AND ordercharges.orderid = '123456' (additional join and where for customerinvoiceid omitted/not attempted yet) orders: +-orderid | customerinvoiceid | (...) | | ordercharges: | orderchargeid +---orderid orderchargeasbilled (...) To Illustrate, I [ordercharges]- orderchargeid | orderid | orderchargecode | orderchargeasbilled --- 1 123456SALE 10.00 2 123456S&H (update) 3 123457SALE 15.00 4 123457EXPEDITE 5.00 5 123457S&H (update) 6 123458SALE 20.00 7 123458S&H (update) 8 123459SALE 10.00 9 123459S&H (update) --- [orders]--- orderid | customerinvoiceid --- 12345654321 12345754321 12345854321 12345955543 --- (e.g. use 54321 as parameter to update 3 S&H rows in 3 orders, but not 1 S&H row in order 123459) ---(end of broadcast)--- TIP 3: 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] Query Help
Wouldn't that return every A.id ? since A.id would be compared to -1, and the wouldnt' be the same. I want: select min(A.id) such that A.charge, B.userid, C.employee_id in ( select A.charge , B.user_id , C.employee_id from A inner join B using (user_id) inner join C using (employee_id) except select X.charge , Y.user_id , Z.employee_id from X inner join Y using (user_id) inner join Z using (employee_id) ) "scott.marlowe" <[EMAIL PROTECTED]> wrote: >On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > >> I'm interested in finding the minimim A.id such that the following holds: >> >> select A.charge >> , B.user_id >> , C.employee_id >> from A >> inner join B using (user_id) >> inner join C using (employee_id) >> >> except >> >> select X.charge >> , Y.user_id >> , Z.employee_id >> from X >> inner join Y using (user_id) >> inner join Z using (employee_id) >> >> -- >> >> I can't do the following, since the number of selected columns have to match: >> >> select A.id >> , A.charge >> , B.user_id >> , C.employee_id >> from A >> inner join B using (user_id) >> inner join C using (employee_id) >> >> except >> >> select X.charge >> , Y.user_id >> , Z.employee_id >> from X >> inner join Y using (user_id) >> inner join Z using (employee_id) > >Maybe you can add a dummy field in the second half like this: > >except > > select -1 > , X.charge > , Y.user_id > , Z.employee_id > >??? > > >---(end of broadcast)--- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
