Help writing SQL statement in PHP script

On Thu, 15 May 2008 12:01:02 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
>vkayute@gmail.com wrote:
>> On May 14, 7:21 pm, Mike Lahey <mikey6...@yahoo.com> wrote:
>>> Jerry Stuckle wrote:
>>>
>>>> No argument.
>>>> But that was an additional condition the poster required - not the
>>>> original op. And that's what makes it incorrect.
>>> Uniqueness is a consequence of the relationship the OP wanted to model.
>>> Best practice is to create an index, which is the correct solution, as
>>> has been pointed out several times.
>>>
>>> You should properly normalize your DB instead of working around a broken
>>> design as you're arguing for.
>>
>> Amen. Any proposed solution that skips this step is incomplete. One
>> shouldn't rely on a broken data model and expect to get good results.
>>
>
>No arguments. But based on the information given, we cannot say the
>database was not normalized.

Normalizing it first can do no harm and is certainly an improvement. A
relational table doesn't need redundant rows.

>>> The OP wanted to indicate membership in a group. A membership relation
>>> does not contain duplicates.
>>
>> Yes, by definition, a membership set has no dups. To take another
>> example, it wouldn't be proper for a student to belong to the same
>> class twice. (He could repeat the course, but that wouldn't be the
>> same class would it.)
>>
>
>It depends. For instance, you could have an additional column -
>privileges. Things like "read", "post", "upload" to determine the
>rights the user has.

Doesn't matter. Each row is still unique. Why would you specify the
same "rights" twice when once is enough? There is no relational design
that you can postulate that requires redundancy. This can always be
eliminated.

>> Using a flawed db design creates all sorts of inconsistencies which
>> are better to avoid when developing robust systems.
>>
>> Jerry's suggested query blows up when faced with duplicates, so you
>> can see how easy it is to fall into this trap.
>>
>
>My query does not blow up with there are duplicates. It works perfectly
>well. But Peter's fails in that case.
>
>And people wonder why I refer MySQL questions to comp.databases.mysql -
>where the real experts hang out.

There have been several posts pointing out your error, but you seem
desperate to cling to the idea that this is a "bug in MySQL" rather
than a flawed design. You made the same mistake Peter warned you
against. His approach is superior to yours because it both a)
normalizes (by removing dups) and b) optimizes by creating an index.
Your solution does neither and does not even properly handle duplicate
rows.

Mitch

Mitch Sherman wrote:
> On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>> Corey Jansen 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!
>
> Not at all, this is a bug in your query. It produced the same result
> here. MySQL did exactly what you told it to do. You seem desperate to
> avoid acknowledging this, resorting even to making up fictitious MySQL
> bug reports.
>
> The problem is you are self-joining using a condition that isn't
> unique and lacks a primary key reference. Sometimes this is what you
> want, but that is not the case in the original problem.
>
> Let me spell it out for you. Let's say you have rows A through F that
> contain the following values:
>
> A: (2, 30)
> B: (2, 35)
> C: (2, 30)
> D: (2, 35)
> E: (2, 30)
> F: (2, 35)
>
> There are only 6 rows in the table. Your query, however, will produce
> more than 6 matches. This is because rows A, C, and E can each be
> paired a total of 3 times. The result of the inner join is:
>
> (A, B), (A, D), (A, F)
> (C, B), (C, D), (C, F)
> (E, B), (E, D), (E, F)
>
> Now, here's how it looks in SQL:
>
> -- Create the table with 6 rows --
>
> DROP TABLE IF EXISTS test;
> CREATE TABLE test (entry_id int, cat_id int);
> INSERT INTO test (entry_id, cat_id) values
> (2, 30), (2, 35), (2, 30), (2, 35), (2, 30),
> (2, 35);
>
> -- Run the query --
>
> SELECT a.entry_id FROM test a INNER JOIN test b
> ON a.entry_id = b.entry_id WHERE a.entry_id =
> b.entry_id AND a.cat_id = 30 AND b.cat_id = 35;
>
> The result of your query is:
>
> 9 rows in set (0.00 sec)
>
> This gets worse as your table gets bigger. You end up with the
> "cartesian explosion" in the test case that you are denying exists.
>
>> This works fine (sorry about the line wraps):
>>
>> <?php
>>
>> $link = mysql_connect('localhost', 'root', 'vps11131') or die("Can't
>> connect: " . mysql_error());
>> $db = mysql_select_db('test');
>>
>> // Clear table if it existed
>> mysql_query('DROP TABLE IF EXISTS test');
>> mysql_query('CREATE TABLE test (groupid INT NOT NULL, ' .
>> 'userid INT NOT NULL, PRIMARY KEY(groupid, userid))');
>
> Your script doesn't test the same scenario at all. The table you
> created is guaranteed not to have any duplicates because you defined a
> PRIMARY KEY. This is exactly what you've been arguing against doing
> all this time, so you've basically demonstrated why uniqueness is a
> good thing.
>
> Mitch
>

Obviously you did not even try to cut and paste the code I put in there.

The results:

Rows found: 33
3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75
78 81 84 87 90 93 96 99

From a table with almost 10K rows (on purpose). Doesn't look like a
cartesian product to me.

I really suggest next time you try it before making a fool of yourself
again.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Mitch Sherman wrote:
> On Thu, 15 May 2008 12:01:02 -0400, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>> vkayute@gmail.com wrote:
>>> On May 14, 7:21 pm, Mike Lahey <mikey6...@yahoo.com> wrote:
>>>> Jerry Stuckle wrote:
>>>>
>>>>> No argument.
>>>>> But that was an additional condition the poster required - not the
>>>>> original op. And that's what makes it incorrect.
>>>> Uniqueness is a consequence of the relationship the OP wanted to model.
>>>> Best practice is to create an index, which is the correct solution, as
>>>> has been pointed out several times.
>>>>
>>>> You should properly normalize your DB instead of working around a broken
>>>> design as you're arguing for.
>>> Amen. Any proposed solution that skips this step is incomplete. One
>>> shouldn't rely on a broken data model and expect to get good results.
>>>
>> No arguments. But based on the information given, we cannot say the
>> database was not normalized.
>
> Normalizing it first can do no harm and is certainly an improvement. A
> relational table doesn't need redundant rows.
>

And there is nothing to say the table has redundant rows. Again - this
may be the table structure - we don't know:

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

There is no redundant information. However, there are duplicates for
(userid, groupid). The permissions column was not pertinent to the
original ops query, and therefore may not have been listed. In fact,
there could have been 20 additional columns. But since they were
irrelevant to the query, the op would not have listed them (which would
be the correct thing to do).


>>>> The OP wanted to indicate membership in a group. A membership relation
>>>> does not contain duplicates.
>>> Yes, by definition, a membership set has no dups. To take another
>>> example, it wouldn't be proper for a student to belong to the same
>>> class twice. (He could repeat the course, but that wouldn't be the
>>> same class would it.)
>>>
>> It depends. For instance, you could have an additional column -
>> privileges. Things like "read", "post", "upload" to determine the
>> rights the user has.
>
> Doesn't matter. Each row is still unique. Why would you specify the
> same "rights" twice when once is enough? There is no relational design
> that you can postulate that requires redundancy. This can always be
> eliminated.
>

Yes, each row is unique. But (userid, groupid) is NOT unique. See above.

>>> Using a flawed db design creates all sorts of inconsistencies which
>>> are better to avoid when developing robust systems.
>>>
>>> Jerry's suggested query blows up when faced with duplicates, so you
>>> can see how easy it is to fall into this trap.
>>>
>> My query does not blow up with there are duplicates. It works perfectly
>> well. But Peter's fails in that case.
>>
>> And people wonder why I refer MySQL questions to comp.databases.mysql -
>> where the real experts hang out.
>
> There have been several posts pointing out your error, but you seem
> desperate to cling to the idea that this is a "bug in MySQL" rather
> than a flawed design. You made the same mistake Peter warned you
> against. His approach is superior to yours because it both a)
> normalizes (by removing dups) and b) optimizes by creating an index.
> Your solution does neither and does not even properly handle duplicate
> rows.
>
> Mitch
>

And I have posted the code proving it works. Additionally, there are a
number of SQL experts over in comp.databases.mysql who also agree this
is the correct thing to do.

Now you know why I send people with SQL questions to
comp.databases.mysql. They get good answers there - not the crap given
out by rank amateurs who think they know everything that you see here.

I suggest you take this up over in comp.databases.mysql. Tell the
developers of MySQL why this won't work. They will disagree with you, also.

Shit - I have never seen so many idiots argue something is wrong without
even trying it - just because they have no f'ing idea what they're
talking about.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 15, 9:49 pm, Mitch Sherman <mitch.sher...@hush.ai> wrote:
> On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle
>
> <jstuck...@attglobal.net> wrote:
> >Corey Jansen 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!
>
> Not at all, this is a bug in your query. It produced the same result
> here. MySQL did exactly what you told it to do. You seem desperate to
> avoid acknowledging this, resorting even to making up fictitious MySQL
> bug reports.

Classic Jerry Stuckle. He is known to be a compulsive liar. I'll bet
he's thinking right now:

"Boy I've put my foot in it, but maybe I can still try hard to
convince people who don't know any better that I'm right! Maybe if I
throw a temper tantrum that will magically turn my bad advice into
good advice!"

Jerry is the only one arguing this doomed position. I don't expect his
false pride will let him admit it, so we can expect his trolling to
continue unabated. Fact is, he's still perpetuating awful
misconceptions about sql.

Ron Doyle [oxision at yahoo.com]

On May 15, 9:52 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Mitch Sherman wrote:
> > On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
> > <jstuck...@attglobal.net> wrote:
> >> I'm not arguing about proper database design. My only comment is it is
> >> IMPOSSIBLE 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.
>
> That is the critical point!

Totally incorrect. I refer you to the Wikipedia definition:

A table is in first normal form (1NF) if and only if it faithfully
represents a relation.[3] Given that database tables embody a relation-
like form, the defining characteristic of one in first normal form is
that it does not allow duplicate rows or nulls.

It would be nice if you knew something about database normalization
before you started pontificating about it. Duplicates are forbidden.
Your whole premise is based on allowing duplicate rows.

>
> > You seem to arguing that it's better to build on a potentially flawed
> > database design rather than get it right first, which is terrible
> > advice.
>
> No, I'm not. There is nothing flawed about a design which has three
> columns (of which these are only two) determining the primary key (or
> other unique value).
>
> >> There could be one or more additional columns to determine uniqueness, for instance.

That's not the design in the op's problem.

>
> >> And people wonder why I send folks to comp.databases.mysql for MySQL
> >> questions - that's where the REAL experts hang out.
>
> > This is a pointless hypothetical. If you have N columns, you can still
> > maintain uniqueness across those columns. That doesn't require
> > duplicate rows any more than the original problem which had only 2
> > columns.
>
> > Mitch
>
> No, it is not pointlessly hypothetical. It is very germane to this
> situation. We do not have all of the information - the complete
> database design, usage, etc.

If my aunt had balls, she'd be my uncle!

> The other column(s) may not be germane to the problem, so the original
> op did not list them. That is quite common - and correct - as it does
> not confuse the issue at hand with irrelevant data. There may very well
> have been 2 columns - or 20 columns or even 200 columns. You don't know
> which is correct.

And there would still be no duplicates if you normalized it.

> For instance, here's a table which could very well be the case:
>
> userid groupid permission
> 1 1 read
> 1 1 write
> 1 1 delete
> 1 2 read
> 1 3 read
>
> This is a commonly used design. The permission column is not pertinent
> to the original ops question - so it wouldn't be listed. But Peter's
> query will fail if it looks for someone who is a member if groups 1 and
> 2. The correct query works in this case just fine.

Wrong. Your example has no duplicates at all. Therefore it does not
support your assertion that a table with duplicate is somehow
"normalized."

If you add columns, then obviously you have to add those to the group
by statement in Peter's solution. You would need to do the same with
your query as well. It's pointless to change the table design and then
expect the same queries to work. Try restricting your argument to the
actual problem the op posed, instead of some hypothetical.

> My God, I've never seen someone so insistent about making false
> assumptions about someone else's code - and so stubborn about sticking
> to a bad suggestion.

Thank you for describing your position so accurately. I couldn't have
done it better myself.

> I really suggest you learn some more advanced sql - actually, the
> correct answer isn't even advanced level. I'm not sure it even makes
> intermediate level.
>
> The correct query works 100% of the time - whether there are duplicates
> or not.

Yes, it works 100% of the time, except in those cases where it doesn't
work at all.

Ron Doyle [oxision at yahoo.com]

On May 15, 10:06 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Mitch Sherman wrote:
> > Your script doesn't test the same scenario at all. The table you
> > created is guaranteed not to have any duplicates because you defined a
> > PRIMARY KEY. This is exactly what you've been arguing against doing
> > all this time, so you've basically demonstrated why uniqueness is a
> > good thing.
>
> Obviously you did not even try to cut and paste the code I put in there.
>
> The results:
>
> Rows found: 33
> 3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75
> 78 81 84 87 90 93 96 99
>
> From a table with almost 10K rows (on purpose). Doesn't look like a
> cartesian product to me.
>
> I really suggest next time you try it before making a fool of yourself
> again.

Your code doesn't test duplicates, so I fail to see how it addresses
this situation. Look at Corey's prior post for the correct test case
which shows that your query does indeed blow up.

In your example, you created a primary key which eliminated any
possibility of duplicates. If the original problem had a primary key,
it would already be normalized.

Ron Doyle [oxision at yahoo.com]

On May 15, 10:13 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Mitch Sherman wrote:
> > Normalizing it first can do no harm and is certainly an improvement. A
> > relational table doesn't need redundant rows.
>
> And there is nothing to say the table has redundant rows. Again - this
> may be the table structure - we don't know:

Oh but we do know because the table structure was posted in the
original problem. You didn't read it carefully.

Here is the actual table structure:

On May 11, 2:06 am, Chuck Cheeze <chadcrow...@gmail.com> wrote:
>
> 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

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

> There is no redundant information. However, there are duplicates for
> (userid, groupid). The permissions column was not pertinent to the
> original ops query, and therefore may not have been listed. In fact,
> there could have been 20 additional columns. But since they were
> irrelevant to the query, the op would not have listed them (which would
> be the correct thing to do).

Bad assumptions. Your query would explode even worse if there were
additional columns, so I fail to see how this supports your argument.

> > Doesn't matter. Each row is still unique. Why would you specify the
> > same "rights" twice when once is enough? There is no relational design
> > that you can postulate that requires redundancy. This can always be
> > eliminated.
>
> Yes, each row is unique. But (userid, groupid) is NOT unique. See above.

In this new design you would obviously group by userid, groupid, and
permission. Repeat for every additional column in the primary key.
Usually however, a table only has 1 or 2 columns in its primary key.
Nothing you have said contradicts the original correct solution.

>
>
> >>> Using a flawed db design creates all sorts of inconsistencies which
> >>> are better to avoid when developing robust systems.
>
> >>> Jerry's suggested query blows up when faced with duplicates, so you
> >>> can see how easy it is to fall into this trap.
>
> >> My query does not blow up with there are duplicates. It works perfectly
> >> well. But Peter's fails in that case.
>
> >> And people wonder why I refer MySQL questions to comp.databases.mysql -
> >> where the real experts hang out.
>
> > There have been several posts pointing out your error, but you seem
> > desperate to cling to the idea that this is a "bug in MySQL" rather
> > than a flawed design. You made the same mistake Peter warned you
> > against. His approach is superior to yours because it both a)
> > normalizes (by removing dups) and b) optimizes by creating an index.
> > Your solution does neither and does not even properly handle duplicate
> > rows.
>
> > Mitch
>
> And I have posted the code proving it works. Additionally, there are a
> number of SQL experts over in comp.databases.mysql who also agree this
> is the correct thing to do.
>
> Now you know why I send people with SQL questions to
> comp.databases.mysql. They get good answers there - not the crap given
> out by rank amateurs who think they know everything that you see here.
>
> I suggest you take this up over in comp.databases.mysql. Tell the
> developers of MySQL why this won't work. They will disagree with you, also.
>
> Shit - I have never seen so many idiots argue something is wrong without
> even trying it - just because they have no f'ing idea what they're
> talking about.

You're the only one making these claims. Arguing with a dishonest
person is pointless, so I'll leave it at that.

Ron Doyle [oxision at yahoo.com]

Guys, you're very stubborn with Jerry... but don't start giving out such
ridiculous asserts O_o

> That's not the design in the op's problem.
Do you know it ? No, cause he only gave 2 columns, and NEVER SAID there
*only* was those 2 columns.
All you can say is that his design *at least* has 2 columns.

> Wrong. Your example has no duplicates at all.
Seriously this whole thread is starting pissing me so I'll get straight:
GET A BRAIN !
If you want to argue with Jerry, at least be smart! OF COURSE HIS
EXAMPLE HAS DUPLICATES ! REGARDING USERID AND GROUPID ONLY !

What is the thing you don't understand in his sentence ?
> "AND STILL HAVE DUPLICATES IN THESE COLUMNS".
"IN THESE COLUMNS" are words you don't get ? Apparently yes, since you
start posting a Wikipedia (woohoo !) definition that doesn't consider
this point at all.

Jerry's point is *this* one ! There could be duplicates regarding those
*TWO* columns only ! Which would not be duplicated if there is a THIRD
column (or more), making the design clearer.

Let me rephrase it:
The originally proposed SQL does not work if there is a duplicate in
column 1 and 2. Which is highly possible IF the design includes some
MORE columns, which also is possible as the OP never said his table only
had those 2 columns.

Jerry may be wrong about the cartesian explosion point, but on this
specific topic, he's right ! The proposed answer assume that only those
2 fields are available, which may be wrong.

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

*highly pissed*
--
Guillaume

oxision@yahoo.com 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".

> You're the only one making these claims. Arguing with a dishonest
> person is pointless, so I'll leave it at that.
That is one of the part that is pissing me the most. All those claims
about Jerry being stubborn, stupid, dishonest, liar... Are they relevant
to the problem ? Not at all...

--
Guillaume

Guillaume wrote:

> That is one of the part that is pissing me the most. All those claims
> about Jerry being stubborn, stupid, dishonest, liar... Are they relevant
> to the problem ? Not at all...
>

No, but they are relevant to any solutions he proposes: Jerry would
rather *seem* to be right than *be* right.

Sometimes he is both: More alarmingly, often he isn't.

I haven't killfiled him yet, because he knows some stuff. Sadly, it's a
lot LESS than he likes to make out.

So its dangerous to rely on his advice.