quote:
Originally posted by:
ACS LLC I have a table that has two fields in there, let's say field1
and field2.
I need to somehow join these together to get a distinct list
of records from each, as if they were one, so for example
field1,field2
1,10
1,12
4,1
6,1
If I query WHERE field1=1 or field2=1
I need to get one one result 10,12,4,6 which gives me all the
records relating to record 1 regardless of the order
Can anybody help me with this?
Thanks
Mark
What is your db? If you are using one that allows you to
select by row number, you are in luck. Or, if you know the 1st row
has the lowest value of field 1, that also makes it pretty simple.
SELECT DISTINCT either_field
FROM
(SELECT field1 AS either_field
FROM yourtable
WHERE field1=1 OR field2=1
UNION
SELECT field2 AS either_field
FROM yourtable
WHERE field1=1 OR field2=1) AS tbl
ORDER BY either_field
I think field1=1 or field2=1 was just an example. He needs
ALL unique values of both fields with any value of the other field
not just 1. So it could be mine or Paross solution with addition of
dinamic value passed as variable or result of other query.
I've decided that this is going to cause to many
complications, therefore I'm using a different approach altogether
where I store a list of comma delim' numbers in one field... less
records, faster access time.. I'll use CF to slice it up as I need
to and write it back to the dbase
I only ever have to search by the unique UID of the user, so
if I need the record for user 123 I just search on the user record
for user_uid=123 and I retrieve the list, I will never have to
search through the list in SQL, just get the data
My concern on top of the complex code is that if I have let's
say 200,000 members, and each one has an average of say 25
'friends', that's 5,000,000 records... and I actually anticipate a
LOT more with potentially higher averages per person... I'm
relucant to have a query gather a record set from a list that could
span 10-20M or more records for multiple users at the same time...
could get ugly.
I could always 'undo' the work I've done as we grow, but I
can't see any problems with the way I'm doing it...yet! 😉
It is hard to comment on what you are doing because you gave
an incomplete picture of what you were trying to do, and only asked
for a narrow example. Judging from your last statement, I have a
hunch that you may have a data model problem that may be helped by
proper normalization, but it is hard to say without more specific
information on your current design, and description of what you are
really trying to accomplish (not how you think that you may
accomplish it).
Are you talking about a many-to-many relationship between
members and friends? Are both (members and friends) listed in the
"members" table? Are you trying to do something like being able to
list all of the friends of a particular member, etc.?
I only ever have to search by the unique UID of the user, so
if I need the record for user 123 I just search on the user record
for user_uid=123 and I retrieve the list, I will never have to
search through the list in SQL, just get the data
My concern on top of the complex code is that if I have let's
say 200,000 members, and each one has an average of say 25
'friends', that's 5,000,000 records... and I actually anticipate a
LOT more with potentially higher averages per person... I'm
relucant to have a query gather a record set from a list that could
span 10-20M or more records for multiple users at the same time...
could get ugly.
I could always 'undo' the work I've done as we grow, but I
can't see any problems with the way I'm doing it...yet! ;-)
Paross was right. Everything you mention describes a simple
many to many relationship. For all I know, that's what you had when
you wrote the original post.
Lots of records are no big deal. If you have that much data
it will easier and quicker to get than it from a normalized schema
than from varchar fields containing list.
In fact, paross's answer timestamped 02/15/2008 03:27:06 PM
is the best one on this thread.
I pretty much understand his solution, it is more or less the
same as my first approach. But the huge amount of records that I
could create is still of concern to me.
The largest set of records I've ever handled was around 26M,
at this point even with indexing in the right places it started to
slow down. This site could have similar amounts but more hits.
What sort of problems do you forsee if I do it the other way
with one record and a comma delim' list? note that I won't search
that list only the uid of the person (member) that I need to refer
to
Since your "link" table is essentially the only one that is
really going to have the large number of rows, and since it would
be comprised of two ID fields that could/should be easily indexed,
you really shouldn't experience a significant performance hit
unless you are doing something much more complex in your queries. I
can not see any other method that would not be worse in regards to
performance, and certainly not better in regards to simplified
code.
quote:
Originally posted by:
ACS LLC I pretty much understand his solution, it is more or less the
same as my first approach. But the huge amount of records that I
could create is still of concern to me.
The largest set of records I've ever handled was around 26M,
at this point even with indexing in the right places it started to
slow down. This site could have similar amounts but more hits.
What sort of problems do you forsee if I do it the other way
with one record and a comma delim' list? note that I won't search
that list only the uid of the person (member) that I need to refer
to
Your sql will be
where field1 = 1
or field2 like '1,%'
or field2 like '%,1,%'
or field2 like '%,1'
Put in a few thousand records and see how fast that one is.
And bear in mind, this is only step one. You still have to loop
through all the lists to get the ids, and then run another query to
get the names.
I'm going to go with the suggestion that paross1 made, it
seems that the only vote I got for my method was by myself ;-)
I see you are online right now and I'm trying to get some
sort of structure in over the weekend but I'm having a slight
amount of trouble getting to terms with the exact SQL, would you
have time to give me a little assistance with this? :)
Very simple fix. You need what is called an "associative
entity" which is a fancy name for a link table between "members"
and "friends" in order to resolve the many-to-many relationship.
Because "friends" are also "members", then the link to the member
table will be essentially self referential.
Lets say you have a member table, and you wish to link to
other members as friends, then you could create a table named
friends with member_id and friend_id feilds. Both the member_id and
friend_id fields are foreign keys back to the member table
member_id field, but each row in the friends table would contain
the member_id of the member, and the friend_id (member_id) of the
friend.
Lets say that you have Joe, and his member_id is 1, and his
friends are Fred (member_id = 2) and Mary (member_id 3). You would
have two rows in the friends table, each would have a member_id
value of 1, but different friend_id values (2 and 3 respectively).
In order to find out who Joe's friends were, you could write a
query like this:
SELECT m1.name AS member,
m2.name AS friend
FROM member m1
INNER JOIN friend f ON m1.member_id = f.member_id
INNER JOIN member m2 ON f.friend_id = m2.member_id
WHERE m1.name = 'Joe'
This I believe is how I was going to do it in the first
place, the problem was that I would end up with millions upon
millions of records, which could cause performance issues
37
Replies
AdChoices