join a 2 tables with 2 columns

[spiderwell]

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?

[Barand]

Join twice but with different aliases

 

FROM contact

INNER JOIN user as sender ON contact_user_id = sender.user_id

INNER JOIN user as recip ON contact_contact_user_id = recip.user_id

[Jessica]

And pick some column names that make sense

[spiderwell]

poster one: many thanks

poster two: yeah yeah i know

[spiderwell]

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?

[Barand]

There is - don't use SELECT *