Jump to content

join a 2 tables with 2 columns


spiderwell
 Share

Recommended Posts

hi all, i have 2 tables, one is a users table, it has the usual expected fields, but for now all you need to know is user_id is the auto increment PK for that table.

My second table is called 'contacts' and has this structure:

CREATE TABLE IF NOT EXISTS `contact` (
 `contact_user_id` int(11) NOT NULL,
 `contact_contact_user_id` int(11) NOT NULL,
 `contact_status` enum('Request','Confirm','Deny','Follow','Block') CHARACTER SET utf8 NOT NULL,
 `contact_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

the contact_user_id is the sender of the contact request, and the contact_contact_user_id the receipent ,

the idea being its a list of contacts for a user in the database. now to get a list of contacts for a given user I used this sql for userid = 1:

SELECT * FROM (`contact`) WHERE `contact_contact_user_id` = '1' OR `contact_user_id` = '1' AND `contact_status` != 'Block'

 

I want to left join users table on to these results, but the column which that user is in can be either of the 2 columns mentioned above as here is a result set

										1							47							Request							2013-02-08 17:33:20											1							45							Confirm							2013-02-08 17:33:31											48							1							Confirm							2013-02-08 17:33:40											49							1							Request							2013-02-08 17:34:34											1							46							Request							2013-02-08 17:41:17			

 

how would i join my user table to one of 2 columns in the contacts table, and ensure im not joining my user details instead of the contacts?

Link to comment
Share on other sites

ok so having tried Barand's solution i end up with a set of results that is twice as many columns as i need, it gives me both sender and receipient user details, which basically means each row has sender details and the recipient details, only sometimes my details are on sender and other time on receipient. Is there anyway to acheive this without it putting both user details into every row ? other wise i am repeating userid = 1 details on every row of the result.

 

Im sure there is a more effcient way to do this?

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.