Select fields in multiple tables / change output value / compare fields

Question

i want compare (=) two fields in two tables. In the first field, i have only a nid (e.g. 55) and in the second field, the value is formatted with "node/%" (e.g. node/55).

How can I created the join query for compare these two fields ?

I have tested this with this code without success :

$uid = arg(1);
$query = db_select('field_data_field_users', 'alias1');
$query
    ->condition('user_id', $uid, '=')
    //->addExpression('node/(fdfur.entity_id)', 'alias2'); // > not functionnal
$query->join('menu_links', 'ml', 'ml.link_path = alias1.entity_id');
// ml.link_path = alias1.entity_id > node/55 = 55 > no match...
$query
    ->fields('alias1', array('entity_id'))
    ->fields('ml' , array('language'))
    ->fields('ml' , array('link_path'))
$results = $query->execute();

Here an image that explains my problem

I start from the user id and I want to get the datas of the menu links. The first db_select in the custom table is ok, i get the related nids with the user ids, but I can't to get the datas of the menu link.

Have you any idea to resolve my problem ?

Thanks for your help.

Answer 1

I suspect this is what you are looking for

db_query("SELECT * FROM node LEFT JOIN url_alias ON url_alias.alias=concat('node/',node.nid)");