RE: Find foreign key for a table
Alternatively you can use: SHOW TABLE STATUS from name_db like '%part_of_table_name%'; to get a list of all fk constraints in the database that match a certain string. It also gives some other interesting information. As far as dependencies go you can see which table the constraint references in both of the suggested methods. Not sure if that's what you're after though. Waldemar On Tue, 14 Nov 2006, Clyde Lewis wrote: Will the output from the show command also provide a list of other dependent tables? From what I've seen with the show create table tablename command, it only provide keys associated with that table tablename, but other dependent tables. Thanks so much. At 01:18 PM 11/14/2006, Howard Hart wrote: show create table tablename? -Original Message- From: Clyde Lewis [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Tue 11/14/2006 10:16 AM To: MySQL List Subject: Find foreign key for a table Hello, How can I find the foreign keys for a table? I would like to be able to find, for any table, the columns that are foreign keys and what tables/columns the foreign keys are related to. Basically what I am trying to do is this. My developers want to allow logical deletes of data. I don't want them to be able to logically delete a record that has related records. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: loop through SELECT statement query results in a Trigger
On Fri, 27 Oct 2006, Ferindo Middleton wrote: Is there a way to loop through individual query records within a stored procedure or trigger. If I have table called client_names (id SERIAL, first name TEXT, middlename TEXT, lastname TEXT, suffix TEXT, pet_id INT, properly_trained TEXT) and I have a trigger on it, I'd like to iterate through individual query rows back at another table having a foreign key on pet_id. For example: CREATE TRIGGER update_clients_with_week_assignment_based_on_pet_id_in_pets_table BEFORE INSERT ON client_names FOR EACH ROW BEGIN (SELECT * FROM pets;) label1: LOOP IF (pets row.pet_type) = 4 THEN SET properly_trained = 1; ITERATE label1; END IF; LEAVE label1; END LOOP label1; SET @x = p1; END Is this possible. Can you loop through the query results of a SELECT statement in a trigger, function, or procedure. Ferindo Check out the section on cursors: http://dev.mysql.com/doc/refman/5.0/en/cursors.html That allows you to walk over a result set. -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
exiting out of a loop iteration in a stored proc
Hello everyone, I'm wondering if there is a way to exit out of a REAPEAT/WHILE loop iteration in a stored procedure. I think LEAVE will get you out of the loop completely, but I looking for something like next record. Thanks, Waldemar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: exiting out of a loop iteration in a stored proc
On Wed, 25 Oct 2006, Paul DuBois wrote: At 17:33 -0400 10/25/06, Waldemar Jankowski wrote: Hello everyone, I'm wondering if there is a way to exit out of a REAPEAT/WHILE loop iteration in a stored procedure. I think LEAVE will get you out of the loop completely, but I looking for something like next record. Perhaps you want ITERATE: http://dev.mysql.com/doc/refman/5.0/en/iterate-statement.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Perfect. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help with query: select customers that ARO NOT in orders t
You're right. A join is often more efficient than a subselect especially if you have good indices set up. I believe the IS NULL will also get optimized away in your query. In this example I personally like the subselect syntax for it's explicitness if the speed difference is negligible. For large data sets I would definetly go with your solution of using an outer join. Waldemar On Mon, 23 Oct 2006, Jerry Schwartz wrote: Is a sub-select more efficient than an outer join? SELECT cust_id FROM customers LEFT JOIN orders on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL; Or am I missing something (as usual)? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Waldemar Jankowski [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 1:53 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: help with query: select customers that ARO NOT in orders table On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query: select customers that ARO NOT in orders table
On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query: select customers that ARO NOT in orders table
On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: Ok. Just found I gave wrong info. To make my life easier, the person who created db named cust_id in 'orders' table as SoldTo [EMAIL PROTECTED] in this case, select cust_id from customers where cust_id not in (select Soldto from orders); will not work :( Maybe I'm not understanding you but as long as Soldto is cust_id just with a different column name it will still work. The subquery will result in a list of values and then the NOT IN clause will check if your cust_id is not in that list. For more info check out: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
