SQL statement!

[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 tips?

 

[cyberbiker]

I just had joins clearified for me recently in an answer to a post of mine

you would want something like 'from members left outer join
membercontacts on membercontacts.memberid = members.memberid'

Terry (cyberbiker)

 

[SemperFiDownUnda]

cyberbiker has it right
If you have 2 (or more) tables that you want to join together and there may not be data in the non base table (the first one in your FROM clause) then you use a LEFT OUTER JOIN

This will select ALL rows from the left table. For each row in the left table the right table will be searched. If there is no corresponding rows in the right table then the columns will be NULL and if there are rows in the right table then 1 to n rows will be produced in the result table all with the same data from the row in the left table but possible different data coming from the right table.

There is also a RIGHT OUTER JOIN you can use that reverses the tables. I try to keep to LEFT OUTER JOIN for consistancy. You also have a FULL OUTER JOIN which will do both. You'll get all rows from both the left and right table. Where they match they will be joined producing a full row of data. Where they don't the opposing table's columns will have NULL values.

 

[johnvai]

Thanks friends for your posts! it's what i was looking for.

 

[123Pom]

in writing a new library in visual basic 6.0, it posible that can I write a coding to my library by calling a form, in this case that this library when I use it by checking or tag at my project references to active this my new library with calling a form (ie. when I used a listbox, dbgrid, etc. for browsing my file)

 

[johnwm]

Hi 123Pom and welcome to Tek-Tips.

Your post doesn't seem to be directly related to this thread. May I suggest you carefully read faq222-2244, then use that info to clarify and re-write your question in a new thread.

With a clear and carefully written question I'm sure you will get the answers you need!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'