How can those two SQL statements be combined into one?

Question

I wrote and would like to combine these 2 sql, one is based on results of another. I checked this post, but looks like its not results based. How could I achieve it ?

First sql:

SELECT
    `potential`.*,
    `customer`.`ID` as 'FID_customer'
FROM
    `os_potential` as `potential`,
    `os_customer` as `customer`
WHERE `potential`.`FID_author` = :randomID
      AND `potential`.`converted` = 1
      AND `potential`.`street` = `customer`.`street`
      AND `potential`.`zip` = `customer`.`zip`
      AND `potential`.`city` = `customer`.`city`;

Second sql:

SELECT
    sum(`order`.`price_customer`) as 'Summe'
FROM
    `os_order` as `order`,
    `RESUTS_FROM_PREVIOUS_SQL_STATEMENT` as `results`
WHERE `order`.`FID_status` = 10
      AND `results`.`FID_customer` = `order`.`FID_customer`;

I would like to get everything from first sql + the 'Summe' from second sql.

TABLES

1.Potentials:

+----+------------+-----------+--------+-----+------+
| ID | FID_author | converted | street | zip | city |
+----+------------+-----------+--------+-----+------+

2.Customers:

+----+--------+-----+------+
| ID | street | zip | city |
+----+--------+-----+------+

3.Orders:

+----+--------------+----------------+
| ID | FID_customer | price_customer |
+----+--------------+----------------+

It is very hard to answer your question without knowing structure of your DB and relationship between tables. Can you shed a light on it? — Andrey Korneyev, Sep 06 '16 at 12:34
Seems you may need a subquery. Use the *results from another query* that will needed to behave like a table as a subquery to the query that needs them as a table. — sçuçu, Sep 06 '16 at 12:40
@AndyKorneyev I edited my post and shown tables below my queries. — ksno, Sep 06 '16 at 12:43

score 3 · Accepted Answer · answered Sep 06 '16 at 12:45

3

SELECT p.*
     , c.ID FID_customer
     , o.summe
  FROM os_potential p
  JOIN os_customer c
    ON c.street = p.street 
   AND c.zip = p.zip 
   AND c.city = p.city 
  JOIN 
     ( SELECT FID_customer
            , SUM(price_customer) Summe
         FROM os_order 
        WHERE FID_status = 10
        GROUP
           BY FID_customer
     ) o
    ON o.FID_customer = c.ID
 WHERE p.FID_author = :randomID 
   AND p.converted = 1
   ;

answered Sep 06 '16 at 12:45

Strawberry

33,750
13
40
57

Thanks a lot. It does exactly what I need. Could you please explain a little bit how it works? – ksno Sep 06 '16 at 13:26
I took the second query, and stuck it inside the first query. It's called an uncorrelated subquery. – Strawberry Sep 06 '16 at 13:28

Gordon Linoff · Answer 2 · 2016-09-07T01:13:26.587

2

You would just write a single query like this:

SELECT sum(o.price_customer) as Summe
FROM os_order o JOIN
     os_potential p JOIN
     os_customer c
     ON p.street = c.street AND p.zip = c.zip AND p.city = c.city JOIN
     os_order o2
     ON o2.FID_customer = c.FID_customer
WHERE p.FID_author = :randomID AND p.converted = 1 AND
      o2.FID_status = 10 ;

Notes:

Never use commas in the FROM clause. Always use explicit JOIN syntax with conditions in an ON clause.
Table aliases are easier to follow when they are short. Abbreviations for the table names is commonly used.
Backticks are only necessary when the table/column name needs to be escaped. Yours don't need to be escaped.

edited Sep 07 '16 at 01:13

answered Sep 06 '16 at 12:43

Gordon Linoff

1,242,037
58
646
786

`order` is a reserved word (order by), that must be enclosed by backticks in the original query – Shadow Sep 06 '16 at 12:59
@Shadow While I agree that `order` is a poor choice for a table/column name, it doesn't need backticks in this particular context. That said, I'm not convinced that this is what the OP is looking for. – Strawberry Sep 06 '16 at 13:32
There is TYPO in last JOIN os.order should be os_order, I get error `Not unique table/alias: 'o'`, when I fix it, there is uknown column 'c.FID_customer', should be c.ID. When I fix all those. I get 1 column and 1 row with huge number. Since I am really beginner, I dont even know where it comes from. But thanks a lot for the notes. I'm willing to learn joints and good sql practices! – ksno Sep 06 '16 at 13:41

score 1 · Answer 3 · answered Sep 06 '16 at 12:55

If the 1st query return 1 record per customer, then just simply join the 3 tables, keep the sum and use the group by clause:

SELECT
    `potential`.*,
    `customer`.`ID` as 'FID_customer',
    sum(`order`.`price_customer`) as Summe
FROM
    `os_potential` as `potential`
INNER JOIN
    `os_customer` as `customer`
ON  `potential`.`street` = `customer`.`street`
      AND `potential`.`zip` = `customer`.`zip`
      AND `potential`.`city` = `customer`.`city`
LEFT JOIN
    `os_order` as `order`
ON  `results`.`FID_customer` = `order`.`FID_customer`
      AND `order`.`FID_status` = 10
WHERE `potential`.`FID_author` = :randomID
      AND `potential`.`converted` = 1
GROUP BY `customer`.`ID`, <list all fields from potential table>

If the 1st query may return multiple records per customer, then you need to do the summing in a subquery:

SELECT
    `potential`.*,
    `customer`.`ID` as 'FID_customer',
    `order`.Summe
FROM
    `os_potential` as `potential`
INNER JOIN
    `os_customer` as `customer`
ON  `potential`.`street` = `customer`.`street`
      AND `potential`.`zip` = `customer`.`zip`
      AND `potential`.`city` = `customer`.`city`
LEFT JOIN
    (SELECT FID_customer, sum(price_customer) as Summe
     FROM `os_order`
     WHERE FID_status=10 
     GROUP BY FID_customer
    ) as `order`
ON  `results`.`FID_customer` = `order`.`FID_customer`
WHERE `potential`.`FID_author` = :randomID
      AND `potential`.`converted` = 1

score 0 · Answer 4 · answered Sep 06 '16 at 12:36

I think you should use a subselect, but be careful with the number of results, it's not the best for performance.

You can do something like this:

SELECT n1, n2, (select count(1) from whatever_table) as n3, n4 from whatever_table

note that the subselect must return just 1 result, in other case you'll have an error

How can those two SQL statements be combined into one?

4 Answers4

Linked