On Mon, 2 Jun 2003, Frank Nørvig wrote: > Hello, > > I was wondering if the following could be done with an advanced > sql-statement in MySQL 3.23: > > I have 5 tables with the following columns: id, name, phonenumber > I need to check for duplicate phonenumbers in the 5 tables, that is if the > same phonenumber exists in 3 tables or more.
Advanced? Not at all. It's just simple combinatorial enumeration of C(5,3) combinations presented as a list of tuples and used as the selection criterion: SELECT T1.id, T1.name, T1.phonenumber FROM T1, T2, T3, T4, T5 WHERE T1.phonenumber = T2.phonenumber AND T2.phonenumber = T3.phonenumber OR T1.phonenumber = T2.phonenumber AND T2.phonenumber = T4.phonenumber OR T1.phonenumber = T2.phonenumber AND T2.phonenumber = T5.phonenumber OR T2.phonenumber = T3.phonenumber AND T3.phonenumber = T4.phonenumber OR T2.phonenumber = T4.phonenumber AND T4.phonenumber = T5.phonenumber OR T3.phonenumber = T4.phonenumber AND T4.phonenumber = T5.phonenumber A same phone number exists in three or more tables if and only if the result set is nonempty. Regards, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
