Sql server 2005
-
I had two tables, for Table 1 fields are contactId,Str_name and for table 2 contactId,str_phonenumber,Str_phonetype example : Table 1: 111 Krishna 112 Ramesh 113 Kishore Table 2: 111 8389368936398 M1 111 3535353535353 M2 111 6326326326262 L1 111 3263262626326 L2 Now i need like this 111 Krishna 8389368936398 6326326326262 how to join this I just need any of M1 or M2 and L1 or L2 and contactid and Name how to get this. Please anyone can help me i have been strucked with this problem Thanks
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'Most likely you want to use outer join if there are no matching rows in table2.
The need to optimize rises from a bad design.My articles[^]
-
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'Most likely you want to use outer join if there are no matching rows in table2.
The need to optimize rises from a bad design.My articles[^]
-
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'Most likely you want to use outer join if there are no matching rows in table2.
The need to optimize rises from a bad design.My articles[^]
-
sir when there is no matching rows in table2, i have to retrieve the names from table1 where str_phonenumber are null. I tried with outer join getting errors please suggest me how to do Thanks for Your Help
-
Nath wrote:
I tried with outer join getting errors please suggest me how to do
What is the query like, could you post it? Also what's the error?
The need to optimize rises from a bad design.My articles[^]
-
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'Most likely you want to use outer join if there are no matching rows in table2.
The need to optimize rises from a bad design.My articles[^]
by this query i am getting records with the phone numbers, but if any one phonenumber is null then that record was not displaying, if the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you. could you please help me regarding to this as i was new to this database
-
by this query i am getting records with the phone numbers, but if any one phonenumber is null then that record was not displaying, if the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you. could you please help me regarding to this as i was new to this database
Nath wrote:
the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you
Yes, it's possible. I wrote the query for you so that you can get to start. I'm asking you to post the modified query you have written (the one which has problems) simply because I want to see that you have put effort into this. It doesn't matter if your query isn't working and has problems. The main thing is that you've tried.
The need to optimize rises from a bad design.My articles[^]
-
Nath wrote:
the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you
Yes, it's possible. I wrote the query for you so that you can get to start. I'm asking you to post the modified query you have written (the one which has problems) simply because I want to see that you have put effort into this. It doesn't matter if your query isn't working and has problems. The main thing is that you've tried.
The need to optimize rises from a bad design.My articles[^]
select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber, m.str_phonetype,m.str_phonenumber from trans_contacts t outer join trans_address_phones l,trans_address_phones m where t.int_contactid=l.int_contactid and t.int_contactid=m.int_contactid and l.str_phonetype='L1' and m.str_phonetype='M1' error is incorrect near the join and I tried with single alias also select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber from --m.str_phonetype,m.str_phonenumber from trans_contacts t outer join trans_address_phones l on t.int_contactid=l.int_contactid where l.str_phonetype='L1' here is the same problem thats the reason, whether it is possible to work with join, Help me sir
-
select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber, m.str_phonetype,m.str_phonenumber from trans_contacts t outer join trans_address_phones l,trans_address_phones m where t.int_contactid=l.int_contactid and t.int_contactid=m.int_contactid and l.str_phonetype='L1' and m.str_phonetype='M1' error is incorrect near the join and I tried with single alias also select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber from --m.str_phonetype,m.str_phonenumber from trans_contacts t outer join trans_address_phones l on t.int_contactid=l.int_contactid where l.str_phonetype='L1' here is the same problem thats the reason, whether it is possible to work with join, Help me sir
You're on the right track. If you modify the query like the following, do you get right results
select t.str_firstname,
t.str_lastname,
l.str_phonetype,
l.str_phonenumber
from trans_contacts t
left outer join
trans_address_phones l
on ( t.int_contactid = l.int_contactid
and l.str_phonetype = 'L1')The need to optimize rises from a bad design.My articles[^]