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 4692
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.
Captain Paralytic wrote:
On 12 May, 02:36, Mike Lahey <mikey6...@yaho o.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.
>
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.
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*******@attgl obal.net
=============== ===
On Tue, 13 May 2008 12:02:59 -0400, Jerry Stuckle
<js*******@attg lobal.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
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
Mitch Sherman wrote:
On Tue, 13 May 2008 12:02:59 -0400, Jerry Stuckle
<js*******@attg lobal.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*******@attgl obal.net
=============== ===
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.
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*******@attgl obal.net
=============== ===
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.
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. 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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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
| |