CODEWITHSUNDEEP
mysqlsqldatabase
unleashed
unleashed

Reputation: 1

Easier way of making this SQL query nicer

I've got this MySQL query here..

/***
 * An ugly self joining sql.
 * We want the parent's name so we have to join with the same table
 *    joining on child_row.parent_id = parent_row.id
 * Don't remove the LIMIT from this... it might be bad.
 */
$sql = 'SELECT cNode.*, pNode.name as parent_name ';
$sql.= ' FROM t_nodes AS cNode LEFT JOIN t_nodes AS pNode ';
$sql.= ' ON cNode.parent_id = pNode.id ';
$sql.='  WHERE cNode.id = '.$id.' LIMIT 1';

Is there any way of making this more elegant?

Upvotes: 0

Views: 103

Answers (2)

MatBailie
MatBailie

Reputation: 86735

The only reasonable alternative that I can think of is a correlated sub-query to get the parent name...

SELECT
  cNode.*,
  (SELECT name FROM t_nodes AS parent WHERE id = t_nodes.parent_id) AS parent_name
FROM
  t_nodes
WHERE
  cNode.id = $id
LIMIT 1

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

Nothing to do with SQL but just to improve legibility in the PHP source:

$sql = "
    SELECT cNode.*, pNode.name as parent_name
    FROM t_nodes AS cNode LEFT JOIN t_nodes AS pNode
    ON cNode.parent_id = pNode.id
    WHERE cNode.id = $id LIMIT 1
";

Upvotes: 1

Related Questions