Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ghislou
Starting Member
1 Post |
 Posted - 2002-03-01 : 14:24:28
|
| I migrated my DB from MS Access to SQL Server. All the relation between table as been lost. Is there anyway to get them back? If not, how to combine those two SQL in one.QUERY: lst_ContactSELECT Contact.*, Country.[Country Name], Country.[Local Name], v_Contact_Organisation_full.[Organisation Name]FROM (Contact LEFT JOIN Country ON Contact.Country_ID = Country.Country_ID) LEFT JOIN v_Contact_Organisation_full ON Contact.Contact_ID = v_Contact_Organisation_full.Contact_IDQUERY: v_Contact_Organisation_fullSELECT Contact_Organisation.*, Organisation.*, Organisation.Organisation_ID AS IDFROM Organisation INNER JOIN Contact_Organisation ON Organisation.Organisation_ID = Contact_Organisation.Organisation_ID |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-02 : 07:16:15
|
| As long as the ID fields were maintained in the transition, your relationships were intact. (Those relationships which Access maintained served only two purposes - one was to specify how cascading deletes should operate, the other was to specify "default joins" so that you did not have to remember them when designing a query.)To combine both select statements into one, I believe you're after:SELECT Contact.*, Country.[Country Name], Country.[Local Name], Organisation.[Organisation Name]FROM Contact left join Country on contact.country_id = country.country_id left join Contact_Organisation on contact.contact_id = contact_organisation.contact_id inner join Organisation ON Organisation.Organisation_ID = Contact_Organisation.Organisation_ID--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
|
|
|
|
|
Combining 2 SQL statements into 1: Three tables