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...
Jun 2 '08
118  4697 
On Thu, 15 May 2008 12:01:02 -0400, Jerry Stuckle
<js*******@attg lobal.netwrote:
>vk*****@gmail. com wrote:
>On May 14, 7:21 pm, Mike Lahey <mikey6...@yaho o.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
Mitch Sherman wrote:
On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle
<js*******@attg lobal.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('D ROP TABLE IF EXISTS test');
mysql_query('C REATE 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*******@attgl obal.net
=============== ===
On May 15, 9:49 pm, Mitch Sherman <mitch.sher...@ hush.aiwrote:
On Thu, 15 May 2008 11:50:57 -0400, Jerry Stuckle
<jstuck...@attg lobal.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]
On May 15, 9:52 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:
Mitch Sherman wrote:
On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
<jstuck...@attg lobal.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]
On May 15, 10:06 pm, Jerry Stuckle <jstuck...@attg lobal.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]
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 ox*****@yahoo.c om a écrit :
Here is the actual table structure:
>Here is an example of my data:
Nice assert.
There is no "userid", "groupid", or "permission ". This is a linking
table that relates entry_id to cat_id.
You wish. Still, you assume. And you're wrong. I mean you're wrong to
assume, cause you may be right about the table structure. Note the word,
"may".
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.
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
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*******@attgl obal.net
=============== === This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Chuck100 |
last post by:
I'm having problems with the output of the following script (I've
simplified it):-
select a.section,a.user,count(b.number),null
from table a, table b
where.........
group by a.section,a.user
union
select a.section,a.user,null,count(c.number)
from table a, table c
|
by: Franklin P Patchey |
last post by:
I have modified some script and think i have put a bit in that isn't
"compliant"
Is the bit marked below (towards the end) correct - should it be () and not
("")
<SCRIPT LANGUAGE="JavaScript">
<!-- hiding
page=new Date();
if (page.getDate() == 1)
document.write("<embed src='media/audio/waltzinblack.mp3' width='145'
|
by: ASallade |
last post by:
Hello,
I've scoured my books and the web, but am still daunted, hopefully some of
the users in this newsgroup will have advice for my problem.
I am not an experienced javascript programmer, but have gotten my code to
work well, without errors on IE, Opera and netscapes recent builds. While
testing, I found that it doesnt execute on Netscape 4.7
In the head I have a script that creates a custom object/class for products,
|
by: Funnyweb |
last post by:
When adding a field to a table using ALTER TABLE is it possible to check if
the field already exits before the ADD command is run? If so how do I do
this?
Thanks
Hamilton
|
by: JPL Verhey |
last post by:
(i hope somebody (else) will read and have an idea! Thnx)
Hi,
With a script in a popup window, I want to check if certain content is
present in a page loaded into the frame "main" of the frameset in the
opener window.
I started with something like this (what happens when the considions are
met already works):
| | |
by: Rahul |
last post by:
Hi Everybody
I have some problem in my script. please help me. This is script file.
I have one *.inq file. I want run this script in XML files. But this
script errors shows . If u want i am attach this script
files and inq files. I cant understand this error. Please suggest me.
You can talk with my yahoo id b_sahoo1@yahoo.com. Now i am online.
Plz....Plz..Plz...
|
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted
********************************************************
For this teeny job, please refer to:
http://feeds.reddit.com/feed/8fu/?o=25
|
by: adamurbas |
last post by:
ya so im pretty much a newb to this whole python thing... its pretty
cool but i just started today and im already having trouble. i
started to use a tutorial that i found somewhere and i followed the
instructions and couldnt get the correct results. heres the code
stuff...
temperature=input("what is the temperature of the spam?")
if temperature>50:
print "the salad is properly cooked."
else:
|
by: koutoo |
last post by:
I have a code that writes to 2 seperate files. I keep getting a "list
index out of range" error. The strange part is that when checking the
files that I'm writing too, the script has already iterated through
and finished writing, yet the error stated implies that it hasn't? So
how can it be, that my script has written to the files, yet the error
is stating that it hasn't made it through the script? I'll have 15
files that I have...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
