Avoiding a cross-Join

I have a query which calculates the sum/ count of amount (QTY) for a Prefix for a particular Client. A Prefix has been used in place of product to reduce versions/ naming variants.

This query is run on two separate tables that are linked by a foreign key, where client_ID is the shared attribute and primary key in the third table. One table is called Purchased and the other Installed.

I am attempting to calculate what the difference between the quantity installed and quantity purchased is along with a number of additional fields from each table. The concept is similar to a stock check (Amountinstock - AmountSold).

The issue that I’m having is that this creates a cr开发者_开发百科oss-join on the result. What are the method(s) to avoid the cross-join? Would outer, right or left joins resolve this or do I need to utilise a union statement.

The tables are as follows:

Client  ( Client_ID*, Client) 
Purchased (Client_ID, Product, Prefix, License Status, Amount, Deployed at, Start_date, End_date) 
Installed (Client_ID, Product, Prefix, Publisher, Version, Domain, Server, Amount) 
*Primary Key 

The quantity of the Prefix & Client query code is:

SELECT 
    Installed.Client_ID, 
    Client.Client, 
    Installed.Prefix, 
    SUM(Installed.Amount) AS QuantityofLicensesInstalled
FROM Installed 
    INNER JOIN Client 
        ON Installed.Client_ID=Client.Client_ID
GROUP BY Installed.Client_ID, Installed.Prefix, Client.Client;

The code attempting to join the results is:

SELECT 
    Installed.Prefix, 
    QuantityofLicensesInstalled, 
    Purchased.Prefix, 
    QuantityofLicensesPurchased, 
    (QuantityofLicensesInstalled-QuantityofLicensesPurchased) AS Differencebetweenvalues
FROM ClientIDPrefixSumInstalled, ClientIDPrefixSumPurchased;

This is currently producing a cross join result.

First: drop the client column from ClientIDPrefixSumPurchased and ClientIDPrefixSumInstalled both from the select clause and the group by.

Then this might result in what you need.

SELECT 
    Installed.Prefix, 
    QuantityofLicensesInstalled, 
    Purchased.Prefix, 
    QuantityofLicensesPurchased, 
    (QuantityofLicensesInstalled-QuantityofLicensesPurchased) AS Differencebetweenvalues
FROM ClientIDPrefixSumInstalled 
full outer join ClientIDPrefixSumPurchased on installed.prefix = purchased.prefix;

Obtain a single list of all the prefixes present in both tables, get the total amount for every prefix in each table, then left join the totals to the prefix list to calculate the difference.

SELECT
  c.Client,
  a.Prefix,
  IFNULL(i.SumAmount, 0) - IFNULL(p.SumAmount, 0) AS AmountDiff

FROM (
  SELECT Client_ID, Prefix
  FROM Installed
  UNION
  SELECT Client_ID, Prefix
  FROM Purchased
) a

  INNER JOIN Client c ON a.Client_ID = c.Client_ID

  LEFT JOIN (
    SELECT Client, Prefix, SUM(Amount) AS SumAmount
    FROM Installed
    GROUP BY Client, Prefix
  ) i
    ON a.Client_ID = i.Client_ID AND a.Prefix = i.Prefix

  LEFT JOIN (
    SELECT Client, Prefix, SUM(Amount) AS SumAmount
    FROM Purchased
    GROUP BY Client, Prefix
  ) p ON a.Client_ID = p.Client_ID AND a.Prefix = p.Prefix