Re: Aliias names in select criteria

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 table name 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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant