SELECT something NOT EQUAL to????
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";
Now I know that the != isn't part of postgres but I can't figure out
what the equivalent operation would be.
I've read the postgres guide under WHERE clause but didn't see anything
that seemed appropriate, or would work when I plugged it in. > <> >=
etc. nothing.
It doesn't throw an error when using the != sign though but returns all
records of table list.
I want it to return all names in list (using employee_id) that aren't
already in the records field for a particular project. That would mean
the results should only return names that are in project_id 2 which is
THREE records for employee_id's 4,2,5 and those record's name.
I was earlier able to get the query to pull all records and names
associated with a particular project by using a similar string, but that
was using e.employee_id=s.employee_id and it pulled all the records fine
and only the appropriate records.
DB isn't my strong suit and I usually get stuff done through trial and
error, examples online and basic knowledge but this sql query is not
going well.
If any one has any thoughts or suggestions it'd be greatly appreciated.
Thanks.
-Sterling
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 int4and contains 5 records
project_id employee_id
1 2
1 3
2 4
2 2
2 5table "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 DenverI 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:
Show quoted text
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 int4and contains 5 records
project_id employee_id
1 2
1 3
2 4
2 2
2 5table "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 DenverI 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 definitionsSELECT
e.employee_id,
e.name,
s.project_idFROM
list e
NATURAL JOIN
records sWHERE
s.project_id != 1ORDER BY
name