如果我运行以下查询:
SELECT loc2.* FROM `locations` AS loc INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent WHERE loc.location_status='publish'
我会得到以下结果:
实际上,我正在尝试从数据库中获取所有子代/孙代。现在,上面的查询仅按父子规则排序并返回所有行。但是,我想向SQL添加条件,以便它仅获取特定父级的子代。
例如,我想获取
我尝试了这个:
SELECT loc2.* FROM `locations` AS loc INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent WHERE loc.location_status='publish' AND loc2.location_parent=19
和这个:
SELECT loc2.* FROM `locations` AS loc INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent WHERE loc.location_status='publish' AND loc.location_parent=19
但它们都返回相同的结果,正如它应该:
SELECT loc2.* FROM `locations` AS loc INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent WHERE loc.location_status='publish'
我会得到以下结果:
实际上,我正在尝试从数据库中获取所有子代/孙代。现在,上面的查询仅按父子规则排序并返回所有行。但是,我想向SQL添加条件,以便它仅获取特定父级的子代。
例如,我想获取
19
的所有子行/节点/位置。结果集应该如下所示:我尝试了这个:
SELECT loc2.* FROM `locations` AS loc INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent WHERE loc.location_status='publish' AND loc2.location_parent=19
和这个:
SELECT loc2.* FROM `locations` AS loc INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent WHERE loc.location_status='publish' AND loc.location_parent=19
但它们都返回相同的结果,正如它应该:
那么,我应该做些什么才能达成我所需的结果呢?
select
语句--或更改数据结构,使整个路径作为一列。 - Gordon Linoff