MySQL

How to use wildcard in a JOIN ON using a string

Found others looking for the same, how to use wildcard in a JOIN ON using a string reference:

This is how I soleved it with wildcard:

SELECT acl.* , node.type FROM acl
LEFT JOIN node ON ( acl.name LIKE concat( '%', node.nid ) )
WHERE 1

This is how I soleved it with fixed value:

SELECT acl.* , node.type FROM acl
LEFT JOIN node ON ( acl.name LIKE concat( 'view_', node.nid ) )
WHERE 1

This is how I soleved it with pattern like [something]_[nid]:

SELECT acl .

Combine MySQL Update and LEFT JOIN to make it dynamic

Here is how you can combine a MySQL UPDATE with LEFT JOIN to make the query more dynamic.

<?php
UPDATE `x_client_import` LEFT JOIN term_node
USING ( nid )
LEFT JOIN node ON ( node.title LIKE `x_client_import`.`client`
AND `node`.`status` =1 ) SET `x_client_import`.`nid`=`node`.`nid` WHERE `term_node`.`tid`
IN ( 329 );
?>

or

<?php
UPDATE `content_type_client` LEFT JOIN `x_client_import` USING (nid) SET `content_type_client`.`field_c_ongoing_value` = `x_client_import`.`ongoing_value` WHERE `content_type_client`.`field_c_ongoing_value` IS NULL;
?>

MySQL group concat a join select

This is an example of how you can aggregate data using a JOIN in a SELECT statement.

<?php
SELECT node.*, GROUP_CONCAT(example.nid) AS myfield FROM node LEFT JOIN example USING(nid) GROUP BY node.nid;

Result:
..."123, 65, 14, 65"...
?>

Make the list semicolon separated and order the values descending and make sure that the values are distinct (no duplicates)

Pages