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

[vkayute]

On May 15, 4:08 am, [e-mail] wrote:

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.

I was wrong here. The data model is too vague to know what table
design is being used. Jerry's solution doesn't rely on a broken model
at all.

[vkayute]

I was wrong.. Jerry's approach is fine and normalized well.

On May 15, 3:01 am, [e-mail] wrote:

[oxision]

On May 15, 11:09 pm, [e-mail] wrote:

I was wrong in this post.. Jerry's table has a 3-column key, so of
course there can be dups on 2 columns... It is an accurate scenario
that tests the problem.

Ron Doyle [oxision at yahoo.com]

[oxision]

On May 15, 11:24 pm, [e-mail] wrote:

On May 15, 10:13 pm, Jerry Stuckle <[e-mail]:

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:

Jerry is correct. We can't be sure how many columns are there, so my
post was not really relevant.

Ron Doyle [oxision at yahoo.com]

[oxision]

This post I made was rude course a lie.. Jerry is a very smart guy.
His comments in this newsgroup to everyone.

Please disregard my foolish statements.

On May 15, 10:34 pm, [e-mail] wrote:

On May 15, 9:49 pm, Mitch Sherman <[e-mail]:

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

<[e-mail]:

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

[oxision]

My post was a lie and lame. Jerry's an incredibly smart and well-
respected guy and his posts in this newsgroup are extremely useful to
everyone.

Please disregard my cilly posts such as this one.

Ron Doyle [oxision at yahoo.com]

[e-mail] wrote:

Ron Doyle [oxision at yahoo.com]

[oxision]

On May 15, 10:51 pm, [e-mail] wrote:

>

I was wrong in this previous post... Jerry's query of course works and
he pointed out several times that you use DISTINCT if you want to
unique the results... DIsregard my post.

Ron Doyle [oxision at yahoo.com]

[oxision]

On May 15, 10:51 pm, [e-mail] wrote:

On May 15, 9:52 pm, Jerry Stuckle <[e-mail]:

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.

Well I was wrong and made a mistake and didn't understand Jerry's
post. Of course he meant there were duplicates in 2 columns only, but
not across all columns in the row. Of course his table is normalized
so my post was BS. Please disregard it.

Ron Doyle [oxision at yahoo.com]

[Jamie Nelson]

On Sat, 17 May 2008 11:12:01 -0500, Jamie Nelson wrote:

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

That can't occur because the first step in the solution is to strip duplicates.

I was wrong, Jerry is correct. The first step is invalid because
duplicates may exist in the table. The problem doesn't prohibit it and so
these rows can't be removed without potentially breaking something.

--
Jamie Nelson

[Jamie Nelson]

On Sat, 17 May 2008 11:13:54 -0500, Jamie Nelson wrote:

On Tue, 13 May 2008 12:02:59 -0400, Jerry Stuckle 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.

The data is always normalized because this was accomplished in the first
step. That should be obvious. You don't need redundant rows, hence they
can be removed.

No I was wrong, they can't be removed. You might need those rows that have
duplicates in those columns.

--
Jamie Nelson

[Jamie Nelson]

On Sat, 17 May 2008 11:24:49 -0500, Jamie Nelson wrote:

Quite right.

I misunderstood Jerry. He meant dups in those 2 columns only, not an
entire row. So this quote I posted is not relevant.

--
Jamie Nelson

[Jamie Nelson]

On Sat, 17 May 2008 11:37:58 -0500, Jamie Nelson wrote:

>
Normalization can't hurt

I was incorrect, Jerry's table is normalized. It jsut has more columns.
--
Jamie Nelson

[grcassociates]

Disregard my post, Jerry was correct. I retract all my previous posts
in this thread.

On May 19, 1:04 am, "W. Jenkins" <[e-mail]:

[grcassociates]

On May 19, 1:21 am, "W. Jenkins" <[e-mail]:

Guillaume <[e-mail]:

[e-mail] 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.

No that's not right. My post was wrong. The OP gave a sample, which
could be interpreted in many different ways. It wouldn't be good to
assume this is what he meant. Better to use Jerry's solution instead
of adding new restrictions...

[grcassociates]

On May 19, 1:29 am, "W. Jenkins" <[e-mail]:

Jerry Stuckle <[e-mail]:

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"?

I was wrong.. Jerry said it was normalized then gave an example.
Pretty simple. It's clear what he was talking about.

[grcassociates]

Guillaume was right, I see that now. Sorry Guillaume, please accept my
apology and disregard my post. My objections weren't very sound. Of
course he is correct we can't assume too much about the poster's
problem because he was very vague. Attempting to shoe-horn his problem
into a particular relationship wouldn't be prudent and is unnecessary
given Jerry's solution works across all the reasonable scenarios.
On May 19, 12:55 am, "W. Jenkins" <[e-mail]:

Guillaume <[e-mail]:

[williamitefalls]

This account was a duplicate of Mitch Sherman. Sorry.

Disregard my posts.

On May 14, 5:13 am, [e-mail] wrote:

On May 14, 3:32 am, Mitch Sherman <[e-mail]:

[gary.l.simms]

[e-mail] wrote:

Tim Roberts wrote:

You are absolutely correct.

Not quite correct, because

Tim Roberts and Jerry were correct, I see that now. Sorry Tim for my
lame post.

Disregard my post please.

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

[ccj9100]

Sorry, Corey Jansen (cc*****@gmail.com) is a sock puppet of Mitch
Sherman (mi***********@hush.ai).

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.

Not correct. Mtruy post was wrong. Jerry's method is fine if you use
DISTINCT which he said. And of course this is a pathological example.
Peter's query needs to be modified if you want to use it without
creating an index first.

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