How Can Those Two Sql Statements Be Combined Into One?

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: S

Solution 1:

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 = 10GROUPBY FID_customer
     ) o
    ON o.FID_customer = c.ID
 WHERE p.FID_author = :randomID 
   AND p.converted = 1
   ;

Solution 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 = 1AND
      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.

Solution 3:

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`) asSummeFROM`os_potential`as`potential`INNERJOIN`os_customer`as`customer`ON`potential`.`street` = `customer`.`street`AND`potential`.`zip` = `customer`.`zip`AND`potential`.`city` = `customer`.`city`LEFTJOIN`os_order`as`order`ON`results`.`FID_customer` = `order`.`FID_customer`AND`order`.`FID_status` = 10WHERE`potential`.`FID_author` = :randomID
      AND`potential`.`converted` = 1GROUPBY`customer`.`ID`, <listallfieldsfrompotentialtable>

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`.SummeFROM`os_potential`as`potential`INNERJOIN`os_customer`as`customer`ON`potential`.`street` = `customer`.`street`AND`potential`.`zip` = `customer`.`zip`AND`potential`.`city` = `customer`.`city`LEFTJOIN
    (SELECT FID_customer, sum(price_customer) asSummeFROM`os_order`WHERE FID_status=10GROUPBY FID_customer
    ) as`order`ON`results`.`FID_customer` = `order`.`FID_customer`WHERE`potential`.`FID_author` = :randomID
      AND`potential`.`converted` = 1

Solution 4:

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, (selectcount(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