select * from multiple tables??????
-
Hi!! I am using ASP-script and an access-database. I want to make a select query that shows all the data in many tables how is it possible to do this? An example: I want to see the names of all companys in the company-table and I also want to see their adresses wich are in the adress-table and I also want to see their email-adresses wich are in the email-table. But I also want to see the companys who do not have for example an email-adress. How is it possible to extract this kind of data? /Martin S :cool:
-
Hi!! I am using ASP-script and an access-database. I want to make a select query that shows all the data in many tables how is it possible to do this? An example: I want to see the names of all companys in the company-table and I also want to see their adresses wich are in the adress-table and I also want to see their email-adresses wich are in the email-table. But I also want to see the companys who do not have for example an email-adress. How is it possible to extract this kind of data? /Martin S :cool:
Select
Company.Name, Adress.Adress, email.email
From
Company, Adress, email
where
Company.ID = Address.id And
Company.Id =* email.idYou Understand. The secret is the =*. But You can use *= depends de case. I need to clarify something. The =* is a RIGHT JOIN and the other is a LEFT JOIN. In some databases engine =* or *= are no soported, in that case you need to Use the syntax LEFT|RIGHT JOIN. Test if don't work please notify me, that Y will help you. Cheers!!!! :) Carlos Antollini.
-
Select
Company.Name, Adress.Adress, email.email
From
Company, Adress, email
where
Company.ID = Address.id And
Company.Id =* email.idYou Understand. The secret is the =*. But You can use *= depends de case. I need to clarify something. The =* is a RIGHT JOIN and the other is a LEFT JOIN. In some databases engine =* or *= are no soported, in that case you need to Use the syntax LEFT|RIGHT JOIN. Test if don't work please notify me, that Y will help you. Cheers!!!! :) Carlos Antollini.
-
Thanks for your answer Carlos;) But since I am using an Access database it does not seem to work by using "*" sign. I would be grateful for other suggestions!!! Best regards/ Martin :cool:
No problem In Access you Have the LEFT JOIN and the RIGHT JOIN syntaxis Like this:
FROM
Company LEFT JOIN email ON
Company.ID = email.companyIDThis expession assums that you have Company that don't have email. If a company don't is in the table email the company is showed. In others words: It selects all companys, including those without email. Cheers!!!! :-D Carlos Antollini.
-
No problem In Access you Have the LEFT JOIN and the RIGHT JOIN syntaxis Like this:
FROM
Company LEFT JOIN email ON
Company.ID = email.companyIDThis expession assums that you have Company that don't have email. If a company don't is in the table email the company is showed. In others words: It selects all companys, including those without email. Cheers!!!! :-D Carlos Antollini.
Hi & Thank you!!! But how do I do if I have Many tables that I want to join. For example. I want to see all companies including companys without email and also including companys without telephonenumber etc. Will I need some sort of nested join-query to do this? if so What how shall I write this? Thanks in advance, Martin :cool:
-
Hi & Thank you!!! But how do I do if I have Many tables that I want to join. For example. I want to see all companies including companys without email and also including companys without telephonenumber etc. Will I need some sort of nested join-query to do this? if so What how shall I write this? Thanks in advance, Martin :cool:
Is the same
Select Company.*, email.email, phone.phone
FROM
Company LEFT JOIN email ON
Company.ID = email.companyID AND
Company LEFT JOIN phone ON
Company.ID = phone.companyIDIf the phone or the email for that company don't exist, you will see the field like NULL. Cheers!!!! Carlos Antollini.