sql server2000: join querry on one table 3

[duchovnick]

Hi,
My table contains rows where husband is located in a separate row from as well as wife.
I'd like to create a query of which the results will be husband and wife at one row.
Common column is "phone".
I write:

select
a.name ,
b.name,
a.phone
from
table1 as a left join table2 as b
on
a.phone=b.phone

and i get the following errors:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'phone'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'phone'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'name'.

Will anyone help me please with that? How do i get rid of that error message ?

 

[SQLDenis]

i guess you picked the wrong table

create table table1(name varchar(50),phone varchar(50))

insert table1 values ('husband','123123123')
insert table1 values ('wife','123123123')



select
a.name ,
b.name,
a.phone
from
table1 as a  join table1 as b
on
a.phone=b.phone
and a.name <> b.name
and a.name ='husband'--if you don't add this you will get 2 rows for each phone

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[duchovnick]

Hi SQLDenis,
and thanks
iwrote:

select
a.secr,
b.secr,
a.lastname,
phone1
from contact1 as a join contact1 as b
on
a.phone1=b.phone1 and
a.name<>b.lastname and
a.name='husband'

and got:

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'PHONE1'.

.
Any idea why ?
Thanks a lot !

 

[gmmastros]

That happens because Phone1 exists in both tables. Well, it's the same table but self joined so it appears as 2 tables. To Fix this problem, you must specify which table it's coming from. Since you are using aliases, you must put a. or b. in front of the column name. Like this...

select
a.secr,
b.secr,
a.lastname,
[!]a.[/!]phone1
from contact1 as a join contact1 as b
on
a.phone1=b.phone1 and
a.name<>b.lastname and
a.name='husband'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[duchovnick]

Hi gmmastros !
This time the code worked but i ommited the:

and
a.name='husband'

because i didnt understand its meaning and there's no "husband" name in my table.
The result wa 2 rows for each pair something i'd like to avoid. When i added the above part but with an existing name, such as "john" i recieved empty result.
Can you kindly explain what: 'and
a.name='husband''
stands for?
Thank you very much.

 

[LogicalmanUS]

From your initial post, you indicate that both husband and wife records exist in the same table, just on different rows. If that is the case, you problem exists in the table name of the second table accessed.
You wrote:

select
a.name ,
b.name,
a.phone
from
table1 as a left join table2 as b
on
a.phone=b.phone


using a and b as table aliases, correctly, but alias b refers to a table by the name of table2, and alias a refers to a table named table1.

alter the statement::

select
a.name name1,
b.name name2,
a.phone
from
table1 as a left join table1 as b
on
a.phone=b.phone

The problem exists whereby you do not indicate that either row is the husband or wife. If you did have an indicator there, you could then alter the first two parts of the select statement to be husband and wife, rather than name1 and name2.

HTH

LogicalmanUS

 

[gmmastros]

Here's your problem. On the join you have a.name <> b.last name, this should really be...

select
a.secr,
b.secr,
a.lastname,
a.phone1
from contact1 as a join contact1 as b
on
a.phone1=b.phone1 and
[!]a.name<>b.name[/!] and

To explain the 'husband' thing....

Your query returns 4 columns. If you want the husband's name to show first (column 1), then you need a way to identify which record is the huband, and which is the wife. If there is no way to identify this, then you will get inconsistent results. Some times the husbands name will appear before the wife, and some times the wifes name will appear before the husband. Also, you may want to consider making this a left join so that you don't exclude people without a spouse (unless this is what you are trying to do).

If you don't join on A.Name <> B.Name, then you will get 2 records per 'couple'. This will, of course, fail if the husbands name is the same as the wife's name. While this is unlikely to happen, it could. it would be better to use a primary key instead of names (ex: A.PrimaryKeyField <> B.PrimaryKeyField)

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oooops. I left a dangling and at the very end of the query. This should be removed.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[duchovnick]

So sorry !
I didnt notice i wrote table1-table 2 !!!!!
I meant: both tables are the same table !
My initial code should be then:

select
a.name ,
b.name,
a.phone
from
table1 as a left join table[COLOR=#ff0000]1[/color]
as b
on
a.phone=b.phone

Can i ask for help again ?

 

[duchovnick]

hi gmmastros
Your code worked but yielded somthing like this:
It shows first name and then all the other names next to it.
Then, second name along with all the names etc. It multiplied the bulk of the table significantly. I trie "group by" with all the fields but i only got a handful of lines and i need to check what showed up.
Anyway you were very helpfull because i managed to get rid of the error messages and now i can find for myself how to get the right result.
My notifications were confused and i'm sorry it turned out this way.
Thanks a lot.