identical rows

[Geoff Jones]

Hiya

I'm new to SQL so I hope somebody can help me with the following:

Suppose I have two tables. How can I find if the two have "identical rows";
in the sense that they are allowed to have different primary keys but in
respect to all the other fields they are the same.

Thanks in advance

Geoff

[Aggro]

Geoff Jones wrote:

Suppose I have two tables. How can I find if the two have "identical rows";
in the sense that they are allowed to have different primary keys but in
respect to all the other fields they are the same.

You need to join those two tables and search rows where all wanted
columns have similar values. Something like this:

select
table1.id as table1_id,
table2.id as table2_id
from table1,table2
where
table1.column1 = table2.column1 and
table1.column2 = table2.column2 and
table1.column3 = table2.column3;

Be aware that this can return the same id values multiple times if you
got multiple duplicates.

[Geoff Jones]

Hiya Aggro

Forgive my ignorance, but like I say, I'm a beginner at this stuff, but
could you explain:

select
table1.id as table1_id,
table2.id as table2_id
from table1,table2

i.e. I'm a little puzzed by the "table1.id as table1_id" (the last bit)

Is table1.id the primary key of table1? What is table1_id?

Geoff

"Aggro" <sp**********@y ahoo.com> wrote in message
news:Xm******** *******@read3.i net.fi...

Geoff Jones wrote:

Suppose I have two tables. How can I find if the two have "identical
rows"; in the sense that they are allowed to have different primary keys
but in respect to all the other fields they are the same.

You need to join those two tables and search rows where all wanted columns
have similar values. Something like this:

select
table1.id as table1_id,
table2.id as table2_id
from table1,table2
where
table1.column1 = table2.column1 and
table1.column2 = table2.column2 and
table1.column3 = table2.column3;

Be aware that this can return the same id values multiple times if you got
multiple duplicates.

[Aggro]

Geoff Jones wrote:

i.e. I'm a little puzzed by the "table1.id as table1_id" (the last bit)

Is table1.id the primary key of table1? What is table1_id?

table1_id is just a alias for the id in table1. And the example assumed
that you have a table named table1 and table2 and both have column named
id as their primary key. Without the alias, your query would return just
two columns, both named as "id", so it would be harder for you to know
which id belongs to which table.

The name of alias is not much ruled, so you could say:
select
table1.id as cat,
table2.id as dog

And it would be as valid and working. But I like naming the aliases so
that you know the table and column from the name.

[Geoff Jones]

Thanks Aggro

"Aggro" <sp**********@y ahoo.com> wrote in message
news:wj******** *******@read3.i net.fi...

Geoff Jones wrote:

i.e. I'm a little puzzed by the "table1.id as table1_id" (the last bit)

Is table1.id the primary key of table1? What is table1_id?

table1_id is just a alias for the id in table1. And the example assumed
that you have a table named table1 and table2 and both have column named
id as their primary key. Without the alias, your query would return just
two columns, both named as "id", so it would be harder for you to know
which id belongs to which table.

The name of alias is not much ruled, so you could say:
select
table1.id as cat,
table2.id as dog

And it would be as valid and working. But I like naming the aliases so
that you know the table and column from the name.