CODEWITHSUNDEEP
sql
ThunderBirdsX3
ThunderBirdsX3

Reputation: 588

I want to join 3 tables from this data

I have 3 tables.

customers

-------------------
custID | custName |
-------------------
   1   | 1        |
   2   | 2        |
   3   | 3        |
-------------------

cars

-------------------
carID  | custID   |
-------------------
  A    | 1        |
  B    | 1        |
  C    | 2        |
  D    | 2        |
  E    | 3        |
-------------------

bill

-------------------------------------
billNo | carID | custID | billTotal |
-------------------------------------
1      |   A   | 1      | 100       |
2      |   B   | 1      | 100       |
3      |   D   | 2      | 100       |
4      |   E   | 3      | 100       |
5      |   A   | 1      | 100       |
6      |   C   | 2      | 100       |
7      |   C   | 2      | 100       |
8      |   E   | 3      | 100       |
9      |   B   | 1      | 100       |
10     |   D   | 2      | 100       |
11     |   A   | 1      | 100       |
12     |   E   | 3      | 100       |
13     |   E   | 3      | 100       |
14     |   B   | 1      | 100       |
-------------------------------------

I want to make JOIN 3 tables as illustrated below.

----------------------------------------
custID | custName | countCar | sumBill |
----------------------------------------
1      | 1        |    2     |   600   |
2      | 2        |    2     |   400   |
3      | 3        |    1     |   400   |
---------------------------------------

what SQL code can make it show this result??? what SQL code can make it show this result??? what SQL code can make it show this result??? what SQL code can make it show this result??? what SQL code can make it show this result???

PS.my english is not well.

Upvotes: 0

Views: 47

Answers (2)

Neel
Neel

Reputation: 11731

well I will not tell u the exact answer else u will not learn it but I will write whatever I understood from ur query

SELECT TableA.*, TableB.*, TableC.*
FROM TableA
    JOIN TableB
        ON TableB.aID = TableA.aID
    JOIN TableC
        ON TableC.cID = TableB.cID
 GROUP BY    TableA.aID ,
                TableA.aName

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

How about something like

SELECT  c.custID,
        c.custName,
        COUNT(DISTINCT b.carID) countCar,
        SUM(b.billTotal) sumBill
FROM    customers c INNER JOIN
        bill b  ON  c.custID = b.custID
GROUP BY    c.custID,
            c.custName

Have a look at SQL COUNT() Function, SQL SUM() Function, SQL GROUP BY Statement and SQL Joins

Upvotes: 3

Related Questions