Well, doing on all tables at once woule probably bring the server to its knees
due to the cartesian product producing a VERY large temporary table. You can
do it on two tables at once like this (if my memory serves):
SELECT * from mytable as t1, mytable as t2
WHERE t1.column1 = t2.column1 AND
t1.column2 = t2.column2 AND
t1.column3 = t2.column3 AND
t1.id <> t2.id
Of course, you can extend that to as many tables as you want, but the syntax
and performance complications quickly arise.
I would recommend comparing all your tables to one another, two at a time. A
quick perl or C script should accomplish this quickly. For 10 tables, that
is only 45 queries. Not bad.
j----- k-----
On Wednesday 21 April 2004 12:56 am, John Mistler said something like:
> Thanks for the response, Joshua.
>
> I am so very new to MySQL, that I am afraid I require more guidance.
>
> Is there a way to join ALL tables in a database rather than just one table
> to itself, or one particular table to another?
>
> SELECT * FROM allTables WHERE column1=column1 AND column2=column2 AND
> column3=column3;
>
> I know this syntax is off the mark--it should specify:
> table1.column1=table2.column1, etc. However, I need it to match columns on
> all of the tables in the database (of which there are many), rather than
> just two.
>
> Any ideas?
>
> Thanks,
>
> John
>
> on 4/21/04 12:57 AM, Joshua J. Kugler at [EMAIL PROTECTED] wrote:
> > Yes, there is a way. It's called joins. :) I don't remember the exact
> > syntax off the top of my head, but the approach is thus:
> >
> > Do a self join on the table and select records that match in their first
> > three columns, but do not have the same primary key (you *do* have
> > primary keys on your table, don't you?). If you don't add one for this
> > excercise.
> >
> > j----- k-----
> >
> > On Tuesday 20 April 2004 11:22 pm, John Mistler said something like:
> >> Is there a way to use a SELECT statement (or any other, for that matter)
> >> that will look at every table in a database and return every row whose
> >> first 3 columns are duplicated in at least one other row in any of the
> >> tables? Essentially, a command to find duplicate entries in the database
> >> .
--
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ... .... ..- .- -.- ..- --. .-.. . .-.
[EMAIL PROTECTED]
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and under
the earth, that Jesus Christ is LORD -- Count on it!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
