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.

What language programming do you use?

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

What DB are you referring to?

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.

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.