Re: Help writing SQL statement in PHP script

Tim Roberts wrote:
Jerry Stuckle <jstucklex@xxxxxxxxxxxxx> wrote:

This table is 100% 1NF - but it has duplicates in the (userid, groupid)
entries:

userid groupid permission
1 1 read
1 1 write
1 1 delete
1 2 read
1 3 read

You are absolutely correct. I *suspect* the difference in opinion here
comes down to terminology; many people do not consider a database to be
"normalized" until it is 3NF, and the table above is not 3NF.

Not quite correct, because Jerry's query still fails on this table
structure. This is easily demonstrated by this SQL example:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
userid int not null,
groupid int not null,
permission char(6) not null,
primary key (groupid, userid, permission)
);

INSERT INTO test VALUES
(1, 1, 'read'),
(1, 1, 'write'),
(1, 1, 'delete'),
(1, 2, 'read'),
(1, 2, 'write'),
(1, 2, 'delete');

SELECT t1.groupid
FROM test AS t1
LEFT JOIN test AS t2
ON t1.userid = t2.userid
WHERE t1.groupid = 1 AND t2.groupid = 2;

His query returns:

9 rows in set (0.00 sec)

Jerry's query returned 9 rows, but there only 6 in the original table.
This is a serious inconsistency. His oft-repeated claim that his query
works with duplicates is clearly false. In another example that was
posted here, his query produced 200 million results in a table with
40,000 records. The more data you have, the more infeasible this
becomes.

Peter's approach works correctly however. The original query targeted
a binary relation posted by the OP. If you want to alter the problem
to have a ternary, quaternary, or higher dimension relation as in
Jerry's latest and greatest table design, then you simply use COUNT
DISTINCT like so:

SELECT userid
FROM test
WHERE groupid IN (1, 2)
GROUP BY userid
HAVING COUNT(DISTINCT userid, groupid) = 2;

However, what if you want to test for membership in not just 2 groups,
but 10, 100 or 1,000? That's easy to do with Peter's solution by
creating a simple function:

function group_intersection($group_ids)
{
$matches = array();
if (count($group_ids)) {
$set = join(', ', $group_ids);
$query = "SELECT userid FROM test WHERE
groupid IN ($set) GROUP BY userid HAVING
COUNT(DISTINCT userid, groupid) = " .
count($group_ids);
$db = getMdb2();
$matches = $db->extended->getCol($query);
}
return $matches;
}

$group_ids = range(1, 1000);
print_r(group_intersection($group_ids));

Let's see Jerry's solution do that... It can't, because it's
restricted to 2 groups only! That is a second serious deficiency in
his design, which doesn't pass muster in the real world where you
could have hundreds or thousands of groups defined.

--
Gary L. Simms
gary.l.simms at gmail.com
.

Relevant Pages

  • Re: Conditional Constraint - HowTo
    ... Tibor Karaszi, SQL Server MVP ... >> Tibor Karaszi, SQL Server MVP ... >>> Considering that UserID is FK from Users Table and GroupID is FK from Groups Table: ...
    (microsoft.public.sqlserver.server)
  • ID Numbering in Group and Passwd
    ... Create a group called "myfamily" using "groupadd myfamily" - the file ... As you can see the utilities have created the user brother with a userid of ... 500 and a groupid of 501. ... I've done a fair bit of work with user accounts / groups stored in OpenLDAP ...
    (Fedora)
  • Re: Help writing SQL statement in PHP script
    ... any column reference in the HAVING clause must either be part of the result ... set or one of the grouped columns. ... groupid INT NOT NULL, ... PRIMARY KEY (groupid, userid, permission)); ...
    (comp.lang.php)
  • Re: SQL rewrite and re-explained - help?
    ... This is what you have now for your base query. ... Now, each UserID/GroupID combination has a number assigned to it, From 1 to X, with X being the number of GroupIds assigned to that UserID. ... And the VALUE field, which will be your GroupID, with First or Last in the totals row. ... When it fails, it exits the internal select clause, which will be run again when the next record from the external select clause is processed, but Rank is reset. ...
    (microsoft.public.access.queries)
  • Re: Conditional Constraint - HowTo
    ... "KenA" wrote in message ... > Considering that UserID is FK from Users Table and GroupID is FK from Groups Table: ... > GroupID (int) ...
    (microsoft.public.sqlserver.server)