Обсуждение: Query question

Hi,

I've got a query which I'd like to optimize (for using indizes):

This is a working version, but it does not do exactly what I want:

------ snip -------
SELECT  m.*
FROM  table1 m,
   table2 ms,
   table3 s,
   table4 l,
   table5 c
WHERE  m.id = 262451
AND   ms.value2 = m.id
AND   l.id = m.value
AND   s.id = ms.slot
AND   c.id = s.contestant
ORDER BY m.date ASC
------ snap -------


This version should theoretically do what I want, but it does not work (it
gives "ERROR:  Relation "s" does not exist"):

------ snip -------
SELECT  m.*
FROM  table1 m,
   table2 ms,
   table3 s,
   table4 l
LEFT JOIN table5 c ON s.id = ms.slot
WHERE  m.id = 262451
AND   ms.value2 = m.id
AND   l.id = m.value
AND   c.id = s.contestant
ORDER BY m.date ASC
------ snap -------


Thanks for your help.

Regards,
Bjoern

Just move table3 s to be last table specified before the left join phrase.

--
Nigel J. Andrews


On Thu, 28 Nov 2002, Björn Metzdorf wrote:

> Hi,
>
> This version should theoretically do what I want, but it does not work (it
> gives "ERROR:  Relation "s" does not exist"):
>
> ------ snip -------
> SELECT  m.*
> FROM  table1 m,
>    table2 ms,
>    table3 s,
>    table4 l
> LEFT JOIN table5 c ON s.id = ms.slot
> WHERE  m.id = 262451
> AND   ms.value2 = m.id
> AND   l.id = m.value
> AND   c.id = s.contestant
> ORDER BY m.date ASC
> ------ snap -------
>
>
> Thanks for your help.
>
> Regards,
> Bjoern

Oops, should have read properly. Do it as:

SELECT m.*
 FROM table1 m,
      table4 l,
      table2 ms
  LEFT JOIN
      table3 s ON ...


So doing m.* works to select the columns returned does it? I'm sure last time I
tried it it didn't.


I wrote:

Just move table3 s to be last table specified before the left join phrase.

--
Nigel J. Andrews


On Thu, 28 Nov 2002, Björn Metzdorf wrote:

> Hi,
>
> This version should theoretically do what I want, but it does not work (it
> gives "ERROR:  Relation "s" does not exist"):
>
> ------ snip -------
> SELECT  m.*
> FROM  table1 m,
>    table2 ms,
>    table3 s,
>    table4 l
> LEFT JOIN table5 c ON s.id = ms.slot
> WHERE  m.id = 262451
> AND   ms.value2 = m.id
> AND   l.id = m.value
> AND   c.id = s.contestant
> ORDER BY m.date ASC
> ------ snap -------
>
>
> Thanks for your help.
>
> Regards,
> Bjoern

The first was fine, thanks, its working now..

Regards,
Bjoern

----- Original Message -----
From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
To: "Björn Metzdorf" <bm@turtle-entertainment.de>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, November 28, 2002 3:21 PM
Subject: Re: [GENERAL] Query question




Oops, should have read properly. Do it as:

SELECT m.*
 FROM table1 m,
      table4 l,
      table2 ms
  LEFT JOIN
      table3 s ON ...


So doing m.* works to select the columns returned does it? I'm sure last
time I
tried it it didn't.


I wrote:

Just move table3 s to be last table specified before the left join phrase.

--
Nigel J. Andrews


On Thu, 28 Nov 2002, Björn Metzdorf wrote:

> Hi,
>
> This version should theoretically do what I want, but it does not work (it
> gives "ERROR:  Relation "s" does not exist"):
>
> ------ snip -------
> SELECT  m.*
> FROM  table1 m,
>    table2 ms,
>    table3 s,
>    table4 l
> LEFT JOIN table5 c ON s.id = ms.slot
> WHERE  m.id = 262451
> AND   ms.value2 = m.id
> AND   l.id = m.value
> AND   c.id = s.contestant
> ORDER BY m.date ASC
> ------ snap -------
>
>
> Thanks for your help.
>
> Regards,
> Bjoern

On Thu, 28 Nov 2002, Björn Metzdorf wrote:

> The first was fine, thanks, its working now..


Yes, the second one was completely wrong. Don't know what I was thinking for
that.