Question 217822 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – One SQL query, two records?

I trying to fetch data from two related tables using a foreign key and keep the data as separate records / objects.

The best solution I have found so far is using JOINs with column prefixing to differentiate between the two tables.

SELECT
  tableA.method AS tableA_method,
  tableB.primitiveType AS tableB_primitiveType
FROM tableB
JOIN tableA ON tableB.tableA_id = tableA.id
WHERE tableB.id = ?

This works, but means I have to split the data after the query is completed. Is there a way to perform a single SQL query that returns separate records for each table’s data?

Current result: A single object/RowDataPacket with columns from both tables

[ RowDataPacket { tableA_method: "some value", tableB_primitiveType: "some value }]

Desired result: Two objects, one for each table

[ RowDataPacket { method: "some value" }, RowDataPacket { primitiveType: "some value" }]

or

[ RowDataPacket { tableA: { method: "some value" }, tableB: { primitiveType: "some value }}]

I’ve looked into subqueries, but they involve multiple queries and might not be the most efficient solution.

Tags:

2

Answers


  1. 0

    What you want makes only sense if the two tables have the same column. Let’s say tableA has a column name and tableB as well. You can now JOIN them but then you get 2 columns. If you want to "merge" the result set you can use UNION.

    SELECT name FROM tableA
UNINON
SELECT name FROM tableB

    A single SELECT statement is not capable of returning different result sets from different tables. But why should it do that? You can just run 2 different SELECTS.

    Login or Signup to reply.
  2. 0

    You could set up a stored procedure to execute your two queries in one go.

    Set up:

    DELIMITER // ;
CREATE PROCEDURE myProcedure (IN tableB_id INT)
       BEGIN

          SELECT tableA.*
          FROM tableA
          INNER JOIN tableB ON tableA.id = tableB.tableA_id
          WHERE tableB.id = tableB_id;

          SELECT tableB.*
          FROM tableB
          WHERE tableB.id = tableB_id;

       END//
DELIMITER ;

    Then, whenever you want to return your two result sets with a single call – for example for an id of 123, you can use:

    CALL myProcedure(123);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search