Help writing SQL statement in PHP script

[Chuck Cheeze]

This might be in the wrong group, but...

Here is an example of my data:

entry_id cat_id
1 20
2 25
3 30
4 25
5 35
6 25
2 30
2 35
3 35

As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35

I have captured the cat_id's 30 and 35 with my script, so I need all
entry_id's that belong to BOTH cat_id 30 and 35.

I tried "Select entry_id from myTable where cat_id = '30' and cat_id =
'35' but obviously that is incorrect.

Can someone help? Thanks...

[W. Jenkins]

Jerry Stuckle <js*******@attg lobal.netwrote:

ox*****@yahoo.c om wrote:

>On May 15, 9:52 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

>>Mitch Sherman wrote:
On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
<jstuck...@a ttglobal.netwro te:
>
I'm not arguing about proper database design. My only comment is it is
IMPOSSIBL E to determine if the database is normalized or not from the
given information.
>
That doesn't mean that the relation can't be normalized first. That
seems to be the critical point you're missing.

No, the critical point YOU'RE MISSING is that the table may be
normalized - AND STILL HAVE DUPLICATES IN THESE COLUMNS.

You're not speakign clearly here. First you said:

"It is IMPOSSIBLE to determine if the database is normalized or
not."

Then:

"The table may be normalized - AND STILL HAVE DUPLICATES IN THESE
COLUMNS."

In one case the table is not normalized, in the second it *is* normalized!

The poster was referring to the first case, which the solution deals
with properly by normalizing. The second case is not the same problem
at all.

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

No one is arguing that this new scenario is not 1NF.

Have you seen the CREATE TABLE definitions? If so, your crystal ball
must be working better than mine - because he sure as hell didn't post
it. And he could have had 200 additional columns in that table which he
didn't mention, because they were not germane to the question he posed.

He gave a concrete example of his data. There's no need to assume
anything outside that. He was clear he had a binary relation which
is always exactly 2 columns.

[W. Jenkins]

Guillaume <gg*****@nospam .gmail.com.inva lidwrote:

ox*****@yahoo.c om a ?crit :

>Here is the actual table structure:

>>Here is an example of my data:

Nice assert.

>There is no "userid", "groupid", or "permission ". This is a linking
table that relates entry_id to cat_id.

You wish. Still, you assume. And you're wrong. I mean you're wrong to
assume, cause you may be right about the table structure. Note the word,
"may".

The poster gave a concrete example of his data which is equivalent to
an instance of his problem. Most people can recognize a many-to-many
relationship.

I'll just refer you to my previous comments here:

http://groups.google.com/group/comp....d33733e0ee61aa

[W. Jenkins]

Jerry Stuckle <js*******@attg lobal.netwrote:

>
How about this relationship:

groupid userid permission
1 1 read
1 1 write
1 1 delete
2 4 read
5 7 read

A perfectly normalized table

How do you know it's normalized? Maybe there are "extra columns"?

According to your argument a "CREATE TABLE" statement would
be necessary to actually determine that.

I think you've just proved the poster's point.

[Mitch Sherman]

On Mon, 19 May 2008 11:35:32 +0200, Guillaume
<gg*****@NOSPAM .gmail.com.INVA LIDwrote:

Any solution that involves a database would be wrong according to your
logic, so your premise is a bit contrived.

It is NOT incorrect to make reasonable inferences from a casually
posed question.

>Jerry claimed that this was a problem in the 2-column case:

On May 12, 12:59 pm, Jerry Stuckle wrote:

> >
Not necessarily. What happens if he has two entries with (2,30)?
It will fail.
>

No he claimed it was a problem FOR THESE 2 columns, amongst others.

His initial claim quoted above is what people were arguing with, not
the claim about the 3-column table. You either didn't read this or are
conveniently ignoring it. Frankly, he wasn't very clear. One minute he
had a table that was "not normalized", the next he's talking about a
3-column design that is "perfectly normalized."

>A B C
1 1 2
1 1 3

2 DB lines, 3 columns each, none is a duplicate. But considering process
on A & B only, there is a duplicate !

Oh no, this table has problems. What if you have a user in a group,
but that user has no permissions?

Which you cannot differentiate if you don't use column C in your process !

Not true at all, column C is never referenced at all! Where are you
getting this nonsense.

Here is the query revised to work with this design. You simply replace
(userid) with (DISTINCT groupid):

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

Where on earth do you see column C here???

>Next Jerry proposed a new table that had a 3 column primary key.
Not the original problem at all.

Once again, you don't know. Unless you can tell me where the OP ever
said there *only* was 2 columns.

You seem to be deluded into thinking the question should be exactly
and formally specified, which is not possible unless you decide to
say, encode it in Peano arithmetic or predicate logic and have it
automatically verifiable by a computer. Again, use your reading
comprehension skills and make reasonable inferences such as assuming
SQL is available, even though he didn't explicitly say "I am using an
SQL database." It was strongly implied however by his reference to
"SELECT".

2 columns is a reasonable inference because a) his example problem
contained 2 columns, b) he said he intended to map entries to
categories, c) his question matches a well-known and common M:N design
pattern. Of all the possible relations he could be modelling, a
many-to-many is the best fit, so it's not incorrect to provide a
solution for this case.

This very assumption was stated up front in the solution (must create
a unique index), so I don't see what you're complaining about.

In comparison, the answer you prefer did NOT mention that duplicates
could be repeated in the result set (unless DISTINCT is added). The
other unstated assumption is that the poster would only want to search
2 groups. Why are you making that presumption, given that he may have
many more than 2 groups in his db?

>No one has said Jerry's new table is not normalized. They were simply
pointing out that duplicate rows cannot exist in a normalized table,
which is why the original solution works in the op's problem.

And Jerry pointed that the original problem MAY contain other columns,
thus it is still possible to have duplicates in the 2 columns he
described, considering an (or many) other one prevents duplicating records.

Again, you're confusing 2 differing claims. See above.

>The poster EXPLICITLY said "here is an example of my data" which is
absolutely clear. "Example" is synonymous with "instance."

Absolutely not clear. Thus completely wrong.

>Example of data is just what it is, an example.

Example means "an instance of the problem." It does not mean
"whatever."

Any solution that works correctly on the problem instance is correct.
Now you can debate which solution is qualitatively best, but that is
another matter. Formally, you are wrong.

It may not be accurate,

You're willing to negate everything the poster said. Not a reasonable
thing to do.

You seem to be rejecting induction completely as a method of solving
problems, which is fairly nutty.

>or not complete, it's not the data as they exist, it's just an example.

You seem to believe that a problem is either completely, formally, and
exactly specified, or that it's totally vague. This is a false
dichotomy.

An example does not need to be "complete." It merely needs to be a
concrete workable instance of an abstract idea.

And yes, you need to disambiguate between possible data types in his
table based on how he displayed them (no quotes) and how he named them
(id).

>What if the poster were parsing a file and said, "here is an example
of my data?" Would you assume that there were extra columns?
No absolutely not.

Why not ? O_o

What if he said he needed a regexp to match some text like '2008-05-20
10:05:00 AM'. Would you assume there could be trailing strings or
spaces embedded between the month and year?

Your "anything goes" premise doesn't make sense. You need to infer
the pattern that the person is trying to model. If he could already
specify it exactly as a regexp, he wouldn't have asked the question in
the first place.

>Well, depends on the file "structure" , I would assume extra lines, I
would assume possible comments,

Why would you assume comments could exist? Just try adding comments to
a CSV file and you've corrupted it.

I would assume anything but "this file only contains the sample text and nothing else".

So you assume a C++ file could actually be parsed as a Java file.
Anything goes! Key word "assume" here. You agree that any solution
makes assumptions, hopefully usefule ones. Better to state your
assumptions than not to.

>According to you, we should not even presume that his database
supports SQL or that every entry in his actual list has the same
columns! It could be a variable list by your "logic."

My logic is "Jerry has a point, and there is no need to insult or
discriminate him". Which you don't, but you deny his point. As you wish,
but you need to proove that there is no extra columns. Which you can't.

Who insulted him? Jerry is the only one who engages in personal
attacks. You seem to be motivated by a desire to "defend his honour."

>Furthermore, he said entries belong to categories, which is
a binary, many-to-many relation (M:N) linking entry_id and
cat_id. If this were not a binary relation, then entries
would have to belong to categories plus something else.

Why not ? Why not the rights as stated by Jerry ?
An entry belong to a category and may have specific rights in it.

What if an entry belongs to a category, but that entry has no
"rights?" The only way to model this in a normalized fashion is a
2-column M:N table.

>Now if you believe the poster meant something other than what he
explicitly told us, then let's see your evidence.

I have no evidence to show you, it's not like you can say "this is what
the OP thought, if you think different proove it". You're the one that
should proove it since you give asserts (once again with "explicitel y
told us"). My only point was that you can't know, that - though I admit
it's quite clear, and there should NOT be any other columns - it's still
not explicit.

Again, make reasonable inferences. It would be impossible for the
poster to explicitly state everythingperta ining to his problem. Did he
say he was using an SQL db? Maybe it's a flat file. Did he say he was
even using a computer and not sorting paper clips? Again, induction.

>You're actually missing the greater point which is

Which is that this is a PHP newsgroup, once again.
I don't care about all your "points", I don't care about Jerry's being
right or wrong, all I care about is some behavior that should not be.
And some argues as "Jerry is the only one thinking that way" which in no
way is an argue.

So you don't understand the whole point of the thread and don't care.
No wonder you're so confused.

>>Any chance this thread can stop, or continue in the sql newsgroup ?

*highly pissed*

Funny, posting multiple times to a thread that you wish would end.
Highly "logical."

This was my very first post on this thread...
And yet I won't stop as long as many people continue their ideas of "The
OP described the problem THAT way".

4 posts in a thread you wish would end! Keep it up, it's working!

I guess in some surreal universe posting additional messages actually
makes the thread shorter, rather than longer. Anything's possible in
Guillaume-o-verse!

Mitch

[Mitch Sherman]

On Mon, 19 May 2008 19:58:41 -0400, Jerry Stuckle
<js*******@attg lobal.netwrote:

>Mike Lahey wrote:
No, I NEVER SAID COUNT DISTINCT is a MySQL extension. Learn to read, idiot.

What you said was that an SQL query like this wasn't standard:

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

You went to great lengths to assert this "fact":

>Mike Lahey wrote:

>>Yes, you would use count(DISTINCT entry_id, category_id) in the
HAVING clause.

Jerry Stuckle wrote:

Which is not standard SQL and will not work in all databases. It is a
MySQL extension, and probably will work only in that database. The
correct solution is portable across all databases.

Jerry Stuckle wrote:

More show of ignorance. I never said COUNT DISTINCT wasn't defined in
ANSI SQL. I said the HAVING clause is NOT legal in ANSI SQL. But
you're too stoopid to understand why. And I'm NOT going to get into it
in a PHP newsgroup.

Totally wrong. This is completely valid SQL. It is not a "MySQL
extension."

You are trying real hard to spread some Jerrytastic FUD around.

If you still doubt, try it out here at the online SQL parser:

http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm

>On May 15, 10:50 am, Jerry Stuckle <jstuck...@attg lobal.netwrote:

> CoreyJansen wrote:
Jerry's approach results in a "cartesian explosion."

Then you have a broken database server. You need to report
that as a bug to MySQL ASAP. A lot of people depend self-
join queries like this!

Again, put up or shut up. Let's your evidence that this ia a bug. Oh
that's right, you haven't got any!

Crackpot.

F'ing idiot.

Oh of course, anyone who tries to help you and correct your error is
an "idiot!"

Mitch

[Mitch Sherman]

On Tue, 20 May 2008 17:07:13 +0200, Guillaume
<gg*****@NOSPAM .gmail.com.INVA LIDwrote:

>Mitch Sherman wrote :
For this case. Which was Jerry's problem at first. Not (yet) talking
about normalized data and such, Jerry just said the proposed solution
was not correct, for an other case, if those "reasonable inferences"
were actually wrong.

Ah but this objection contains a hidden and incorrect assumption. The
original objections were:

1. "What happens if he has two entries with (2,30)"

2. "If it is not normalized, [it] will not work."

In 1, the entry tuple contains only 2 values. This is the 2 column
case. In 2, a non-normalized table in this context is one that
contains duplicated rows (rows, not just some columns). So this is
also the 2 column case.

Now it sounds like possibly a valid objection. But that is not quite
true, because there is no relational design (except a severely
pathological one) that requires redundant rows. The first step in
normalization is to remove dupliate rows. Since duplicates are never
needed to maintain logical consistency, they can be safely removed.
Doing that as the first step is thus not an error and is actually an
optimization.

To take another example: If I ask you to find the smallest element in
an array, would removing duplicate elements first be an error? No. The
db case is much stronger, since no table needs to house dupes in any
canonical design.

Playing devil's advocate, what if different threads insert (ent_id,
cat_id) rows constantly and are unable to handle a "key exists"
exception. I would argue that is not optimal and that code should be
fixed to properly handle the exception instead.

Later Jerry brough up a new table with 3 columns, and I agree with
Jerry that the solution would need a slight tweak to handle that case.
But this was mentioned explicitly in the original solution.

All we have to do is ask the OP which design he was using. Would you
care to wager cash on his actual table layout? I would be willing to
bet good money that (cat_id, ent_id) can be unique'd. (You could have
a one-column primary key called relation_id as in some designs, but
that would not prevent a unique index on the 2 entity columns.)

>I can quote him later on:
"And the query from comp.databases. mysql works correctly - in both
cases. Yours may work - but it isn't the best."

Problem is, the query in c.d.m did not actually work completely with
duplicates and did not state that up front. It needs to use SELECT
DISTINCT instead of SELECT to handle that case fully.

Further, that solution only works if you search a small number of
groups (eventually you hit a limit on joined tables), whereas the
original can handle many more. This is another case to consider, so
one solution cannot be said to work in "more cases" than the other.

>This very assumption was stated up front in the solution (must create
a unique index), so I don't see what you're complaining about.

I agree, but it's still not a solution, ain't it ?
"I have a problem" "Get that requirement, that solution and it's
solved"... yeah, but what if the OP cannot use that requirement, cause
his model is not just as expected, cause he wasn't clear enough ?
I'm not really willing to talk about database, I prefer dealing with a
general issue.

No db requires the presence of duplicates, so it's not inconsistent to
remove them.

>Let's say (and get back to PHP) someone is willing to do something he
can't cause of safe mode, without saying it (just saying here "I have
this error" which would be a safe mode issue).
One can reply "turn off safe mode, use that, and it's okay". Good reply,
but what if that user cannot turn safe mode, like someone just hosted by
his FAI ? Then it's not a solution, it just *might* have been one.

Yes it's reasonable to suggest turning off safe_mode, since that would
be the most direct solution and wouldn't require potentially
convoluted code rewrites. Overall it is the optimal path.

But, some people do say, "I can't turn off safe_mode," so in that
case, telling them to enable it would be an error.

But database tables don't need dupes (unless it's hopelessly
pathological). I don't think it's reasonable to assume a pathological
constraint in the absence of any indication one exists.

For instance, what if he can't use the letter 'F' in any SQL query, or
each SQL command needs to be exactly 60 chars long for some unknown
bizarre reason (maybe he's writing an SQL injection attack that has to
be constructed very carefully)? Why address a contrivance like that if
it wasn't stated. After all, people are trying to give advice based on
best practices.

>In fact, this thread should have turned into a "Please describe your
design exactly so you'll get the most accurate answer". Or, better, "ask
in a DB newsgroup". Which Jerry did, and he was flamed because of his
reply.

Anyway, this group's charter states that sql is not off-topic. As you
can see several replies in this there included PHP code.

>Though he was quite rude in his way, and flamed in return as well.
I guess nothing would have happened if he just started by saying this
"your solution may work, but it isn't the best".
(I skipped all parts related to pure SQL discussions, cause as I said:
- This is not the good NG,
- I'm not a MySQL expert and only use it in a casual way,
- I thus don't want to argue with things cause I may be wrong anyway.)

Mitch

[Tim Roberts]

Mitch Sherman <mi***********@ hush.aiwrote:

>On Mon, 19 May 2008 19:58:41 -0400, Jerry Stuckle
<js*******@att global.netwrote :

>>Mike Lahey wrote:
No, I NEVER SAID COUNT DISTINCT is a MySQL extension. Learn to read, idiot.

What you said was that an SQL query like this wasn't standard:

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

You went to great lengths to assert this "fact":

>>Mike Lahey wrote:
Yes, you would use count(DISTINCT entry_id, category_id) in the
HAVING clause.

Jerry Stuckle wrote:
Which is not standard SQL and will not work in all databases. It is a
MySQL extension, and probably will work only in that database. The
correct solution is portable across all databases.

Jerry Stuckle wrote:

>More show of ignorance. I never said COUNT DISTINCT wasn't defined in
ANSI SQL. I said the HAVING clause is NOT legal in ANSI SQL. But
you're too stoopid to understand why. And I'm NOT going to get into it
in a PHP newsgroup.

Totally wrong. This is completely valid SQL. It is not a "MySQL
extension."

No, in this case, Jerry is right and you are wrong. In strict ANSI SQL,
any column reference in the HAVING clause must either be part of the result
set or one of the grouped columns. "groupid" is not present in the result
set, nor is it grouped.
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.

[Mitch Sherman]

On Thu, 22 May 2008 04:39:17 GMT, Tim Roberts <ti**@probo.com wrote:

>No, in this case, Jerry is right and you are wrong. In strict ANSI SQL,
any column reference in the HAVING clause must either be part of the result
set or one of the grouped columns. "groupid" is not present in the result
set, nor is it grouped.

No, in SQL-92, aggregate functions like SUM and COUNT in the HAVING
clause may reference source columns, so this query is valid:

"Each <column referencecontai ned in a <subqueryin the <search
conditionthat references a column of T shall reference a
grouping column of T or shall be specified within a <set function
specification>. "

SUM, AVG, COUNT, etc. are set function specifications.

So for example, SUM operates on all the individual rows within the
group:

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, 2, 'read'), (1, 3, 'read'),
(2, 1, 'read'), (2, 2, 'read'), (2, 3, 'read'),
(3, 1, 'read'), (3, 2, 'read'), (3, 3, 'read');

SELECT userid FROM test GROUP BY userid HAVING SUM(userid) = 9;

Returns:

userid
------
3

(1 row)

SELECT userid FROM test GROUP BY userid
HAVING SUM(userid) + SUM(groupid) = 12;

Rreturns:

userid
------
2

(1 row)

It works per standard in MySQL, PostgreSQL, Oracle.

Mitch

[Jerry Stuckle]

Tim Roberts wrote:

Mitch Sherman <mi***********@ hush.aiwrote:

>On Mon, 19 May 2008 19:58:41 -0400, Jerry Stuckle
<js*******@att global.netwrote :

>>Mike Lahey wrote:
No, I NEVER SAID COUNT DISTINCT is a MySQL extension. Learn to read, idiot.

What you said was that an SQL query like this wasn't standard:

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

You went to great lengths to assert this "fact":

>>>Mike Lahey wrote:
Yes, you would use count(DISTINCT entry_id, category_id) in the
HAVING clause.
Jerry Stuckle wrote:
Which is not standard SQL and will not work in all databases. It is a
MySQL extension, and probably will work only in that database. The
correct solution is portable across all databases.

Jerry Stuckle wrote:

>>More show of ignorance. I never said COUNT DISTINCT wasn't defined in
ANSI SQL. I said the HAVING clause is NOT legal in ANSI SQL. But
you're too stoopid to understand why. And I'm NOT going to get into it
in a PHP newsgroup.

Totally wrong. This is completely valid SQL. It is not a "MySQL
extension."

No, in this case, Jerry is right and you are wrong. In strict ANSI SQL,
any column reference in the HAVING clause must either be part of the result
set or one of the grouped columns. "groupid" is not present in the result
set, nor is it grouped.

Tim,

Don't bother arguing with the idiot. He'll never admit that he's wrong.

He's even quoting a standard that has been out of date for over 10 years!

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[Guillaume]

Jerry Stuckle a écrit :

Don't bother arguing with the idiot.

Jerry, shut up a bit with all those extraordinary argues... It's boring
and you don't need such replies.

Regards,
--
Guillaume