CODEWITHSUNDEEP
sqlpostgresqljoin
fcarle
fcarle

Reputation: 65

Split the queries or multiple joins for independent tables

I've looked for hours for this and I can't find the proper solution. I'll start by saying what I'm expecting as a result with fictional tables. After parsing the sql query, I want to return something like this:

[
  {
    "storeID": 1,
    "storeName": "best store ever",
    "products": [
      {
        "productID": 1,
        "productName": "Name1",
        "productPrice": 10.00
      },
      {
        "productID": 2,
        "productName": "Name2",
        "productPrice": 10.00
      },
      {
        "productID": 3,
        "productName": "Name3",
        "productPrice": 10.00
      },
      ...
    ],
    "employees": [
      {
        "employeeID": 1,
        "employeeName": "Name1",
        "employeeSalary": 40000.00
      },
      {
        "employeeID": 2,
        "employeeName": "Name1",
        "employeeSalary": 45000.00
      },
      ...
    ]
  },
  ...
]

My problem is with the join of the stores table with the products and employees tables. Right now I'm doing this:

SELECT
  s.storeID,
  s.storeName,
  p.productID,
  p.productName,
  p.productPrice,
  e.employeeID,
  e.employeeName,
  e.employeeSalary,
FROM stores AS s
LEFT JOIN products AS p ON p.fk_store_id = s.storeID
LEFT JOIN employees AS e ON e.fk_store_id = s.storeID;

With this I get products x employees number of rows for each store which is a significant overhead. If I can get to products + employees number of rows that would be ok. But that seem to be the same as doing 2 separate queries... One for the stores joining the products and one for the stores joining the employees.

What would be the more appropriate solution for this?

Upvotes: 1

Views: 2094

Answers (2)

clinomaniac
clinomaniac

Reputation: 2218

This is what I meant by using a UNION. There will be some manipulation required to look at the type column and figure out how to handle it in the next layer. 

SELECT
  s.storeID,
  s.storeName,
  'Employee' AS type,
  e.employeeID AS id,
  e.employeeName AS name,
  e.employeeSalary AS cost
FROM stores AS s
LEFT JOIN employees AS e ON e.fk_store_id = s.storeID
UNION
SELECT
  s.storeID,
  s.storeName,
  'Product' AS type,
  p.productID,
  p.productName,
  p.productPrice
FROM stores AS s
LEFT JOIN products AS p ON p.fk_store_id = s.storeID;

Single query using a join is not possible since there is no direct relation between product and employee so you will be joining with store and that will give you a product X employee join.

Upvotes: 0

Md Monjur Ul Hasan
Md Monjur Ul Hasan

Reputation: 1791

The type of join you are trying here is not appropriate case of LEFT join. When you make a join between 3 tables: A, B and C; table A and B joins first and then with the result of this join, table C will be join. So, if both table B and C has data connected to all the rows of table A, then the total number of row will be n(A) * n(B) * n(C).

For your desire output, you need to have two queries. Alternatively, you can make one query, and then use your front end scripting to convert the result to your desire output.

Upvotes: 2

Related Questions