Help writing SQL statement in PHP script

Chuck Cheeze wrote:

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...

This isn't a PHP question - it's a database question. You need a group
for your database (i.e. if it's MySQL, comp.databases.mysql).

You'll get good answers there because that's where the SQL experts hang
out. The answers posted here so far are incorrect.

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

Jun 2 '08 #5

Mike Lahey

Jerry Stuckle wrote:

You'll get good answers there because that's where the SQL experts hang
out. The answers posted here so far are incorrect.

Pay attention to the posts. Peter's solution will work.

You can learn about grouping queries at this tutorial page:
http://www.w3schools.com/sql/sql_groupby.asp

Jun 2 '08 #6

On May 10, 11:06 pm, Chuck Cheeze <chadcrow...@gmail.comwrote:

>
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.

SELECT t1.entry_id
FROM theTable AS t1 LEFT JOIN theTable AS t2
ON t1.entry_id = t2.entry_id
WHERE t1.cat_id = 30 AND t2.cat_id = 35

Cheers,
NC

Jun 2 '08 #7

Captain Paralytic

On 12 May, 02:36, Mike Lahey <mikey6...@yahoo.comwrote:

Jerry Stuckle wrote:
You'll get good answers there because that's where the SQL experts hang
out. The answers posted here so far are incorrect.

Pay attention to the posts. Peter's solution will work.

It may work, but that does not make it the "correct" way to do it.

Jun 2 '08 #8

aguillacutty

On May 12, 9:20 am, Captain Paralytic <paul_laut...@yahoo.comwrote:

On 12 May, 02:36, Mike Lahey <mikey6...@yahoo.comwrote:

Pay attention to the posts. Peter's solution will work.

It may work, but that does not make it the "correct" way to do it.

Oh no, the answer is correct, but there are alternatives. What you
must to realize is if the OP decides to match not 2, but 30 or 500
category IDs, you must use group by. This is more universal solution.

--
"Man who lives in glass house should change clothes in basement."

Jun 2 '08 #9

Mike Lahey wrote:

Jerry Stuckle wrote:
>You'll get good answers there because that's where the SQL experts
hang out. The answers posted here so far are incorrect.

Pay attention to the posts. Peter's solution will work.

You can learn about grouping queries at this tutorial page:
http://www.w3schools.com/sql/sql_groupby.asp

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

Nothing in the description of the problem prohibits such an occurrence.

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

Jun 2 '08 #10

Jerry Stuckle wrote:

Chuck Cheeze wrote:
>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...

This isn't a PHP question - it's a database question. You need a group
for your database (i.e. if it's MySQL, comp.databases.mysql).

You'll get good answers there because that's where the SQL experts hang
out. The answers posted here so far are incorrect.

That of course includes Jerry's.

I cant remember which paradox he is quiting..

Anyway the classic one is 'everything I say is false, Is this statement
true or false?

When dealing with Jerry, it is not a hypothetical question.

Jun 2 '08 #11

Captain Paralytic wrote:

On 12 May, 02:36, Mike Lahey <mikey6...@yahoo.comwrote:
>Jerry Stuckle wrote:
>>You'll get good answers there because that's where the SQL experts hang
out. The answers posted here so far are incorrect.
Pay attention to the posts. Peter's solution will work.

It may work, but that does not make it the "correct" way to do it.

The only correct solution is the one that is issued with a certificate
of Papal infallibility.

I.e. the one that comes from the self appointed God/pope of
comp.lang.PHP, i.e. Jerry.

For the rest of us, what works is good enough.

Jun 2 '08 #12

Mike Lahey

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

Nothing in the description of the problem prohibits such an occurrence.

That was addressed in the response, but it looks like you missed it.

You make the mistake of assuming the data is not normalized. I think the
point of Peter's post was to ensure that the data was indeed normalized.

Jun 2 '08 #13

Mike Lahey wrote:

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

Nothing in the description of the problem prohibits such an occurrence.

That was addressed in the response, but it looks like you missed it.

You make the mistake of assuming the data is not normalized. I think the
point of Peter's post was to ensure that the data was indeed normalized.

Which, if it is not normalized, will not work.

As I said - there was nothing in the original problem description to
prohibit it. And the correct answer doesn't require unique entries.

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

Jun 2 '08 #14

On Tue, 13 May 2008 12:02:59 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>Mike Lahey wrote:
>That was addressed in the response, but it looks like you missed it.

You make the mistake of assuming the data is not normalized. I think the
point of Peter's post was to ensure that the data was indeed normalized.

Which, if it is not normalized, will not work.

As I said - there was nothing in the original problem description to
prohibit it. And the correct answer doesn't require unique entries.

That's impossible if you have a unique index as the poster stated.

In a classic one-to-many (1:N) relationship, you shouldn't have any
duplicates. The recommendation to normalize your data is sound advice
which you seem to be disagreeing with.

The query you're suggesting will incorrectly overstate the number of
unique entries matched if there are duplicates. You should use "SELECT
DISTINCT" in that case.

Mitch

Jun 2 '08 #15

williamitefalls

On May 14, 3:32 am, Mitch Sherman <mitch.sher...@hush.aiwrote:

That's impossible if you have a unique index as the poster stated.

In a classic one-to-many (1:N) relationship, you shouldn't have any
duplicates. The recommendation to normalize your data is sound advice
which you seem to be disagreeing with.

The query you're suggesting will incorrectly overstate the number of
unique entries matched if there are duplicates. You should use "SELECT
DISTINCT" in that case.

Mitch

I have to agree. It's always easier to get the most out of your
database when you've normalized it properly. For those who are
interested, MySQL has a good introduction to this on their development
portal:

http://dev.mysql.com/tech-resources/...alization.html

Jun 2 '08 #16

Mitch Sherman wrote:

On Tue, 13 May 2008 12:02:59 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:
>Mike Lahey wrote:
>>That was addressed in the response, but it looks like you missed it.

You make the mistake of assuming the data is not normalized. I think the
point of Peter's post was to ensure that the data was indeed normalized.
Which, if it is not normalized, will not work.

As I said - there was nothing in the original problem description to
prohibit it. And the correct answer doesn't require unique entries.

That's impossible if you have a unique index as the poster stated.

In a classic one-to-many (1:N) relationship, you shouldn't have any
duplicates. The recommendation to normalize your data is sound advice
which you seem to be disagreeing with.

The query you're suggesting will incorrectly overstate the number of
unique entries matched if there are duplicates. You should use "SELECT
DISTINCT" in that case.

Mitch

No argument.

But that was an additional condition the poster required - not the
original op. And that's what makes it incorrect.

It may be very possible to have a perfectly normalized database but
duplicate columns here - there could be a third column which is also
part of the primary key, but not pertinent to this question, so was not
asked.

For instance, you might have:

Game HomeTeam VisitingTeam
1 5 6
2 7 8
3 5 8
4 3 4
5 5 6

You want to know which teams played both team 6 and team 8. The Game
column is not pertinent to the question - but is critical to the game
design, because teams often play each other more than once.

In this case your query would fail. The one in comp.databases.mysql
would work correctly. Which is why I directed him there.

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

Jun 2 '08 #17

Mike Lahey

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.

It may be very possible to have a perfectly normalized database but
duplicate columns here - there could be a third column which is also
part of the primary key, but not pertinent to this question, so was not
asked.

For instance, you might have:

Game HomeTeam VisitingTeam
1 5 6
2 7 8
3 5 8
4 3 4
5 5 6

You want to know which teams played both team 6 and team 8. The Game
column is not pertinent to the question - but is critical to the game
design, because teams often play each other more than once.

This is not the same relationship. Teams can play each other more than
once, but a well-formed set cannot contain the same element twice.

Pay attention to the problem description which stated that entry_id's
belong to cat_id's. In your example however, HomeTeam and VisitingTeam
have parity instead. Re-read the original question:

Chuck Cheeze 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

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.

The OP wanted to indicate membership in a group. A membership relation
does not contain duplicates. Your query wont work in your example
because it doesn't report which visitors played those teams, since each
team can either be at home or away.

You've changed the problem, hence you need a new query.

Jun 2 '08 #18

Mike Lahey 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.

Believe me - I know all about database design. I've been doing it for
over 20 years.

>It may be very possible to have a perfectly normalized database but
duplicate columns here - there could be a third column which is also
part of the primary key, but not pertinent to this question, so was
not asked.

For instance, you might have:

Game HomeTeam VisitingTeam
1 5 6
2 7 8
3 5 8
4 3 4
5 5 6

You want to know which teams played both team 6 and team 8. The Game
column is not pertinent to the question - but is critical to the game
design, because teams often play each other more than once.

This is not the same relationship. Teams can play each other more than
once, but a well-formed set cannot contain the same element twice.

Ah, but it is. There is nothing in the ops statement that prohibits
such a construct. It is completely normalized and correct.

Pay attention to the problem description which stated that entry_id's
belong to cat_id's. In your example however, HomeTeam and VisitingTeam
have parity instead. Re-read the original question:

I am paying attention to the problem description. But you're reading
more into it than exists.

Chuck Cheeze 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
>
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.

The OP wanted to indicate membership in a group. A membership relation
does not contain duplicates. Your query wont work in your example
because it doesn't report which visitors played those teams, since each
team can either be at home or away.

You've changed the problem, hence you need a new query.

That depends on a lot of things. For instance, what if that third
column represents a site the group belongs to (to keep it on the web,
anyway).

And the query from comp.databases.mysql works correctly - in both cases.
Yours may work - but it isn't the best.

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

Jun 2 '08 #19

Corey Jansen

I tried both queries, and the result is Jerry's method produces very
strange results. The normalized approach posted by petersprc does give
the expected result though.

For a table containing a a few thousand records with duplicates, Jerry's
query returned 200 million rows (yes 200 million) after running for
about 2 minutes. That's more rows than there were in the original table.

I copied the query directly into a test case.

DROP PROCEDURE IF EXISTS setup;

DELIMITER //

CREATE PROCEDURE setup ()
BEGIN
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS test;
CREATE TABLE test (entry_id int,
cat_id int);
WHILE i < 10000 DO
INSERT INTO test VALUES (2, 30),
(2, 35), (3, 30), (3, 35);
SET i = i + 1;
END WHILE;
END;

//

DELIMITER ;

CALL setup();

DROP TABLE IF EXISTS result;

CREATE TABLE result AS
SELECT a.entry_id
FROM test a
INNER JOIN test b
ON a.entry_id = b.entry_id
WHERE a.cat_id = 30
AND b.cat_id = 35;

The output is:

Query OK, 200000000 rows affected (2 min 6.35 sec)
Records: 200000000 Duplicates: 0 Warnings: 0

Jerry's approach results in a "cartesian explosion."

--
Corey Jansen
cc*****@gmail.com

Jun 2 '08 #20

vkayute

On May 15, 12:27 am, Corey Jansen <ccj9...@gmail.comwrote:

Jerry's approach results in a "cartesian explosion."

Which is exactly the problem database normalization is designed to
prevent.

If only Mr. Stuckle had listened to what 10 people told him already.
Obstinacy is his best policy it seems :)

Yet another lesson in "Why You Should Use Proper Database Design."

Jun 2 '08 #21

Corey Jansen wrote:

I tried both queries, and the result is Jerry's method produces very
strange results. The normalized approach posted by petersprc does give
the expected result though.

For a table containing a a few thousand records with duplicates, Jerry's
query returned 200 million rows (yes 200 million) after running for
about 2 minutes. That's more rows than there were in the original table.

Those that can, do. Those that can't, teach. ;-)

If I had a tenner for every 'theoretically correct' approach that has
resulted in hgue software size, or machine overhead, or just plain not
working..

I copied the query directly into a test case.

DROP PROCEDURE IF EXISTS setup;

DELIMITER //

CREATE PROCEDURE setup ()
BEGIN
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS test;
CREATE TABLE test (entry_id int,
cat_id int);
WHILE i < 10000 DO
INSERT INTO test VALUES (2, 30),
(2, 35), (3, 30), (3, 35);
SET i = i + 1;
END WHILE;
END;

//

DELIMITER ;

CALL setup();

DROP TABLE IF EXISTS result;

CREATE TABLE result AS
SELECT a.entry_id
FROM test a
INNER JOIN test b
ON a.entry_id = b.entry_id
WHERE a.cat_id = 30
AND b.cat_id = 35;

The output is:

Query OK, 200000000 rows affected (2 min 6.35 sec)
Records: 200000000 Duplicates: 0 Warnings: 0

Jerry's approach results in a "cartesian explosion."

I'll remember that phrase...

>

Jun 2 '08 #22

vkayute

On May 14, 7:21 pm, Mike Lahey <mikey6...@yahoo.comwrote:

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.

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.)

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.

Jun 2 '08 #23

Corey Jansen wrote:

I tried both queries, and the result is Jerry's method produces very
strange results. The normalized approach posted by petersprc does give
the expected result though.

For a table containing a a few thousand records with duplicates, Jerry's
query returned 200 million rows (yes 200 million) after running for
about 2 minutes. That's more rows than there were in the original table.

I copied the query directly into a test case.

DROP PROCEDURE IF EXISTS setup;

DELIMITER //

CREATE PROCEDURE setup ()
BEGIN
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS test;
CREATE TABLE test (entry_id int,
cat_id int);
WHILE i < 10000 DO
INSERT INTO test VALUES (2, 30),
(2, 35), (3, 30), (3, 35);
SET i = i + 1;
END WHILE;
END;

//

DELIMITER ;

CALL setup();

DROP TABLE IF EXISTS result;

CREATE TABLE result AS
SELECT a.entry_id
FROM test a
INNER JOIN test b
ON a.entry_id = b.entry_id
WHERE a.cat_id = 30
AND b.cat_id = 35;

The output is:

Query OK, 200000000 rows affected (2 min 6.35 sec)
Records: 200000000 Duplicates: 0 Warnings: 0

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!

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))');

// Insert 10K rows of data
for ($i = 1; $i <= 100; $i++)
for ($j = 1; $j<= 100; $j++)
mysql_query("INSERT INTO test(groupid, userid) VALUES($i, $j)");

// Now lets get rid of some of the data so we have meaningful results
mysql_query('DELETE FROM test WHERE groupid=32 AND MOD(userid, 3) 0');
mysql_query('DELETE FROM test WHERE groupid=38 AND MOD(userid, 4) 0');

// Pull the matching data from the table

$result = mysql_query('SELECT a.userid AS userid ' .
'FROM test a ' .
'INNER JOIN test b ' .
'ON a.userid = b.userid ' .
'WHERE a.groupid = 32 ' .
'AND b.groupid = 35');
echo 'Rows found: ' . mysql_num_rows($result) . "\n";
while ($data = mysql_fetch_array($result))
echo $data['userid'] . " ";
mysql_close();
?>

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

Jun 2 '08 #24

vk*****@gmail.com wrote:

On May 15, 12:27 am, Corey Jansen <ccj9...@gmail.comwrote:
>Jerry's approach results in a "cartesian explosion."

Which is exactly the problem database normalization is designed to
prevent.

If only Mr. Stuckle had listened to what 10 people told him already.
Obstinacy is his best policy it seems :)

Yet another lesson in "Why You Should Use Proper Database Design."

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. There could be one or more additional columns to
determine uniqueness, for instance.

And people wonder why I send folks to comp.databases.mysql for MySQL
questions - that's where the REAL experts hang out.

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

Jun 2 '08 #25

The Natural Philosopher wrote:

Corey Jansen wrote:
>I tried both queries, and the result is Jerry's method produces very
strange results. The normalized approach posted by petersprc does give
the expected result though.

For a table containing a a few thousand records with duplicates,
Jerry's query returned 200 million rows (yes 200 million) after
running for about 2 minutes. That's more rows than there were in the
original table.

Those that can, do. Those that can't, teach. ;-)

And those who can't teach become philosophers.

If I had a tenner for every 'theoretically correct' approach that has
resulted in hgue software size, or machine overhead, or just plain not
working..

If I had a tenner for every good comment you made, I'd be broke.
However, if I had ten cents for every stupid remark you made, I could
retire.

>
>I copied the query directly into a test case.

DROP PROCEDURE IF EXISTS setup;

DELIMITER //

CREATE PROCEDURE setup ()
BEGIN
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS test;
CREATE TABLE test (entry_id int,
cat_id int);
WHILE i < 10000 DO
INSERT INTO test VALUES (2, 30),
(2, 35), (3, 30), (3, 35);
SET i = i + 1;
END WHILE;
END;

//

DELIMITER ;

CALL setup();

DROP TABLE IF EXISTS result;

CREATE TABLE result AS
SELECT a.entry_id
FROM test a
INNER JOIN test b
ON a.entry_id = b.entry_id
WHERE a.cat_id = 30
AND b.cat_id = 35;

The output is:

Query OK, 200000000 rows affected (2 min 6.35 sec)
Records: 200000000 Duplicates: 0 Warnings: 0

Jerry's approach results in a "cartesian explosion."

I'll remember that phrase...
>>

ROFLMAO. Never heard of a cartesian product?

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

Jun 2 '08 #26

vk*****@gmail.com wrote:

On May 14, 7:21 pm, Mike Lahey <mikey6...@yahoo.comwrote:
>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.

>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.

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.

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

Jun 2 '08 #27

On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>vk*****@gmail.com wrote:
>On May 15, 12:27 am, Corey Jansen <ccj9...@gmail.comwrote:
>>Jerry's approach results in a "cartesian explosion."

Which is exactly the problem database normalization is designed to
prevent.

If only Mr. Stuckle had listened to what 10 people told him already.
Obstinacy is his best policy it seems :)

Yet another lesson in "Why You Should Use Proper Database Design."

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.

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.

There could be one or more additional columns to determine uniqueness, for instance.

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

Jun 2 '08 #28

On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>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

Jun 2 '08 #29

Mitch Sherman wrote:

On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:
>vk*****@gmail.com wrote:
>>On May 15, 12:27 am, Corey Jansen <ccj9...@gmail.comwrote:
Jerry's approach results in a "cartesian explosion."
Which is exactly the problem database normalization is designed to
prevent.

If only Mr. Stuckle had listened to what 10 people told him already.
Obstinacy is his best policy it seems :)

Yet another lesson in "Why You Should Use Proper Database Design."

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!

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.

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.

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.

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.

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.

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.

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

Jun 2 '08 #30

On Thu, 15 May 2008 12:01:02 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>vk*****@gmail.com wrote:
>On May 14, 7:21 pm, Mike Lahey <mikey6...@yahoo.comwrote:
>>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

Jun 2 '08 #31

Mitch Sherman wrote:

On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:
>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.
js*******@attglobal.net
==================

Jun 2 '08 #32

oxision

On May 15, 9:49 pm, Mitch Sherman <mitch.sher...@hush.aiwrote:

On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle

<jstuck...@attglobal.netwrote:
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]

Jun 2 '08 #33

oxision

On May 15, 9:52 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:

Mitch Sherman wrote:
On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
<jstuck...@attglobal.netwrote:
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]

Jun 2 '08 #34

oxision

On May 15, 10:06 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:

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]

Jun 2 '08 #35

Guillaume

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

Jun 2 '08 #36

Guillaume

ox*****@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

Jun 2 '08 #37

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.

Jun 2 '08 #38

Guillaume

The Natural Philosopher a écrit :

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.

Yeah and he seems to be right on that cartesian explosion thing. Anyway
I'm not on c.d.mysql, I didn't saw his reply and proposition there so I
did not followed this part :p

--
Guillaume

Jun 2 '08 #39

Guillaume wrote:

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*

Thank you. But please try running the code I posted. It will show you
they are wrong on that, also. There is no cartesian explosion.

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

Jun 2 '08 #40

Guillaume

Jerry Stuckle a écrit :

Thank you. But please try running the code I posted. It will show you
they are wrong on that, also. There is no cartesian explosion.

No Jerry, I don't want to. This is a PHP ng, remember ? I'm not reading
c.d.mysql, there is a reason. I use it everyday but such topics are
above the casual use I have of it - actually it took me until the very
last posts to completely understand your point about the 2 columns.

My point wasn't to make you be right or wrong, actually as I kinda
"insulted" the poster, there was just no point (argueing by being rude
to the previous poster just ruin the argue imo - may it be the best and
most accurate argue). I just replied cause I was pissed and I wanted to
yell.

Well, there was no point, except for this sentence:
*Any chance this thread can stop, or continue in the sql newsgroup ?*

--
Guillaume

Jun 2 '08 #41

ox*****@yahoo.com wrote:

On May 15, 10:06 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
>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]

Did you try to run the code? I doubt it. No, I KNOW you didn't. Or
you wouldn't make such a stupid comment.

But here's code with duplicates in the (userid, groupid) columns, yet it
still works correctly. But Peter's fails:

<?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')
or die("Can't drop table: " . mysql_error());
mysql_query('CREATE TABLE test (groupid INT NOT NULL, ' .
'userid INT NOT NULL, extra INT NOT NULL, ' .
'PRIMARY KEY(groupid, userid, extra))')
or die ("Can't create table: " . mysql_error());

// Insert 10K rows of data
for ($i = 1; $i <= 50; $i++)
for ($j = 1; $j<= 50; $j++)
for ($k = 1; $k < 4; $k++)
mysql_query("INSERT INTO test(groupid, userid, extra) " .
"VALUES($i, $j, $k)")
or die("Can't insert values $i, $j: " . mysql_error());

// Now lets get rid of some of the data so we have meaningful results
mysql_query('DELETE FROM test WHERE groupid=32 AND 'MOD(userid, 3) 0')
or die("Can't delete: " . mysql_error());
mysql_query('DELETE FROM test WHERE groupid=38 AND MOD(userid, 4) 0')
or die("Can't delete: " . mysql_error());

// Pull the matching data from the table
$result = mysql_query('SELECT a.userid AS userid ' .
'FROM test a ' .
'INNER JOIN test b ' .
'ON a.userid = b.userid ' .
'WHERE a.groupid = 32 ' .
'AND b.groupid = 35')
or die("Can't SELECT: " . mysql_error());
echo 'Rows found: ' . mysql_num_rows($result) . "\n";
while ($data = mysql_fetch_array($result))
echo $data['userid'] . " ";
mysql_close();
?>

The query is the same. It does list the duplicates, but if you don't
want them, it's a simple matter to add DISTINCT (which works even if
there are no duplicates).

Without DISTINCT:

Rows found: 144
3 3 3 3 3 3 3 3 3 6 6 6 6 6 6 6 6 6 9 9 9 9 9 9 9 9 9 12 12 12 12 12 12
12 12 12 15 15 15 15 15 15 15 15 15 18 18 18 18 18 18 18 18 18 21 21 21
21 21 21 21 21 21 24 24 24 24 24 24 24 24 24 27 27 27 27 27 27 27 27 27
30 30 30 30 30 30 30 30 30 33 33 33 33 33 33 33 33 33 36 36 36 36 36 36
36 36 36 39 39 39 39 39 39 39 39 39 42 42 42 42 42 42 42 42 42 45 45 45
45 45 45 45 45 45 48 48 48 48 48 48 48 48 48

With DISTINCT:

Rows found: 16
3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48

Please show me how Peter's example works here. But you can't. Because
it doesn't.

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

Jun 2 '08 #42

ox*****@yahoo.com wrote:

On May 15, 9:49 pm, Mitch Sherman <mitch.sher...@hush.aiwrote:
>On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle

<jstuck...@attglobal.netwrote:
>>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]

Classic troll. Sticks his foot in his mouth and now trying to blame it
on the other person.

You really should try the code I posted. Show me where there is a
cartesian product.

But before you do, stand up and take a load off your brain. You're
obviously too stupid to understand anything sitting down.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 2 '08 #43

The Natural Philosopher wrote:

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.

No, but I'm about ready to killfile you - stoopid troll.

If I had a tenner for every good answer you posted, I'd be broke. But
if I had ten cents for every stupid remark you've posted, I could retire.

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

Jun 2 '08 #44

Jerry Stuckle wrote:

The Natural Philosopher wrote:
>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.

No, but I'm about ready to killfile you - stoopid troll.

If I had a tenner for every good answer you posted, I'd be broke. But
if I had ten cents for every stupid remark you've posted, I could retire.

Well there you go. Another lie.

Jun 2 '08 #45

Guillaume wrote:

The Natural Philosopher a écrit :
>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.
Yeah and he seems to be right on that cartesian explosion thing. Anyway
I'm not on c.d.mysql, I didn't saw his reply and proposition there so I
did not followed this part :p

Please try the code I've posted (two versions now). You will see there
is no cartesian explosion.

And one other thing - Corey's proc LOOKS like a cartesian explosion due
to the data - he had 10K rows of each of 4 value, so the result is
returning multiple values. This would not be a normal database design
:-). But in any case, adding the DISTINCT keyword to the query returns
only 2 values (which wouldn't be wrong in the original query, either).

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

Jun 2 '08 #46

The Natural Philosopher wrote:

Jerry Stuckle wrote:
>The Natural Philosopher wrote:
>>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.

No, but I'm about ready to killfile you - stoopid troll.

If I had a tenner for every good answer you posted, I'd be broke. But
if I had ten cents for every stupid remark you've posted, I could retire.

Well there you go. Another lie.

Another ten cents.

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

Jun 2 '08 #47