SQL query with two 1:n relations to the same table 
Author Message
 SQL query with two 1:n relations to the same table

I've two tables with two identical 1:n relations between it. For example:

Persons
-------
PersonID
Name
City1ID
City2ID

Cities
------
CityID
Description

I want in one view the name of the person with two two citynames of both
city relations. My suggestion was:

SELECT Persons.Name, Cities.Description AS City1, Cities.Desciption AS City2
FROM Persons, Cities
WHERE Persons.City1ID = Cities.CityID AND
      Persons.City2ID = Cities.CityID
ORDER BY Persons.Name

When I run this statement, it fails because only one record from the city
tabel could be selected at the same time. How to solve this?

Erik Reudink



Mon, 01 Jan 2001 03:00:00 GMT
 SQL query with two 1:n relations to the same table

I would be inclined to build an intersection table to resolve the
relationship between Persons and Cities:

Person_City
--------
PersonID
CityID

If there absolutely and always will be only two cities per person, you could
use the existing structure and query it something like this:

select
    p.name,
    c1.description as city1,
    c2.description as city2
from
    persons p
join
    cities c1
on
    p.city1id = c1.cityid
join
    cities c2
on
    p.city2id = c2.cityid

--
Bob Pfeiff
MCSD, SQL Server MVP
Spectrum Technology Group, Inc.



Mon, 01 Jan 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. How can I create a delete query based on two tables relations

2. Query and two tables with relations

3. SQL Query - join two tables in two different databases

4. Two DBGrid's Showing Table Relation's

5. Help!!. More than one relations between two tables

6. Need to get the relation between two tables via ado code

7. Table with two relations.

8. Help! Two relations in one table

9. Relation between two tables

10. ...inner join query between two table of two different database

11. inner join query between two table of two different database

12. Query over two tables in two different databases


 
Powered by phpBB® Forum Software