RES: [SQL] Left join?
It´s just want I need! Perfect! Thanks! Carlos > -Mensagem original- > De: Richard Broersma Jr [mailto:[EMAIL PROTECTED] > Enviada em: sábado, 1 de julho de 2006 18:45 > Para: [EMAIL PROTECTED]; pgsql-sql@postgresql.org > Assunto: Re: [SQL] Left join? > > > > In the following table, codsol, codate and codfec are foreign keys > > referencing table func and I need some help to codify a SELECT > command that > > produces the following result set but instead of codsol, codate > and codfec I > > need the respectives names (column nome from table func). > > > > postgres=# select * from reqtran; > > codreq | codsol | codate | codfec > > +++ > > 1 ||| > > 2 | 1 || > > 3 | 1 | 1 | > > 4 | 1 | 1 | 1 > > postgres=# \d func > > > Table "public.func" > > Column | Type | Modifiers > > +-+--- > > codfun | integer | not null > > nome | text| > > > Indexes: > > "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) > > "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) > > "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) > > Would this do what you need? > > select R1.codreq, >CS.nome, >CD.nome, >CF.nome > from rectran as R1 >left join func as CS on (R1.codsol=CS.codefun) >left join func as CD on (R1.codate=CD.codefun) >left join func as CF on (R1.codfec=CF.codefun) > ; > > Regards, > > Richard Broersma Jr. > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to Select in table check constraint
> On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > > FROM BADGES > > > > WHERE STATUS = 'A' > > > > GROUP BY EMPNO)) > > > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > > interactive/sql-createtable.html) > > > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > > ON badges (empno) > > > WHERE status = 'A'; > > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > > > Michael, > > > > Partial indexs seem to be "what the doctor ordered!" And your suggest is > > right on, the idea > of > > the constraint is to allow only one active badge status at a time. > > > > But now that I think about it, using the authors suggestion (if it actually > > worked), how would > > would it be possible to change the active status from one badge to another? > > Unset the status first then set on the new one. Same transaction of > course. > > You may find this type of constraint is more workable with a TRIGGER > deferred until commit time than a unique constraint which cannot (at > this time in PostgreSQL) be deferred. Thanks for the Input Rod. I will try implementing a trigger as a way to constrain the input data to see how it works. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
> This is more of an implementation option, but when I worry about what is > active/inactive I put start/end dates on the tables. Then you don't need > active indicators. You just look for the record where now() is >= start > date and now() <= end date or end date is null. You can even > activate/deactivate a badge on a future date. Of course, this complicates > the data integrity - you will need some kind of specialized trigger that > checks the data and makes sure there are no date overlaps to ensure you > don't have two badges active at the same time. But is also gives you a > history of badges and their activities. Good point. I take it that this type of solution stems from temporal schema design. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] join two tables with sharing some columns between two
Hi, I have two tables like these: (this is an example, the actual tables have diffferent fields and meanings) TABLE1 id person_name date_arrival date_departure TABLE2 id car_description date_arrival date_departure I'd like to make a query to have such resulting table RESULTING_TABLE id person_name car_description date_arrival date_departure the id is the primary key for the three tables and it's unique for the three (id in table1 cannot be in table2, I use the same counter to generate the id for table1 and table2). I is possible to create such a query? Thanks ---(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
[SQL] graph plottin engines compatible with postgres
hey,I wanted to know about graph plotting engine that is compatible with postgres 8.1.4. I am performing some queries like :Comparing the speed of three runners namely A, B and C versus time. I want to publish the result in a graphical manner on the net instead of just providing the data. So, if you could provide me with some information on how i can do it then that would be great.thanks and regardsparang saraf [EMAIL PROTECTED]
[SQL] unique values of profile in the whole system
Hi all, I have a problem to solve and I don't have a long experience with database, please help me!!! We are developing a system for profile management. The simplified schema is composed by three tables: * tbl_user : the users table; contains the unique id of the users and the profile id (only one profile for each user), and some other information * tbl_data_type : contains the data type of the profile, their id and their names. E.g.: id=1, data type name="last name"; id=2, data type name="address", and so on * tbl_data : the data of all the profiles of the system; it has three columns: the id of the profile the data belongs to (linked to the tbl_user), the data type id (linked to tbl_data_type) and the value of the data. E.g.: profile=1, data_type_1=1, value="Smith", and so on We don't know exactly which are all data_types so we separate data and data_type to keep profile flexible. The problem involves the management of the values of the profile that must be unique. Suppose we have a data type named "unique_id", which value should be stored in tbl_data. The value must be unique in the whole system, so the profiles store only one "unique_id", and I have to able to identify a profile by this value(that's why must be unique!). Generating such a unique id it's not a problem, using e.g. a sequence. The problem is the user can change this value accessing to the proper stored procedure, and the system should check that the value chosen do not violate the requirement of uniqueness. I have only two solutions, I'd be glad to hear from you if they are correct, or if you have already encountered similar problems and you can point me to some useful document. The first solution: Using access exclusive lock inside of the stored procedure mentioned before. Since stored procedures make a local copy of the data, each stored procedure accessing to the tbl_data for updating the unique_id would have its own copy of data; so, a different locking strategy should not have the desired effect. However, I think that locks should be avoided if possible. Furthermore, the unique_id should have a slow update rate, so it should not be a big problem, but the exclusive lock would affect the whole system, even the research (SELECT) on tbl_data. The second solution: Using a support table to take advantage of the UNIQUE constraint. I'd have a fourth table, named "tbl_unique_id_support", storing the the unique_id(s) with the unique constraint. With this, if a new unique_id is proposed, I should try to add it to the support table; if the operation fails, the id already exists, so it cannot be added to the tbl_data table. Otherwise, I can safely add it to the tbl_data. Pro: get rid of lock. Con: more memory is required for support table. The system is less flexible, because for each "unique_id"-kind of data, I should have a support table dedicated. Please, do you have any suggestion about that? Thank you regards, Debora ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] SQL (Venn diagram type of logic)
Hello everyone. I have a customer purchase table that keeps track of transaction details. I'm looking sum up total spent by each customer and classify each customer based on which products they bought. Data looks like... account_id, date, product_id, sale_price - cust1, 03/21/2005, prod1, 50 cust1, 03/22/2005, prod4, 35 cust1, 05/08/2005, prod2, 50 cust2, 04/21/2005, prod16, 20 cust3, 04/16/2005, prod1, 50 etc.. Setup: I'm picturing a Venn Diagram in my head but I need help with the decision logic to classify each customer. Example: Category A: Prod1, Prod2 Category B: Prod3, Prod4 Category C: All products Not in Class A or Class B -A customer who has bought Prod1, Prod2 would be in the A only category. -A customer who has bought Prod1, Prod3 would be in the Class A&B category -A customer who has bought Prod18 would be in the C category -A customer who has bought Prod4, Prod16 would be in the B&C category -A customer who has bought Prod1, Prod4, Prod15 would be in the A&B&C category -etc... Then for each comination of categories (7 in total?), i will need of number of accounts in that category and total spent by those accounts. Any help or direction would be greatly appreciated. Thank you in advance. -Vince __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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
[SQL] Can't drop table
Hi, I have created a table called experimenters with the following query: CREATE TABLE experimenters ( experimenter_id INTEGER CONSTRAINT firstkey PRIMARY KEY, first_name CHAR(20), last_nameCHAR(25), address CHAR(30), phone_numCHAR(15) ); The query was successful. But when I try to drop or alter the table, it just hangs without echoing any error message. I have to cancel the query to get out. I ran the following query to drop the table DROP TABLE experimenters; It also hangs when I try to alter the table. Just don't understand the problem here. Any help will be highly appreciated. Thanks in advance Hima ---(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 two tables with sharing some columns between two
If I understand correctly... I think this should work: SELECT table1.id, table1.person_name, table2.car_description, table1.date_arrival, table1.date_departure FROMtable1, table2 WHERE table1.id = table2.id ; Assuming the date_arrival and date_departure fields are the same on both tables then it doesn't matter if you pull them from table1 or table2, otherwise you'll need to change the SELECT appropriately. If you want it to go in to a new table as well as opposed to just a query, you could have a play with SELECT INTO... Cheers, -p -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of filippo Sent: Monday, 26 June 2006 23:39 To: pgsql-sql@postgresql.org Subject: [SQL] join two tables with sharing some columns between two Hi, I have two tables like these: (this is an example, the actual tables have diffferent fields and meanings) TABLE1 id person_name date_arrival date_departure TABLE2 id car_description date_arrival date_departure I'd like to make a query to have such resulting table RESULTING_TABLE id person_name car_description date_arrival date_departure the id is the primary key for the three tables and it's unique for the three (id in table1 cannot be in table2, I use the same counter to generate the id for table1 and table2). I is possible to create such a query? Thanks ---(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 ***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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Can't drop table
> I have created a table called experimenters with the following query: > CREATE TABLE experimenters ( > experimenter_id INTEGER CONSTRAINT firstkey PRIMARY KEY, > first_name CHAR(20), > last_nameCHAR(25), > address CHAR(30), > phone_numCHAR(15) > ); > The query was successful. But when I try to drop or alter the table, it just > hangs without > echoing any error message. I have to cancel the query to get out. > I ran the following query to drop the table > DROP TABLE experimenters; > It also hangs when I try to alter the table. > Just don't understand the problem here. > Any help will be highly appreciated. You might want to post this on the PG_General Mailing list of you do not get many responses. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Can't drop table
Hima Surapaneni <[EMAIL PROTECTED]> writes: > The query was successful. But when I try to drop or alter the table, it just > hangs without echoing any error message. I have to cancel the query to get > out. Sounds like there's some open transaction with a lock on the table. The pg_locks view might help you figure out which one. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
