Обсуждение: Re: SELECT something NOT EQUAL to????

On Tue, 26 Jun 2001 07:27:10 GMT, Sterling <smullett@omeninc.com> wrote:
> H-
>
> I have a sql query that I'm constructing and I need it to select all the
> records that are not part of these other records.
>
> For instance.
> $project_id = "1";
>
> table "records"
> project_id int4
> employee_id int4
>
> and contains 5 records
> project_id    employee_id
> 1        2
> 1        3
> 2        4
> 2        2
> 2        5
>
> table "list"
> employee_id int4
> name varchar(64)
>
> and contains 4 records
> employee_id    name
> 2        John Doe
> 3        Jane Doe
> 4        Bill Smith
> 5        John Denver
>
> I have this statement.
> $sql = "SELECT DISTINCT e.employee_id, e.name, s.project_id
> FROM list e, records s
> WHERE s.project_id != '$project_id'
> ORDER BY last_name ASC";
>

A few problems:
 not sure you need to SELECT DISTINCT on this query
 s.project_id is int4, so no 'quotes' around the value
 what is last_name? it is not in these table definitions



SELECT
   e.employee_id,
   e.name,
   s.project_id

FROM

   list e
NATURAL JOIN
   records s

WHERE
   s.project_id != 1

ORDER BY
   name

H-

Thank you very much for the reply.

Yes, last_name should just be name. I modified this query for this post
so I wouldn't have to explain every field basically and just used the
fields specific to this query.

I used your query but unforunately this is the reply I got when
executing it.
  PostgreSQL query failed:  ERROR:  JOIN expressions are not yet
implemented

From your query though it looks like != is a valid equate symbol? Of
course the JOIN didn't work so maybe we are using a different version of
postgres. I'm using 6.5.3

If != is a valid usage (it doesn't throw errors) than wonder why it's
not working correctly.

Here is a query that I created that pulls only the employee_id/name from
list based entirely on records project_id/employee_id

SELECT e.employee_id, e.name, s.employee_id
FROM list e, records s
WHERE s.project_id=$project_id AND s.employee_id=e.employee_id
ORDER BY name ASC

This works well. Without errors so I'm working off of this model. Which
probably is a wrong approach but I'm not sure which other direction to
come from. I looked at UNION but didn't think it would be appropriate
because it requires returning the same number of fields and the two
tables are completely different and I'm really only pulling info from
list. Anywo, I'm confused by determined.

Thanks again and any further info or clarification you need or can
provide would be great.
-Sterling


Lee Harr wrote:
>
> On Tue, 26 Jun 2001 07:27:10 GMT, Sterling <smullett@omeninc.com> wrote:
> > H-
> >
> > I have a sql query that I'm constructing and I need it to select all the
> > records that are not part of these other records.
> >
> > For instance.
> > $project_id = "1";
> >
> > table "records"
> > project_id int4
> > employee_id int4
> >
> > and contains 5 records
> > project_id    employee_id
> > 1             2
> > 1             3
> > 2             4
> > 2             2
> > 2             5
> >
> > table "list"
> > employee_id int4
> > name varchar(64)
> >
> > and contains 4 records
> > employee_id   name
> > 2             John Doe
> > 3             Jane Doe
> > 4             Bill Smith
> > 5             John Denver
> >
> > I have this statement.
> > $sql = "SELECT DISTINCT e.employee_id, e.name, s.project_id
> > FROM list e, records s
> > WHERE s.project_id != '$project_id'
> > ORDER BY last_name ASC";
> >
>
> A few problems:
>  not sure you need to SELECT DISTINCT on this query
>  s.project_id is int4, so no 'quotes' around the value
>  what is last_name? it is not in these table definitions
>
> SELECT
>    e.employee_id,
>    e.name,
>    s.project_id
>
> FROM
>
>    list e
> NATURAL JOIN
>    records s
>
> WHERE
>    s.project_id != 1
>
> ORDER BY
>    name