distinct on more than one field

[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

[Dan_Bracuk]

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.

[ACS LLC]

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 :(

[Dan_Bracuk]

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.

[ACS LLC]

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

[Dan_Bracuk]

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.

[ACS LLC]

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

[paross1]

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

[ACS LLC]

ahh. got it :)

Thanks

[paross1]

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

[ACS LLC]

Phil

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

Mark

[Dan_Bracuk]

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

[ACS LLC]

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?

[Dan_Bracuk]

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.