Inner Joining Three Tables

Inner Joining three tables

select *
from
    tableA a
        inner join
    tableB b
        on a.common = b.common
        inner join 
    TableC c
        on b.common = c.common

SQL Inner-join with 3 tables?

You can do the following (I guessed on table fields,etc)

SELECT s.studentname
    , s.studentid
    , s.studentdesc
    , h.hallname
FROM students s
INNER JOIN hallprefs hp
    on s.studentid = hp.studentid
INNER JOIN halls h
    on hp.hallid = h.hallid

Based on your request for multiple halls you could do it this way. You just join on your Hall table multiple times for each room pref id:

SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID

Join three tables on condition

We can do LEFT JOIN on B and C tables separately using id. Now, we can utilize COALESCE(), to consider B.bedrooms first (if not null), else C.bedrooms. Now, there is a chance that both can be null (that is, id does not exist in either of the tables).

In that case, if you don't want those rows, you can put an additional WHERE condition to consider only those id(s), which has atleast one matching row in either B or C.

Here is one way:

select A.id,
       A.price,
       COALESCE(B.bedrooms, C.bedrooms) AS bedrooms 
from A 
left join B on A.id = B.id
left join C on A.id = C.id
WHERE NOT (B.bedrooms IS NULL AND 
           C.bedrooms IS NULL)
order by A.id

Inner Join for 3 tables with SUM of two columns in SQL Query?

PROBLEM

The problem here is that there are consecutive inner joins and the number of rows getting fetched in the second inner join is not restricted. So, as we have not added a condition on sales_payment_id in the join between the sales and sales_payment tables, one row in sales table(for customer_id 2, in this case) would be mapped to 2 rows in the payment table. This causes the same values to be reconsidered.
In other words, the mapping for customer_id 2 between the 3 tables is 1:1:2 rather than 1:1:1.

SOLUTION

Solution 1 : As mentioned by Gordon, you could first aggregate the amount values of the sales_payments table and then aggregate the values in sales table.

Solution 2 : Alternatively (IMHO a better approach), you could add a foreign key between sales and sales_payment tables. For example, the sales_payment_id column of sales_payment table can be introduced in the sales table as well. This would facilitate the join between these tables and reduce additional overheads while querying data.

The query would then look like:

`SELECT c.customer_id,
  c.name,
  SUM(s.total),
  s.created_at,
  SUM(sp.amount)
FROM customer c
INNER JOIN sales s
ON c.customer_id = s.customer_id
INNER JOIN sales_payments sp
ON c.customer_id        = sp.customer_id
AND s.sales_payments_id = sp.sales_payments_id
WHERE s.created_at      ='2020-04-03'
GROUP BY c.customer_id,
c.name,
s.created_at ;`

Hope that helps!

SQL INNER JOIN using CASE and 3 tables

Start with a basic JOIN between all three tables, on the related columns. Note use of table aliases t - Types, p - Products, a - Availability

SELECT * 
FROM   Types t 
         INNER JOIN Products p ON p.Type_Id = t.Type_Id
         INNER JOIN Availability a ON a.Availability_Id = p.Availability_Id

Next add a WHERE clause to filter results by the availability status:

SELECT * 
FROM   Types t 
        INNER JOIN Products p ON p.Type_Id = t.Type_Id
        INNER JOIN Availability a ON a.Availability_Id = p.Availability_Id
WHERE  a.Name IN ('Available', 'Reserved')

Results:

Type_Id | Name          | Product_Id | Name           | Type_Id | Availability_Id | Availability_Id | Name     
------: | :------------ | ---------: | :------------- | ------: | --------------: | --------------: | :--------
      1 | Candy         |          1 | Chocolate Name |       1 |               1 |               1 | Available
      2 | Chocolate Bar |          2 | Chcoolate Name |       2 |               2 |               2 | Reserved 
      2 | Chocolate Bar |          3 | Candy Name     |       2 |               2 |               2 | Reserved 

Finally COUNT(*) the total rows matched, grouping by category (i.e. [Type].[Name])

SELECT t.Type_Id
       , t.Name AS Type_Name
       , COUNT(*) AS Total_Products
FROM   Types t 
        INNER JOIN Products p ON p.Type_Id = t.Type_Id
        INNER JOIN Availability a ON a.Availability_Id = p.Availability_Id
WHERE  a.Name IN ('Available', 'Reserved')
GROUP BY t.Type_Id
         , t.Name

Type_Id | Type_Name     | Total_Products
------: | :------------ | -------------:
      1 | Candy         |              1
      2 | Chocolate Bar |              2

SQL Fiddle

Can you join three tables on the same key if that key could be missing from one or more of the tables?

Here's a version that demonstrates a fully three way full outer join. A successful query will return seven rows:

+------+  +------+  +------+     +------+------+------+
| colA |  | colB |  | colC |     | colA | colB | colC |
+------+  +------+  +------+     +------+------+------+
|    1 |  |    2 |  |    4 |     |    1 | null | null |
|    3 |  |    3 |  |    5 | --> | null |    2 | null |
|    5 |  |    6 |  |    6 |     |    3 |    3 | null |
|    7 |  |    7 |  |    7 |     | null | null |    4 |
+------+  +------+  +------+     |    5 | null |    5 |
                                 | null |    6 |    6 |
                                 |    7 |    7 |    7 |
                                 +------+------+------+

select ColA, ColB, ColC
  from TableA
  full join TableB
    on ColA=ColB
  full join TableC
    on ColC=coalesce(ColA, ColB)
order by coalesce(ColA, ColB, ColC);

The secret is to coalesce the prior table keys in the join condition of each additional table.

You can see it in action here

Join three tables A, B, C and return common in A in mysql

I understand that you want rows from a whose id can be foud in either b or c.

This sounds like two exists subquery:

select a.*
from a
where 
    exists (select 1 from b where b.id = a.id)
    or exists (select 1 from c where c.id = a.id)

If you also want columns from tables b or c, you can use two left joins instead, with a where condition that ensures that at least one of the joins did succeed:

select a.*, b.org, c.dept
from a
left join b on b.id = a.id
left join c on c.id = a.id
where b.id is not null or c.id is not null

Write a inner join query for three tables in sql

The right and working query is:
SELECT *, tags.titel AS tags FROM posts_tags INNER JOIN tags ON posts_tags.post_id = tags.id INNER JOIN posts ON posts.id = tags.id GROUP BY posts.id;

How to join three tables and set blank fields to null?

A CROSS JOIN produces all the combinations you want. Then a simple outer join can retrieve the related rows (should they exist).

You don't mention the database you are using so a faily standard query will do. For example (in PostgreSQL):

select
  row_number() over(order by p.id, t.id) as id,
  p.name,
  case when x.st is not null then t.hr end,
  x.st
from person p
cross join tasks t
left join task x on x.personid_fk = p.id and x.taskid_fk = t.id
order by p.id, t.id;

Result:

 id  name  case   st    
 --- ----- ------ ----- 
 1   Anna  null   null  
 2   Anna  null   null  
 3   Luo   13:00  true  
 4   Luo   14:00  false 
 5   John  null   null  
 6   John  null   null  

See running example at DB Fiddle.

---

Related Topics