Skip navigation

Database Access

Currently Being Moderated

distinct on more than one field

Feb 14, 2008 2:51 PM

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

665 Views 37 Replies Latest reply: ACS LLC, Feb 18, 2008 10:24 AM

Replies

Currently Being Moderated

1. CF_Oracle,

Feb 14, 2008 3:07 PM in reply to ACS LLC

Report

Use2 UNION queries and a loop.
Both unions will give you distinct results.

query_1
Select field1 as x
from mytable
UNION
Select field2
from mytable

query_2
Select field1
from mytable
where field2 = #query_1.x#
UNION
Select field2
from mytable
where field1 = #query_1.x#

|
Mark as:
Currently Being Moderated

2. ACS LLC,

Feb 14, 2008 3:10 PM in reply to CF_Oracle

Report

so this would be to totally unique queries? Or can this be built into one?

|
Mark as:
Currently Being Moderated

3. -==cfSearching==-,

Feb 14, 2008 5:35 PM in reply to ACS LLC

Report

Try a case statement

SELECT DISTINCT CASE WHEN field1 = 1 THEN field2 ELSE field1 END AS FieldValue
FROM TheTable
WHERE Field1 = 1 OR Field2 = 1

|
Mark as:
Currently Being Moderated

4. Dan Bracuk,

Feb 14, 2008 6:48 PM in reply to ACS LLC

Report

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.

|
Mark as:
Currently Being Moderated

5. ACS LLC,

Feb 14, 2008 8:46 PM in reply to Dan Bracuk

Report

I'm using SQL2005

|
Mark as:
Currently Being Moderated

6. -==cfSearching==-,

Feb 14, 2008 9:44 PM in reply to ACS LLC

Report

Did you try using CASE? Is order important?

|
Mark as:
Currently Being Moderated

7. paross1,

Feb 15, 2008 7:27 AM in reply to -==cfSearching==-

Report

How about something like this?

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

Phil

|
Mark as:
Currently Being Moderated

8. CF_Oracle,

Feb 15, 2008 9:15 AM in reply to paross1

Report

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.

|
Mark as:
Currently Being Moderated

9. ACS LLC,

Feb 15, 2008 9:35 AM in reply to CF_Oracle

Report

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

|
Mark as:
Currently Being Moderated

10. paross1,

Feb 15, 2008 10:01 AM in reply to ACS LLC

Report

quote:

.....where I store a list of comma delim' numbers in one field
Oooooh, VERY bad idea..... very BAD design. By denormalizing your data, you are asking for many more problems than you think that you are solving.

Phil

|
Mark as:
Currently Being Moderated

11. ACS LLC,

Feb 15, 2008 10:06 AM in reply to paross1

Report

Really?

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! ;-)

|
Mark as:
Currently Being Moderated

12. paross1,

Feb 15, 2008 10:30 AM in reply to ACS LLC

Report

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

Phil

|
Mark as:
Currently Being Moderated

13. ACS LLC,

Feb 15, 2008 10:33 AM in reply to paross1

Report

understood.

Unfortunately I can't post all the ins and outs of the project into a public forum right now.

I think I'll go with my model and see how it pans out.. I can always change it at a later date :)

|
Mark as:
Currently Being Moderated

14. paross1,

Feb 15, 2008 10:35 AM in reply to ACS LLC

Report

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

Phil

|
Mark as:
Currently Being Moderated

15. ACS LLC,

Feb 15, 2008 10:38 AM in reply to ACS LLC

Report

correct, many to many.. user 1 might have 400 friends. both friends are friends of each other 1 is to 2.. as 2 is to 1

|
Mark as:
Currently Being Moderated

16. paross1,

Feb 15, 2008 10:51 AM in reply to ACS LLC

Report

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'

...or something similar

Phil

|
Mark as:
Currently Being Moderated

17. ACS LLC,

Feb 15, 2008 10:58 AM in reply to paross1

Report

Thanks for the in-depth reply.

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

|
Mark as:
Currently Being Moderated

18. Dan Bracuk,

Feb 15, 2008 11:58 AM in reply to ACS LLC

Report

quote:

Originally posted by: ACS LLC
Really?

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.

|
Mark as:
Currently Being Moderated

19. ACS LLC,

Feb 15, 2008 12:07 PM in reply to Dan Bracuk

Report

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

|
Mark as:
Currently Being Moderated

20. paross1,

Feb 15, 2008 12:17 PM in reply to ACS LLC

Report

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.

Phil

|
Mark as:
Currently Being Moderated

21. ACS LLC,

Feb 15, 2008 12:20 PM in reply to ACS LLC

Report

what I need to do is set it up and fill it with millions of records and run some stress tests.. something like 50M records

|
Mark as:
Currently Being Moderated

22. Dan Bracuk,

Feb 15, 2008 2:32 PM in reply to ACS LLC

Report

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.

|
Mark as:
Currently Being Moderated

23. -==cfSearching==-,

Feb 15, 2008 2:38 PM in reply to Dan Bracuk

Report

quote:

Originally posted by: Dan Bracuk
Put in a few thousand records and see how fast that one is.

Yes, that has to be one of the worst performing options. Forget about any utilization of indexes. Not to mention increased data integrity problems.

|
Mark as:
Currently Being Moderated

24. ACS LLC,

Feb 16, 2008 12:28 PM in reply to Dan Bracuk

Report

Dan,

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? :)

Thanks

Mark

|
Mark as:
Currently Being Moderated

25. Dan Bracuk,

Feb 16, 2008 1:33 PM in reply to ACS LLC

Report

For freinds? The simplest way is:

table person
person_id
name
more stuff

table freind (this is the many to many one)
person_id
freind_id.

To get all the freinds of id=1, do a union query. The top half gets person 1's freinds and the bottom half gets people who have person 1 has a freind.

select p.person_id id, name
from person p join freind f on p.person_id = f.freind_id
where p.person_id = 1
union
select p.person_id id, name
from person p join freind f on p.person_id = f.person_id
where freind_id = 1

For the freind table, your primary key is both fields. Also, each field is a foreign key to person.

|
Mark as:
Currently Being Moderated

26. ACS LLC,

Feb 16, 2008 1:45 PM in reply to Dan Bracuk

Report

This is a touch more advanced that what I'm used to, but once I get to grips with it I'll be fine.

This is what I ACTUALLY have in the dbase

The members are stored in a table called USERS

USERS TABLE -->
USER_UID --> Primary key and unique UID for the user.
other stuff, USER_FIRSTNAME, USER_LASTNAME etc

Then I have the FRIENDS TABLE
FRIEND_UID --> Unique Primary key to identify each record
USER_UID --> The ID of the USER that we are referring to (same value as USER_UID in table USERS
FRIEND_USER_UID --> The ID of the friend of the user we are referrring to, also their USER_UID in table USERS

As suggested I can set up a relationship between USERS and FRIENDS so that both the USER_UID and FRIEND_USER_UID in the FRIENDS table have foreign keys linked to the same field USER_UID in the table USERS

I think all of the above is correct as far as the dbase set up is concerned.. Oh and I also indexed all of the 3 fields in FIELDS, not sure if that is such a good idea?

When I tried all of this with the relationship in place it would not let me manually insert a legit record for a user, say user_UID 13, and I wanted to have friend user_UID=52, it gave an error.

This set up means that I guess if I have user 13 and user 52 as friends that I'll have two records right?

FRIENDS TABLE
user_uid,friend_user_uid
13,52
52,13

Now it's the SQL query I come unstuck on, I could use basic SQL to just

SELECT friend_user_UID
FROM FRIENDS
WHERE user_uid = 13

to get all friends of 13, but with the relationship in place I am presuming this is bad and not efficient, and I need to put that inner join in place for the USER table so that performs better. This is the bit where I'm a tad stuck :(

|
Mark as:
Currently Being Moderated

27. Dan Bracuk,

Feb 16, 2008 1:55 PM in reply to ACS LLC

Report

The difference between my freinds table and yours is the primary key. You have a single field, independant of the two others, I have the two fields. My method prevents double entries, yours allows it.

As far as whether 13 and 52 are mutual freinds if one befreinds the other, that is not a coding question. That's a business rule.

|
Mark as:
Currently Being Moderated

28. ACS LLC,

Feb 16, 2008 2:00 PM in reply to Dan Bracuk

Report

So you're suggesting that I could remove that primary key from the FRIENDS table? That might cause issues when manipulating fields, although the records are always unique in there own way, WHERE user_uid = x and friends_user_uid = y could remove a single record, but if I wanted to remove a group of records from a query without a primary key I'm wondering if it would start to become complex?

Yes, both will become friends, so this would create two records, unless you think I should join the two fields together so that one record one work? that would get a little more complex but half the number of records

|
Mark as:
Currently Being Moderated

29. Dan Bracuk,

Feb 16, 2008 2:25 PM in reply to ACS LLC

Report

If your rules are that people become mutual freinds when one befreinds the other, then you want two records.

About primary keys, what I wrote is different than what you seem to be referring to in your most recent post.

|
Mark as:
Currently Being Moderated

30. ACS LLC,

Feb 16, 2008 2:34 PM in reply to Dan Bracuk

Report

ok, two records it is then :)

I actually removed the primary key from FRIENDS and now it's letting me add records, although I can add duplicates, am I missing something in the dbase setup? (misunderstanding) ?

Thanks for all the help!!!! I'm hoping to start coding it up tomorrow so need to get my brain around this

|
Mark as:
Currently Being Moderated

31. Dan Bracuk,

Feb 16, 2008 3:06 PM in reply to ACS LLC

Report

If you are able to insert duplicate records then you deviated from my suggestion. But it's your app, not mine.

|
Mark as:
Currently Being Moderated

32. ACS LLC,

Feb 16, 2008 3:10 PM in reply to Dan Bracuk

Report

I don't want duplicates, I thought I had the same dbase set up as what you suggested, any idea what I might have wrong looking at the description of how I set it up from the earlier post?

|
Mark as:
Currently Being Moderated

33. Dan Bracuk,

Feb 16, 2008 3:46 PM in reply to ACS LLC

Report

Take at least a one hour break. Then read everything I wrote on this thread today. It's there.

By the way, your plan to insert pairs of records for each freindship has a downside. You won't be able to tell anyone all the freinds they have selected or all the people who have chosen him. As long as you never ever have requirements like that, you'll be fine.

|
Mark as:
Currently Being Moderated

34. paross1,

Feb 18, 2008 7:17 AM in reply to Dan Bracuk

Report

Both user_uid and friend_user_uid are foreign keys. You could make user_uid and friend_user_uid together as a composite primary key, or enable a UNIQUE database constraint on BOTH fields together, so that both fields combined have to be unique, but not either separately.

Phil

|
Mark as:
Currently Being Moderated

35. ACS LLC,

Feb 18, 2008 8:21 AM in reply to paross1

Report

ahh. got it :)

Thanks

|
Mark as:
Currently Being Moderated

36. paross1,

Feb 18, 2008 8:50 AM in reply to ACS LLC

Report

Of course, creating a UNIQUE database constraint will prevent you from adding duplicate entries to the database, and will raise a database exception if you try, so your "application" (CF) code should also be preventing this, or at least, handle the database exception. The constraint "protects" the integrity of the database, but your ColdFusion code should also "protect" the application from throwing unhandled exceptions.

Phil

|
Mark as:
Currently Being Moderated

37. ACS LLC,

Feb 18, 2008 10:24 AM in reply to paross1

Report

Phil

Exactly :) .. The CF will definately take all necessary steps to make sure that duplication is not an issue

Mark

|
Mark as:

Forum guidelines

Answers + Points = Status

10 points awarded for Correct Answers
5 points awarded for Helpful Answers
10,000+ points
1,001-10,000 points
501-1,000 points
5-500 points

Database Access

distinct on more than one field

Feb 14, 2008 2:51 PM

More Like This

Bookmarked By (0)

Answers + Points = Status