Postgres Pro
Downloads
Home   >   mailing lists

Thread: Aliias names in select criteria

Aliias names in select criteria

From
"Bob Powell"
Date:
To whom it may concern:

The following selection criteria causes my box to start a process that gives no result.

SELECT P.Last_Name, P.First_Name, S.Status, S.Entry_Year, S.Graduation_Year, S.Former_School, S.Day_or_Board,
S.Admissions_Id,P.Participant_Id, P.Birthday, P.Modification_Date 
FROM Participants P, Students S
WHERE Participant.Participant_Id = Students.Participant_Id

It also throws the linux box into a process that has to be killed even though I've exited the client.

I found the problem to be the non-use of the alias after the WHERE clause  If I rerun this as below without the full
tablename and using the alias it works.  Is this a normal occurrence.  Other systems I have used work either way. 

SELECT P.Last_Name, P.First_Name, S.Status, S.Entry_Year, S.Graduation_Year, S.Former_School, S.Day_or_Board,
S.Admissions_Id,P.Participant_Id, P.Birthday, P.Modification_Date 
FROM Participants P, Students S
WHERE P.Participant_Id = S.Participant_Id


Bob Powell

Re: Aliias names in select criteria

From
Stephan Szabo
Date:
On Tue, 4 Jun 2002, Bob Powell wrote:

>
> To whom it may concern:
>
> The following selection criteria causes my box to start a process that gives no result.
>
> SELECT P.Last_Name, P.First_Name, S.Status, S.Entry_Year,
> S.Graduation_Year, S.Former_School, S.Day_or_Board, S.Admissions_Id,
> P.Participant_Id, P.Birthday, P.Modification_Date FROM Participants P,
> Students S WHERE Participant.Participant_Id = Students.Participant_Id

AFAIK the above is technically illegal.  Postgres assumes you meant to
have Participant and Students in the from list (in addition to P and S)
and thus you get a 4 way join with only some of the tables being
constrained.

Re: Aliias names in select criteria

From
Manfred Koizar
Date:
Bob,

which version of PostgreSQL do you use?

On Tue, 04 Jun 2002 12:49:35 -0400, "Bob Powell" <Bob@hotchkiss.org>
wrote:
>
>To whom it may concern:
>
>The following selection criteria causes my box to start a process that gives no result.
>
>SELECT P.Last_Name, P.First_Name, S.Status, S.Entry_Year, S.Graduation_Year, S.Former_School, S.Day_or_Board,
S.Admissions_Id,P.Participant_Id, P.Birthday, P.Modification_Date 
>FROM Participants P, Students S
>WHERE Participant.Participant_Id = Students.Participant_Id

With version 7.1.3, 7.2, 7.2.1, and 7.3devel (two or three week old) I
get the messages
    NOTICE:  Adding missing FROM-clause entry for table "Participant"
    NOTICE:  Adding missing FROM-clause entry for table "Students"

This means, the engine does a CROSS JOIN of P, S, Participant, and
Students restricted only by
    Participant.Participant_Id = Students.Participant_Id.
>
>It also throws the linux box into a process that has to be killed even though I've exited the client.

So if
 n1 = number of rows in Participant
 n2 = number of rows in Students
 n3 = number of rows in (Participant INNER JOIN Students
                         ON Participant.Participant_Id =
                            Students.Participant_Id),
you can expect to get a result set of n1 * n2 * n3 rows.

Servus
 Manfred

Re: Aliias names in select criteria

From
Joel Burton
Date:
On Tue, 4 Jun 2002, Bob Powell wrote:

> The following selection criteria causes my box to start a process that gives no result.
>
> SELECT P.Last_Name, P.First_Name, S.Status, S.Entry_Year, S.Graduation_Year, S.Former_School, S.Day_or_Board,
S.Admissions_Id,P.Participant_Id, P.Birthday, P.Modification_Date 
> FROM Participants P, Students S
> WHERE Participant.Participant_Id = Students.Participant_Id
>
> It also throws the linux box into a process that has to be killed even though I've exited the client.
>
> I found the problem to be the non-use of the alias after the WHERE clause  If I rerun this as below without the full
tablename and using the alias it works.  Is this a normal occurrence.  Other systems I have used work either way. 
>
> SELECT P.Last_Name, P.First_Name, S.Status, S.Entry_Year, S.Graduation_Year, S.Former_School, S.Day_or_Board,
S.Admissions_Id,P.Participant_Id, P.Birthday, P.Modification_Date 
> FROM Participants P, Students S
> WHERE P.Participant_Id = S.Participant_Id

If you reference a table but don't include it in your FROM clause, PG adds
it for you. For example:

SELECT Student.name,
       School.name
FROM   Student

would also look in table "School". However, since we haven't provided any
information to restrict the joining of Students and Schools, it will be a
cartesian (full) join -- possibly creating a huge output.

This is what's happening with your query. You're joining Participants as
P, Students as S, Participants, and Students, with the last two being
combined with no restriction on the join.

HTH.

- J.
--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant