Join SQL Statement! 1

[johnvai]

Dear friends:
I have 2 tables (Members and Membercontacts).
membercontacts.memberid is referenced bt members.id

I want to display the members.memname, membercontacts.contactname, membercontacts.contactdetails where members.id=4

i cannot make any join that will display me a result containing at least the members.memname in case no fields are existing in members and membercontacts

Members
Id Memname
1 IBM Company
2 Spinneys
3 Mc Donalds
4 John Vai

Membercontacts
Id Memberid Contactname ContactDetails
1 1 Walter Smith Office Number 3
2 3 Dany Balian Marketing department
3 2 Frederick M. Customer service, Office #4

I want to make a join that will display the Membname in case no related fields exist in membercontacts
(id=4 memname=John Vai)

I am using the following and no result is output:

select m.memname,mc.contactname,mc.contactdetails from Members as m,Membercontacts as mc
where m.id=mc.memberid and m.id=4

Any help?

 

[nikhilparchure]

There is no corrosponding row in Membercontacts
for memberid = 4 .Hence a equi join is not finding any match and hence no result.

Use of inner join , can solve your problem.
But check your requirement and make the changes as given below
-------------------------------------------------------
select m.memname,mc.contactname,mc.contactdetails from Members as m,Membercontacts as mc
where m.id*=mc.memberid and m.id=4
-------------------------------------------------------

A * can just make the difference.


Parchure Nikhil

 

[johnvai]

Dear Parchure:

Thanks for your reply, it works perfectly.
Can I know if the * stands for left outer join?

John

 

[coders4hire]

From sybase: Transact-SQL includes syntax for both left and right outer joins. The left outer join, *=, selects all rows from the first table that meet the statement's restrictions. The second table generates values if there is a match on the join condition. Otherwise, the second table generates null values.

For example, the following left outer join lists all authors and finds the publishers (if any) in their city:

select au_fname, au_lname, pub_name
from authors, publishers
where authors.city *= publishers.city

The right outer join, =*, selects all rows from the second table that meet the statement's restrictions. The first table generates values if there is a match on the join condition. Otherwise, the first table generates null values.

You cannot include a Transact-SQL outer join in a having clause.

A table is either an inner or an outer member of an outer join. If the join operator is *=, the second table is the inner table; if the join operator is =*, the first table is the inner table. You can compare a column from the inner table to a constant as well as using it in the outer join. For example, if you want to find out which title has sold more than 4000 copies:


---
doug@coders4hire.com

http://www.coders4hire.com

http://www.payraisecalculator.com

http://www.cliintel.com

 

[johnvai]

Thanks friends, I got it.