CODEWITHSUNDEEP
sqljoin
alex weeks
alex weeks

Reputation: 45

Comparing SQL Queries with Joins

--Query1

SELECT TransactionDetails.Transactions.TransactionId
,     TransactionDetails.Transactions.CustomerId
,     TransactionDetails.Transactions.TransactionType
,     TransactionDetails.Transactions.DateEntered
,     TransactionDetails.Transactions.RelatedProductId
,     CustomerDetails.CustomerProducts.CustomerFinancialProductId
,     CustomerDetails.CustomerProducts.CustomerId AS 'CustomerProducts--CustomerID'
,     CustomerDetails.CustomerProducts.FinancialProductId
,     CustomerDetails.CustomerProducts.AmountToCollect
FROM TransactionDetails.Transactions 
    INNER JOIN CustomerDetails.Customers ON TransactionDetails.Transactions.CustomerId =       CustomerDetails.Customers.CustomerID 
    INNER JOIN CustomerDetails.CustomerProducts ON CustomerDetails.Customers.CustomerID =     CustomerDetails.CustomerProducts.CustomerId
WHERE TransactionId = 2
ORDER BY CustomerFinancialProductId

--Query2

SELECT TransactionDetails.Transactions.TransactionId
    , TransactionDetails.Transactions.CustomerId
    , TransactionDetails.Transactions.TransactionType
    , TransactionDetails.Transactions.DateEntered
    , TransactionDetails.Transactions.RelatedProductId
    , CustomerDetails.CustomerProducts.CustomerFinancialProductId
    , CustomerDetails.CustomerProducts.CustomerId AS 'CustomerProducts--CustomerID'
    , CustomerDetails.CustomerProducts.FinancialProductId
    , CustomerDetails.CustomerProducts.AmountToCollect
FROM TransactionDetails.Transactions 
    INNER JOIN CustomerDetails.FinancialProducts ON     TransactionDetails.Transactions.RelatedProductId = CustomerDetails.FinancialProducts.ProductId 
    INNER JOIN CustomerDetails.CustomerProducts ON     CustomerDetails.FinancialProducts.ProductId = CustomerDetails.CustomerProducts.FinancialProductId
WHERE TransactionId = 2
ORDER BY CustomerFinancialProductId

Here are two queries that i have been given to compare. it asks which one navigates better ir should be the correct one used?

I know that the first query returns only 1 row where as the second returns 11 rows. Also Query 1 navigates through Transactions to Customers where it's using ProductID as it's column in each. the 2nd query goes from Transactions to FinancialProducts where the join is on RelatedProductID in the transactions table and productID in the FinancialProducts table

Both queries then end in the same table with the same columns chosen in that table.

Lastly the FinancialProductID is 22 in the first query and in the 2nd query all 11 rows have a financialProductID of 22

ACTUAL QUESTION: WHICH ONE IS THE CORRECT FORM OF NAVIGATING TO THE TABLE

Upvotes: 0

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The question that you have asked is a business question about the meaning of the data.

It looks like both versions of the query are properly joining the tables together. You understand your business domain, whereas the rest of us do not.

That said, based on the names of the tables, the first version "makes sense" to me. A transaction has a customer and customers have products. So, if you want all the products for a customer on a transaction, then the first would seem to do that.

I don't know what role FinancialProducts plays in this database. It is quite possible that the second version "makes more sense" for this application. If you are using this database, you should learn what these tables are.

Upvotes: 1

Related Questions