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]
