Select two row values in a single row with parent

Hello All,

After a couple of months, I am back with a MySQL related question, hope gurus can help me on this.

I am using some third party extension in Joomla (a popular PHP CMS) which allows me to add some extra fields if the existing fields are not enough for me and I added some custom fields from their interface and adding records are fine so far. They managed database as below internally:

-------------------------------------------------
tbl_main
id    name        address
1    Raju Gautam    Kathmandu, Nepal
2    John Duo    NY, USA
-------------------------------------------------

tbl_fields
id    fieldname
1    phone
2    gender
-------------------------------------------------

tbl_fieldvalues
id    main_id        field_id    f_value
1    1        1        222
2    1        2        M
3    2        1        3333333
4    2        2        M

And I want the output like this:


id    name        address            phone    gender
1    Raju Gautam    Kathmandu, Nepal    222    M
2    John Duo    NY, USA            333    M

Edit:
For now I have managed it by using the sub query which is kind of fixed i.e. using gender and phone fields in the query and it will not work if another field is added later on.


SELECT	m.*,	(SELECT fv.f_value	FROM tbl_fields AS f INNER JOIN tbl_fieldvalues AS fv ON fv.field_id=f.id	WHERE fv.main_id=m.id AND f.fieldname='gender') AS gender,	(SELECT fv.f_value	FROM tbl_fields AS f INNER JOIN tbl_fieldvalues AS fv ON fv.field_id=f.id	WHERE fv.main_id=m.id AND f.fieldname='phone') AS phoneFROM tbl_main AS m;

Hope the question is clear enough.

Thanking you in advance.

With warm regards
Raju Gautam

the question is absolutely clear

so’s the answer :slight_smile:

if you want multiple columns from multiple rows like that, you have to do just what you’re doing, a subquery for each one

clumsy? you betcha!!

what’s wrong with pulling the contents of tbl_fieldvalues into your application code, and “re-assembling” it over there?

is it because that might be even more programming work? :wink:

Thank you so much for the quick response Rudy !

I cannot bet on this because I have already spent enough time on this as per my knowledge and could not improve the query. That’s why I wanted to make sure if a single query directly can do that. Would be nice if anyone can give some work around but for now your confirmation is enough for me :slight_smile:

Indeed this will be the easiest and reliable way to pull the contents of tbl_fieldvalues in programming and will not be that much work but I (or lets say teh client) want a SQL to retrieve the output directly into the Excel (excel provides this feature) from MySQL. That’s why I am trying to construct a query.

Even I can write some script (in PHP) that generates the Excel but I want it directly in Excel :wink:

Thank you very much for quick response once again Rudy. You have been a real GURU already for the DB related questions for me.

Off Topic:

BTW, I have again voted for you :slight_smile: I don’t think that I should wish you ‘good luck’.