Member Avatar for SimonIoa

I have three tables(table, table2,table3). From table1 i want to return three rows: Title, Desc,time,'products' AS type

From table2 i want to return three rows: group_title, group_desc, created,'groups' AS type

From table3 i want to return three rows: name, occupation,birth,'users' AS type

  $sql = "SELECT DISTINCT * 
    FROM
        (SELECT table1.title, table1.desc, table1.time,'products' AS type
        FROM table1 ORDER BY rand() LIMIT 5) AS T
     UNION ALL
        (SELECT table2.group_name,table2.group_desc,table2.created,'groups' AS type
        FROM tabl2 
        JOIN table1 ON table2.id = table1.id 
         ORDER BY rand() LIMIT 5)
    UNION ALL
        (SELECT table3.name,table3.occupation,table3.birth,'users' AS type
        FROM table3
        JOIN  table1 ON table3.id = table1.id
        ORDER BY rand() LIMIT 5)";

am using Ionic to push those three parameters of an Array to the next page so if i use AS and put the same name. I set a parameter AS type that i need so i can work different types. it has to be 'products' AS type for table1, 'groups' AS type for table2, users as type for table3 i want to separate the objects from each table.

It cant use AS the for the rest of the columns because i want to push the original columns
e.g.

 (SELECT table1.title AS title, table1.desc AS desc, table1.time AS time
    FROM table1 ORDER BY rand() LIMIT 5) AS T
 UNION ALL
    (SELECT table2.group_name AS title,table2.group_desc AS desc,table2.created AS time.....

If i do that it displays the object parameters ok e.g. {{item.title}}

but i can not push or navctrl the parameter as title it has to be group_name. I hope i made myself clear.

  • 6 Contributors
  • 5 Replies
  • 1K Views
  • 4 Months Discussion Span
  • Latest Post Latest Post by Biiim

Dani AI

Generated

you can absolutely combine rows from three tables, but every SELECT in a UNION must return the same set of columns. Column names in the final result come from the first SELECT only, as noted the shape is dictated by the DB engine (MySQL here). is right that you cannot have per-row column names. Two practical patterns that work well with Ionic:

  1. Normalize column names in SQL, keep a type flag, and sort only once at the end.
SELECT title, description, ts, type
FROM (
  SELECT t1.title AS title, t1.`desc` AS description, t1.`time` AS ts, 'products' AS type
  FROM table1 ORDER BY RAND() LIMIT 5
  UNION ALL
  SELECT t2.group_name, t2.group_desc, t2.created, 'groups'
  FROM table2 ORDER BY RAND() LIMIT 5
  UNION ALL
  SELECT t3.name, t3.occupation, t3.birth, 'users'
  FROM table3 ORDER BY RAND() LIMIT 5
) u
ORDER BY ts DESC;

Client tip: before pushing to the next page, map to whatever keys your component expects. For example in PHP:

$mapped = array_map(function($r){
    if ($r['type'] === 'groups') { $r['group_name'] = $r['title']; }
    if ($r['type'] === 'products') { $r['product_title'] = $r['title']; }
    return $r;
}, $rows);
  1. If you must preserve original field names per row, return a JSON payload per row (MySQL 5.7+), then decode in PHP/Ionic and use the native keys.
SELECT JSON_OBJECT('title', t1.title, 'desc', t1.`desc`, 'time', t1.`time`) AS payload, 'products' AS type FROM table1 LIMIT 5
UNION ALL
SELECT JSON_OBJECT('group_name', t2.group_name, 'group_desc', t2.group_desc, 'created', t2.created), 'groups' FROM table2 LIMIT 5
UNION ALL
SELECT JSON_OBJECT('name', t3.name, 'occupation', t3.occupation, 'birth', t3.birth), 'users' FROM table3 LIMIT 5;

Notes:

  • Use a single outer ORDER BY; inner ORDER BY applies only to each LIMIT.
  • Avoid using reserved words as column names; quote desc with backticks.
  • Only JOIN tables if you intend to filter to related rows; otherwise drop the JOINs, as hinted.

Recommended Answers

All 5 Replies

Member Avatar for Anninflow

What language programming do you use?

Member Avatar for Reverend Jim

The tag says php but the format of the query string does not depend on the language, just on the specific db engine.

Member Avatar for Antionne

What DB are you referring to?

Member Avatar for DGPickett

The join table to itself tactic is very good for converting rows to columns. There are other tricks you can use with aggregates and case statements.

Member Avatar for Biiim

You seem to be going about this the wrong way.

An SQL query returns a result of COLUMNS & ROWS, you can't have 3 rows with different column names mixed with another 3 rows of different columns. The query result has to have the same columns for all the rows, you could possibly make 12 columns and have them NULL for the tables that dont have those values.

It sounds like from what you are saying you need to make 3 separate queries to each of the 3 tables and then arrange them into an array in PHP:

$sqlData1 = [];
$sqlData2 = [];
$sqlData3 = [];

$compoundedData = [];
foreach($sqlData1 as $k=>$v){
    //specific column names for this data translated into $compoundedData
    $compoundedData[] = $v;
}
foreach($sqlData2 as $k=>$v){
    //specific column names for this data translated into $compoundedData
    $compoundedData[] = $v;
}
foreach($sqlData3 as $k=>$v){
    //specific column names for this data translated into $compoundedData
    $compoundedData[] = $v;
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.