RE: [PHP-DB] Query returns duplicate rows

[Brock Jimmy D Contr 74 MDSS/SGSI]

These two tables do not have a relation, so there is nothing to join.

Basically, what I'm trying to is this:
Users need to score elements. The elements are assoicatied with standards.
A standard may have 1 to 29 elements associated with it.

For users to score the elements they need to see the criteria for that particular 
element.

Thanks for the help though.

-Original Message-
From: Justin Patrin
To: Brock Jimmy D Contr 74 MDSS/SGSI
Cc: [EMAIL PROTECTED]
Sent: 7/21/2004 5:02 PM
Subject: Re: [PHP-DB] Query returns duplicate rows

On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI
<[EMAIL PROTECTED]> wrote:
> My query is returning duplicates rows.
> 
> table: elements
> elementId
> standardId
> elementtext
> category
> mcode
> linenum
> 
> table: scores
> scoreId
> taskId
> scores
> 
> Here's my query:
> SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId
> FROM elements, scores
> WHERE scores.taskId='12'
> AND elements.standardId='APR.05'
> 
> This is returning duplicate rows, even though I'm using the DISTINCT
keyword.
> 
> If I remove the field scoreId it is fine.
> 
> Any suggestions?
> 

Should you perhaps be doing some kind of ON clause to that join? It's
joining every record in elements with every element in scores. You
need to tell it what fields to join on.

Also, doing a distinct on that many fields at once could be very
expensive. Better tp refine your query or data model.

-- 
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder

paperCrane --Justin Patrin--

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Query returns duplicate rows

[Brock Jimmy D Contr 74 MDSS/SGSI]

My query is returning duplicates rows.

table: elements
elementId
standardId
elementtext
category
mcode
linenum

table: scores
scoreId
taskId
scores

Here's my query:
SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId
FROM elements, scores
WHERE scores.taskId='12'
AND elements.standardId='APR.05'

This is returning duplicate rows, even though I'm using the DISTINCT keyword.

If I remove the field scoreId it is fine.

Any suggestions?

[PHP-DB] Delete Subquery

[Brock Jimmy D Contr 74 MDSS/SGSI]

I've been able to figure how to use a delete subquery to delete all rows, but how do I 
use a delete subquery to delete only one row?

 

I have two tables: users and roles with the following fields:

 

Users:

userid

roleid

 

Roles:

roleid

 

I want to be able to delete a rowid from the roles table, but only if the roleid does 
not exist in the users table.

 

thanks