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
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
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 3 tables with same key
You have to repeat JOIN for each separate table:
SELECT *
FROM dbo.IM_PRC
INNER JOIN dbo.IM_ITEM ON dbo.IM_PRC.ITEM_NO = dbo.IM_ITEM.ITEM_NO
INNER JOIN dbo.IM_INV_CELL ON dbo.IM_INV_CELL.ITEM_NO = dbo.IM_PRC.ITEM_NO
WHERE (dbo.IM_ITEM.ITEM_VEND_NO = 'X')
I recommend using aliases and removing unnecessary parentheses too:
SELECT *
FROM dbo.IM_PRC P
INNER JOIN dbo.IM_ITEM I ON P.ITEM_NO = I.ITEM_NO
INNER JOIN dbo.IM_INV_CELL IC ON IC.ITEM_NO = P.ITEM_NO
WHERE I.ITEM_VEND_NO = 'X'
SQL- how to join 3 tables but only group by columns from two
It's just another join and a WHERE clause filtering on t3.desc. It doesn't seem like you need the columns from T3 in the projection.
select t1.customer_ID, Max(t2.amount)
from t1
join t2
on t1.product_No = t2.product_No
join t3
on t1.product_No = t3.valid_product
where t3.desc = 'Y'
Group by t1.customer_ID
Incidentally, you will notice I wrote the query using the ANSI 92 join syntax. Oracle has supported this since 9i (twenty years now) and it really does make queries easier to read.
SQL Join 3 tables with null values
A little tweak on Thorsten Kettner's answer made it work:
Thanks everyone :)
SELECT
mr.member_id,
mr.name,
mr.tag,
pt.semester,
pt.date,
vt.date
FROM members mr
LEFT JOIN
(
SELECT
member_id,
semester,
date
FROM payment
WHERE ( member_id, date ) IN
(
SELECT
member_id,
MAX(date)
FROM
payment
GROUP BY
member_id
)
) pt ON pt.member_id = mr.member_id
LEFT JOIN
(
SELECT
member_id,
date,
door
FROM visit
WHERE ( member_id, date ) IN
(
SELECT
member_id,
MAX(date)
FROM
visit
GROUP BY
member_id
)
) vt ON vt.member_id = mr.member_id
Related Topics
Difference Between Join and Inner Join
Does Postgresql Support "Accent Insensitive" Collations
MySQL - How to Unpivot Columns to Rows
MySQL Insert Query Doesn't Work With Where Clause
Does the Join Order Matter in Sql
Condition Within Join or Where
How to Do a Batch Insert in MySQL
SQL Server Process Queue Race Condition
How to Create a Comma-Separated List Using a SQL Query
MySQL Cannot Add Foreign Key Constraint
MySQL Query to Dynamically Convert Rows to Columns
Storing Json in Database Vs. Having a New Column For Each Key