I have multiple tables and require to retrieve data from the tables. Though this is quite achieved, what problem i see is that two of my result sets are stored in a table as rows and i would like to retrieve them as columns.
The query that i am executing is... --------------- SELECT u.user_id, ul.location_desc, YEAR(CURDATE())-YEAR(ud.date_of_birth) as age, if(udo.option_type='employed',udo.option_value,'') as emplyed, if(udo.option_type='married',udo.option_value,'') as married FROM user AS u LEFT JOIN user_location ul ON u.user_id=ul.user_id LEFT JOIN user_detail ud ON u.user_id=ud.user_id LEFT JOIN user_detail_option_map udom ON udom.user_detail_id=ud.user_detail_id LEFT JOIN user_detail_option udo ON udo.option_id=udom.option_id WHERE option_type IN('employed','married') ORDER BY modify_date LIMIT 10 ---------------- The issue here is that i get the rows correctly with valid data but since i get the 'employed' and 'married' option types as rows i get duplicate rows for each user which i need to avoid. This is the current result set with the above query. ------------------------------------------ uid | location | age | employed | married ------------------------------------------ 111 | INDIA | 44 | yes | ------------------------------------------ 111 | INDIA | 44 | | NO ------------------------------------------ 112 | INDIA | 24 | No | ------------------------------------------ 112 | INDIA | 24 | | Yes I would like to retrieve the rows with consolidate results like. ------------------------------------------ uid | location | age | employed | married ------------------------------------------ 111 | INDIA | 44 | yes | NO ------------------------------------------ 112 | INDIA | 24 | No | Yes Any reference in this regard would really help.. Paresh Parihar __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]