How to query multiple non related tables?

Question

Basically I have three tables and I want to select attributes from all 3.

SELECT Users.Name, Sales.SaleID, Return.ReturnAmount
FROM Users
INNER JOIN Sales ON Users.SaleID = Sales.SaleID

However, Return has a relationship with Sales, but not Users.

How would I include returnAmount?

Show the layout of the table, along with some sample data (by editing the question). — Gordon Linoff
– Gordon Linoff, Commented Feb 8, 2014 at 14:14
It's easy enough to JOIN the table. Simply add JOIN Return ON 1=1. However, if the table is not related, how would you determine exactly which values should be shown? In the worst case scenario, you end up with a Cartesian product returning all rows of the Sales and Users table (that have a valid JOIN, times the amount of rows in the Return table. — SchmitzIT
– SchmitzIT, Commented Feb 8, 2014 at 14:17
@SchmitzIT But return is related to sales such that Sales.SaleID = Return.SaleID, but return is not related to users — user2525364
– user2525364, Commented Feb 8, 2014 at 14:18
I wonder why the relationship between Users and Sales is on SalesID though: ON Users.SaleID = Sales.SaleID So, a sale has many users and every user is related to one sale? This doesn't look correct design. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Feb 8, 2014 at 14:21
@user2525364 - That makes sense. So you JOIN he Return table to the set using those criteria. A JOIN doesn't need to connect all tables (that'd be impossible), but rather you string together pieces of information that relate to part of the set. — SchmitzIT
– SchmitzIT, Commented Feb 10, 2014 at 7:55

ypercubeᵀᴹ · Accepted Answer · 2014-02-08 14:19:52Z

1

Since you said Return has a relationship with Sales following should work. Just check the column on which you need to join Return with Sales.

 SELECT Users.Name, Sales.SaleID, Return.ReturnAmount
 FROM Users
 INNER JOIN Sales 
   ON Users.SaleID = Sales.SaleID
 INNER JOIN Return
   ON Return.SaleID = Sales.SaleID -- You said Return has relationship with Sales.

edited Feb 8, 2014 at 14:19

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

answered Feb 8, 2014 at 14:17

Adarsh Shah

6,7852 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-02-08 14:21:24Z

1

The most likely query I can think of given these table names is:

SELECT u.Name, s.SaleID, r.ReturnAmount
FROM Users u INNER JOIN
     Sales s
     ON u.UserID = s.UserID INNER JOIN
     Returns r
     on r.SaleID = s.SaleID;

It would be a very unusual data layout that has a SaleID in the Users table.

answered Feb 8, 2014 at 14:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to query multiple non related tables?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related