Question 166697 in Wordpress Question posted in
The official Wordpress documentation can be found here.

columns Can WordPress get results from a table if values are present in multiple columns?

I have this table:

affiliate_id parent_affiliate_id
4 5
3 4
2 3
1 2
6 5
8 7
10 9
12 11

What I need is to make a query that shows all parents of an affiliate.

Expected outcome:

affiliate_id all_parent_id
1 2, 3, 4, 5
2 3, 4, 5
3 4, 5
… …
10 9
12 11

I’m trying to use this query but without success:

$aff = 1;
foreach( $wpdb->get_results("
SELECT A.*
 FROM {$wpdb->prefix}table A
 JOIN 
  ( SELECT affiliate_id
       , parent_affiliate_id
       FROM {$wpdb->prefix}table
       WHERE $aff IN(affiliate_id, parent_affiliate_id) 
        ) B
 ON A.affiliate_id = B.affiliate_id 
 AND A.parent_affiliate_id = B.parent_affiliate_id
 " ) as $key => $row) {
$subaff1 =  $row->parent_affiliate_id;
 echo 'Affiliate Id: '. $aff; echo ' - Parent: '. $subaff1; echo     '<br/>';
}

What am I doing wrong?

Thanks in advance.

EDITED

Maybe i solved in part
using this code

SELECT affiliate_id, (SELECT GROUP_CONCAT( parent_affiliate_id) from affiliates B where B.affiliate_id BETWEEN A.affiliate_id AND B.parent_affiliate_id) AS all_parent_id FROM affiliates A

Show me all parents of every affiliate id
but i need to show too the parent of an affiliate
that have only one parent

You can test it here
https://www.db-fiddle.com/f/dKBfxUpb8fBXLTQqRKbN2B/1

Tags:

2

Answers


  1. 0

    You can use GROUP_CONCAT and a subquery like the following:

    SELECT affiliate_id, 
(SELECT GROUP_CONCAT(affiliate_id) from affiliates B where B.parent_affiliate_id = A.affiliate_id) AS all_parent_id 
FROM affiliates A

    You can test it here https://www.db-fiddle.com/f/5WRvcMBwbwCJhTtpX9rt4v/2

    Here you can find more information. Keep it simple.

    Login or Signup to reply.
  2. 0

    It could be solved by means of recursive Common Table Expressions if available (MySQL 8.0, MariaDB starting with 10.2.2) as follows:

    WITH RECURSIVE affiliate_relation (affiliate_id, all_parent_ids) AS (
  SELECT
    DISTINCT(a_p.parent_affiliate_id),
    CAST('' AS CHAR(20))
  FROM
    affiliate AS a_p
    LEFT JOIN affiliate AS a_c
      ON a_p.parent_affiliate_id = a_c.affiliate_id
  WHERE
    a_c.affiliate_id IS NULL
  UNION
  SELECT
    a.affiliate_id,
    IF (
      a_r.all_parent_ids = '',
      a.parent_affiliate_id,
      CONCAT(a.parent_affiliate_id, ',', a_r.all_parent_ids)
    )
  FROM
    affiliate AS a
    JOIN affiliate_relation AS a_r
      ON a.parent_affiliate_id = a_r.affiliate_id
)
SELECT
  affiliate_id,
  all_parent_ids
FROM
  affiliate_relation
WHERE
  all_parent_ids != ''
ORDER BY
  affiliate_id

    It could be tested here https://www.db-fiddle.com/f/h18jBgh2322J6fj5XRofKu/1

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search