SQL problem 
Author Message
 SQL problem


Quote:

> 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 problem

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 problem
SELECT Persons.Name, C1.Description AS City1, C2.Desciption AS City2
FROM Persons, Cities C1,Cities C2
WHERE Persons.City1ID = C1.CityID AND
      Persons.City2ID = C2.CityID
ORDER BY Persons.Name

Vladimir Tumanov
-----------------------------------------

Quote:
>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
 
 [ 3 post ] 

 Relevant Pages 

1. SQL problem, MSDTC Problem or VB.NET problem?

2. sql problem SA problem urgent

3. datagrid problem or SQL problem?

4. SQL Problem: DISTINCT problem

5. Oracle/SQL-Server SQL-Problem

6. PL/SQL Dynamic SQL problem

7. Dynamic sql problem with SQL/PL procs

8. SQL Server 6.5 latest service pack SQL problems

9. Dynamic SQL Problems with CT-client on SQL Server 10.02

10. Cool SQL problem - SQL for Temporal Aggregation

11. sql-problem ADO - MS Sql Server


 
Powered by phpBB® Forum Software