CODEWITHSUNDEEP
mysqlsql
Don E.
Don E.

Reputation: 83

select data from table already selected from

I have a few tables that I am trying to get data out of. I need to link the cust and emp tables to the sale table. Then, in turn, get the names of the cust and emp from the person table.

I am stuck

Data looks like:

Table SALES

+--------+--------+-------+-------+
| saleID | custID | empID | total |
|--------+--------+-------+-------|
|    1   |    1   |   1   |  3.00 |
|--------+--------+-------+-------|
|    2   |    2   |   3   |  6.00 |
|--------+--------+-------+-------|
|    3   |    3   |   1   |  9.00 |
|--------+--------+-------+-------|
|    4   |    2   |   2   |  2.00 |
|--------+--------+-------+-------|
|    5   |    3   |   3   |  1.00 |
|--------+--------+-------+-------+

Table cust

+--------+---------+----------+
| custID | company | personID
+--------+---------+----------+
|    1   |  comp1  +    2     |
+--------+---------+----------+
|    2   |  comp2  +    4     |
+--------+---------+----------+
|    3   |  comp3  +    6     |
+--------+---------+----------+

Table emp

+--------+----------+----------+
| custID | username | personID |
+--------+----------+----------+
|    1   |   emp1   |    1     |
+--------+----------+----------+
|    2   |   emp2   |    3     |
+--------+----------+----------+
|    3   |   emp3   |    5     |
+--------+----------+----------+

Table person

+----------+------+
| personID | name |
+----------+------+
|     1    | per1 |
+----------+------+
|     2    | per2 |
+----------+------+
|     3    | per3 |
+----------+------+
|     4    | per4 |
+----------+------+
|     5    | per5 |
+----------+------+
|     6    | per6 |
+----------+------+

I want to query to give me this:

+--------+--------+----------+-------+---------+-------+
| saleID | custID | custName | empID | empName | total |
+--------+--------+----------+-------+---------+-------+
|    1   |    1   |   per2   |   1   |   per1  | 3.00  |
+--------+--------+----------+-------+---------+-------+
|    2   |    2   |   per4   |   3   |   per5  | 6.00  |
+--------+--------+----------+-------+---------+-------+
|    3   |    3   |   per6   |   1   |   per1  | 9.00  |
+--------+--------+----------+-------+---------+-------+
|    4   |    2   |   per4   |   2   |   per3  | 2.00  |
+--------+--------+----------+-------+---------+-------+
|    5   |    3   |   per6   |   3   |   per5  | 1.00  |
+--------+--------+----------+-------+---------+-------+

Upvotes: 0

Views: 305

Answers (2)

G.Arima
G.Arima

Reputation: 1171

Try this out: 

select a.saleID, a.custID, d.name as custName,a.empID,e.nname as empName, 
a.total
from
   sales a
left join
   cust b
on a.custID = b.custID
left join
   emp c
on a.empID = C.custID
left join
   person d
on b.personID = d.personID
left join
   person e
on c.personID = e.personID

Let me know in case of any queries.

Upvotes: 0

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Simple JOINs should do the trick:

SELECT 
    a.saleID,
    b.custID,
    p1.name as custName,
    c.empID,
    p2.name as empName,
    a.total
FROM SALES a
JOIN cust b
ON a.custID = b.custID
JOIN emp c
ON c.custID = a.custID AND c.empID = a.empID
JOIN person p1
ON p1.personID = b.personID
JOIN person p2
ON p2.personID = c.personID
ORDER BY saleID

Upvotes: 1

Related Questions