[SQL] Select help
Hi, I am trying to do a 2 joins between 3 tables. ie) select , coalesce(TRR.ABC, SOC.ABC) as newABC, ... from A join (B join C on (..)) on (..)) as TRR left join (D join E on (..)) as SOC on (TRR.Field1=SOC.Field2) When I run this it says that there is an ambiguous field. Yes after the join for TRR and SOC they both contain a fields name ABC. How can I rename this field in the sql statement or how can I make it so the sql statement know that they are different. Please help.. .thanks -- Linh Luong Computalog Ltd. Software Developer Phone: (780) 464-6686 (ext 325) Email: [EMAIL PROTECTED] ---(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] Select for LEFT JOIN
Hi, I am trying to do a 2 joins between 3 tables. ie) select , coalesce(TRR.ABC, SOC.ABC) as newABC, ... from A join (B join C on (..)) on (..)) as TRR left join (D join E on (..)) as SOC on (TRR.Field1=SOC.Field2) When I run this it says that there is an ambiguous field. Yes after the join for TRR and SOC they both contain a fields name ABC. How can I rename this field in the sql statement or how can I make it so the sql statement know that they are different. Please help.. .thanks -- Linh Luong Computalog Ltd. Software Developer Phone: (780) 464-6686 (ext 325) Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Seq Scan
Hi, I am trying to execute this query I wrote and it is not returning at a reasonable time to the browser. I took the query and ran EXPLAIN on it and it displayed that every JOIN I do it is doing a Seq Scan on it. I have indexes on that table but it doesn't seem to use it. Is there a way I can ensure that postgres will use my indexes? Thanks again -- Linh Luong Computalog Ltd. Software Developer Phone: (780) 464-6686 (ext 325) Email: [EMAIL PROTECTED] ---(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] Using indexes
Hi, Indexes are used to find a record faster in a table. It only seems to work if I am working with one table. eg) > create index customer_id_idx on customer(id); > select * from customer where id=1; -- This uses the index I create However, when I start to join 2 or more tables together it doesn't seem to use it.. WHY??? eg) > create index customer_id_idx on customer(id); > select * from customer, purchaseorder where customer.id=purchaseorder.id; -- This results in a seq scan .. WHY?? Is there a way to make it so that it uses my index Thanks again. ---(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] Too many rows returning
Hi, Does the number of rows affect how long it takes to execute a query. I have 3 UNIONS. One table has 97 rows, another has 7375 rows, and 1558; In each union there are at least one LEFT OUTER JOIN and each subselect has at least 2 JOINed tables. Something like this: select ..,..,,...,. from (subselect. ... ) as T1 LEFT JOIN (subselect ... ) as T2 on T1.field1=T2.field2 UNION select ..,..,,...,. from ((subselect. ...) as T1 LEFT JOIN (subselect ) as T2 on T1.field1=T2.field1) as T3 LEFT JOIN (subselect ... ) as T2 on T1.field1=T2.field2 UNION select ..,..,,...,. from (subselect. ... ) as T1 LEFT JOIN (subselect ... ) as T2 on T1.field1=T2.field2 When I do my sql stmt it takes more than 10minutes to return and sometimes it display the maximum 30 sec has exceed... Can anyone provide any suggestion in how I can speed up the execution and possibly return something to me without the error message. I have tried index but it doesn't seem to have an affect.. because I did the explain it still using seq scan.. (re: last post "using indexes") Thanks again Linh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Cascade constraint gone!!!
Hi, I recently wipe my database away. And some of the tables have a UPDATE CASCADE on it to another table. When I reload the data from my backup that I did with pg_dumpall. My code started to give me an error because I tried to modify a table that was reference from another table. But the referenced table should have an UPDATE CASCADE. But according to the error it doesn't. Is there a way to add the ON UPDATE CASCADE back without dropping any table again? Thanks -- Linh Luong ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] dropping constraints
Hi, Is there to drop a constraint when you add it separately with an alter statement. alter table failuretypecategory1 add constraint fk_failuretypecategory1 FOREIGN KEY (failurecategory1id) REFERENCES failurecategory1 (id) ON UPDATE CASCADE ON DELETE CASCADE; I tried drop trigger on failurecategory1 and drop trigger on failuretypecategory1 and also drop trigger on failurecategory1 and drop trigger on failuretypecategory1 But it kept saying ERROR: DropTrigger: there is no trigger / on relation failuretypecategory1 What am I doing wrong. I tried dropping the table also and when I tried to delete from a table that it referenced above it gave the error that the table I just drop doesn't exist. Obviously because I just removed it but the constraint it still there.. Thanks -- Linh Luong ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
