[SOLVED] query/join : is this possible?

[Zwiter]

hello

I have a table with names, another with features and a last one to link the 2 others (normalized tables).

 

I am trying to get the names with the features assiciated to each name. A name can have no or many features. A feature can have no or many name.

 

Here is an exemple :

test :
+---------+-----------+
| test_id | test_name |
+---------+-----------+
| 1       | toto      | 
| 2       | titi      | 
+---------+-----------+

test2feature :
+---------+------------+---------------+
| test_id | feature_id | feature_value |
+---------+------------+---------------+
| 1       | 1          | rouge         | 
| 2       | 1          | noir          | 
| 2       | 2          | oui           | 
+---------+------------+---------------+

testfeature :
+------------+--------------+
| feature_id | feature_name |
+------------+--------------+
| 1          | voiture      | 
| 2          | ballon       | 
+------------+--------------+

 

And here is what i am trying to get :

query result :
+-----------+--------------+---------------+--------------+---------------+
| test_name | feature_name | feature_value | feature_name | feature_value |
+-----------+--------------+---------------+--------------+---------------+
| toto      | voiture      | rouge         | voiture      | rouge         | 
| titi      | voiture      | noir          | voiture      | rouge         | 
+-----------+--------------+---------------+--------------+---------------+

even better ^^ :
+-----------+---------+--------+
| test_name | voiture | ballon |
+-----------+---------+--------+
| toto      | rouge   | NULL   | 
| titi      | noir    | oui    | 
+-----------+---------+--------+

 

The 2nd result is possible to obtain ? If yes, how is the query?

If not, what about the 1st result? And the query?

 

This problem is giving me headache

 

Thank you for your help.

Z.

[fenway]

This would be step one....

 

select 
t.test_name
,f.feature_name
,t2f.feature_value

from test2feature as t2f
inner join test as t on ( t.testid = t2f.testid )
inner join testfeature as t2 on ( tf.featureid = t2f.featureid )

 

But your query can't be psychic -- it needs to know that there are 2 (or N) feature names in advance... very difficult to do dynamically in a single query.

[Zwiter]

I am currently trying any case possible with this request :

SELECT test_name, 
(SELECT group_concat(feature_name SEPARATOR ', ') 
FROM test2feature as T2F
WHERE T.test_id=T2F.test_id) AS f_names, 
(SELECT group_concat(feature_value SEPARATOR ', ') 
FROM testfeature as TF
WHERE T.feature_id=TF.feature_id) AS f_values
FROM test as T
NATURAL JOIN test2feature
NATURAL JOIN testfeature
GROUP BY test_id

[fenway]

That's not really going to give you what you want... if you know the values in advance, you can hard-code them.

[Zwiter]

i cannot know. It is dynamic.

I have coded my request with my solution, and it works. Currently, i show values and name like  a liste with a ',' separator.

 

Next, if i need, i can use the php function split to get each individualy.

But currently, display is fine.

 

the only problem I see is the cost. I dont know how mysql handle this type of request. All I can tell is that i wont have much features. But I will have many primary data (10 000 to 100 000) (like table test).

 

I set this topic solved, but we can continue to discussed about better way to do it

 

Z.

[fenway]

Like I said, if you know which features there are (even if a previous query), then you can generate a pivot table / cross-tab to get your result very easily.

[Zwiter]

and like i said, features are not set from start.

Each add of data can add a new feature at same time

[fenway]

and like i said, features are not set from start.

Each add of data can add a new feature at same time

Like I said, determine them in an earlier query, and there's no difference.

[Zwiter]

and then, how do you proceed?

Is it much better than the current query?

 

[fenway]

Take a look at this resource.

[Zwiter]

i thank you for this link.

I dont think it can help me for this specific problem, but it gives great ideas i have never thought it was possible.

Z.

[fenway]

I dont think it can help me for this specific problem

It most definitely can.

[Zwiter]

done, and it work perfectly!!!!

I dont know if its faster (i curently only have a set of 1000 data, the query is done to fast), but it work nicely.

thank so much!

 

This raise another question, if i have 100 features, it will add 100 attributs in my main SELECT.

Is that ok?

 

Z.

[Zwiter]

i answe myself : i did the join stuff and the where stuff in the 1st query, ended by select unique rows.

 

But now, i have another question : group by or distinct : what is better?

Z.

[fenway]

Would you mind posting your solution for everyone else to see?

 

And always GROUP BY, never DISTINCT.

[Zwiter]

sure, i will :

 

The request is build in 2 times :

1) A request wich return a part of the next request. Grab all the feature

2) The real request, wich give me the result i need, build with the result of the 1st one.

 

1) I query featuretest for the feature values, and return the result formated for the next query :

SELECT concat( ', (SELECT feature_value FROM testfeature NATURAL JOIN test2feature WHERE feature_name=\'', feature_name, '\' AND test_id=T.test_id) as \'', feature_name, '\'' ) 
FROM testfeature;

Wich gives :
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| concat( ', (SELECT feature_value FROM testfeature NATURAL JOIN test2feature WHERE feature_name=\'', feature_name, '\' AND test_id=T.test_id) as \'', feature_name, '\'' ) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| , (SELECT feature_value FROM testfeature NATURAL JOIN test2feature WHERE feature_name='voiture' AND test_id=T.test_id) as 'voiture'                                       | 
| , (SELECT feature_value FROM testfeature NATURAL JOIN test2feature WHERE feature_name='ballon' AND test_id=T.test_id) as 'ballon'                                         | 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 

So you notice the 2 rows i obtain looks like a part of a query.

I use php, so i collect this 2 rows in a string

<?php
$list_features = '';
while ($row = mysql_fetch_row($query)
    $list_features .= $row[0];
?>

 

2) I build my second query :

<?php
$sql = "SELECT test_name $list_feature
FROM test as T";
?>

So in mysql:
SELECT test_name
, (SELECT feature_value FROM testfeature NATURAL JOIN test2feature WHERE feature_name='voiture' AND test_id=T.test_id) as 'voiture'
, (SELECT feature_value FROM testfeature NATURAL JOIN test2feature WHERE feature_name='ballon' AND test_id=T.test_id) as 'ballon'
FROM test as T;

And the result:
+-----------+---------+--------+
| test_name | voiture | ballon |
+-----------+---------+--------+
| toto      | rouge   | NULL   | 
| titi      | noir    | oui    | 
+-----------+---------+--------+

 

Here it is

 

About distinct and group by:

- 1st, I did not use them in my exemple because it dont need it. In my real case, i just have tu use a 'GROUP BY feature_nam' at the very end of my 1st query.

- Then, i try 100 times my queries and went with 20.8 sec for group by and 19.4 for distinct.

More search told me that mysql handle distinct the same way as group by. But this is not true for all SQL language. In some, distinct may order the elements before selecting a unique one for each. group by don't, and so is faster.

 

I hope you will something, in exchange of the help you provide me

Z.