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

[Mitch Sherman]

On Tue, 20 May 2008 09:20:39 -0500, 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."

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

I think I misrepresented what Jerry Stuckle posted here. Disregard my
post.

Mitch

[Mitch Sherman]

Disregard all my posts from prior days in this thread please. It looks
like I misunderstood what Jerry, Guillaume, Tim Roberts, Captain
Paralytic, and others were saying and made a fool of myself!

I apologize for this to all.

Mitch

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

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 09:20:39 -0500, 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."

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

Jerry's a brilliant guy. Disregard this false and rude post please.

Mitch

[Mitch Sherman]

On Thu, 22 May 2008 01:52:44 -0500, Mitch Sherman
<mi***********@ hush.aiwrote:

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

This quote I pasted is irrelevant... That's about subqueries. You have
to look elsewhere to find the other one...

Mitch

[Mitch Sherman]

Disregard the foolish the posts I made before Jan 2 in this thread.
Looks like I didn't understand the replies made by others!

Mitch

On Mon, 02 Jun 2008 13:28:46 -0500, Mitch Sherman
<mi***********@ hush.aiwrote:

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

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

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

Sorry Jerry, I see that you're correct in this thred. I didn't mean to
suggest otherwise. The correct answer should work with duplicates or
without. Peter's answer is designed to work in a limited scenario.
Your answer (with DISTINCT as you said) is a better response.

Thanks for your help in understanding this SQL issue. I can see you
have a lot of experience in this area which helps to understand how to
properly model these problems.

Thanks again for your expert SQL recommendations in this thread and
many others.

Mitch

[Mitch Sherman]

On Thu, 15 May 2008 21:49:03 -0500, Mitch Sherman
<mi***********@ hush.aiwrote:

>
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

No I was wrong, Jerry's test shows another reasonable scenario which
the poster could be using and is a valid test of the original problem.
It's a counterexample to petersprc's original answer. petersprc's
answer needs to be modified as shown elsewhere in this thread to work
with Jerry's scenario.

Mitch

[Mike Lahey]

Mike Lahey wrote:

He said entries belong to categories, which is a fairly obvious
relationship. This is a binary relation. Thus there must exist a 2
column relational table in this design, or it's not normalized. Yes,
other tables may exist, but it's not relevant.

Yes well I was wrong in the post I'm replying to as well. Jerry's
scenario is totally consistent with the poster's scenario.

[Mike Lahey]

Mike Lahey wrote:

It's a red herring because

This post was very rude. Please disregard it.

[Mike Lahey]

Mike Lahey wrote:

This was a rude post and I was wrong. Please disregard it.

[aguillacutty]

On May 12, 10:55 am, aguillacu...@gm ail.com wrote:

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

On 12 May, 02:36, Mike Lahey <mikey6...@yaho o.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.

Well my post was not quite accurate. The answer wasn't correct it
imposed an unnecessary restriction - that the 2 columns couldn't have
duplicates. However, the answer is still useful if your data fits that
model and you want to intersect arbitrary #'s of groups....