Need help writing a fairly basic query

[bmarshall0511]

I've got a pretty basic table that look like this:

 

[friend_id] [user_id] [friend_user_id] [status]

 

What I need to do is grab the friend_user_id for all the records that match the specified user_id AND friend_user_id matches the specified user_id. Basically users can become a 'Fan' of someone, which makes them a 'Fan'. If the other user also becomes a 'Fan' of that user, they become 'Friends'. That's what I'm trying to grab... the 'Friends'. They also have to be set as 'active' in the status column.

 

Here's some sample data:

 

friend_id

user_id

friend_user_id

status

1

14

4

active

2

4

14

active

3

4

16

active

4

14

19

active

 

So with this table, it should only pull one result and grab the friend_user_id 4 based off the specified user 4.

 

Any help would be greatly appreciated.

 

[techdude]

I am assuming that this is a look up table, and you store the ids elsewhere. If that is the case, why not use a join? Post back here for more help with that.

 

Aside from that, to build the query, start with what you want to select.

 

SELECT `friend_user_id`

 

Next, chose the location.

 

FROM `database_name`.`table_name`

 

Where database_name is the name of the database, and table_name is the name of the table.

 

Now you add the conditional statement.

 

WHERE `user_id` = `friend_user_id`

 

That is if you want to select rows where the user id and the friend user id are equal. I don't think that is what you want to do. It would help if you clarified a bit. I see it a one of the following:

 

1. get the friend user id where the user id = x

2. get the friend user id where the friend user id = x

or 3. get the friend user id where the user id = x and the friend user id = x.

 

Post back for help with this. Also, I would recommend a change to your table design. Post back here if you are interested.

 

 

[bmarshall0511]

Thanks for the reply. I'm fairly proficient in SQL queries, I just need help coming up with a query to grab the user's friends, which are people that have both added each other to their 'Fan' list which uses the table I mentioned. I need to only grab records for a user where they have added a user to their Fan list and that user has added the specified user to their Fan list. I don't want to grab all records for the specified user, just the ones that the friend_user_id has also added their friend_user_id to the specified user. Using the table I showed, I only want to grab the green records.