CODEWITHSUNDEEP
sql
Vincent Lam
Vincent Lam

Reputation: 29

SQL query from multiple tables

I am new to SQL, I need some help,

I want to find out confirmed (confirmed=1) reservation date with last name Steven

Can some one help for with the this SQL Query

table_member

+-----------+-----------+
|Member_id  | last_name |
+-----------+-----------+
| 1         | David     |
| 2         | owen      |
| 3         | Tom       |
| 4         | Steven    |
| 5         | Steven    |
| 6         | Steven    |
| 7         | Steven    |
| 8         | Steven    |
| 9         | Steven    |
+-----------+-----------+

table_resevation

+-----------+-----------+-----------+-----------+
|resvationID|Member_id  | day       |   month   |
+-----------+-----------+-----------+-----------+
| 1         | 1         |   1       |     6     |
| 2         | 1         |   2       |     6     |
| 3         | 2         |   1       |     6     |
| 4         | 4         |   1       |     6     |
| 5         | 4         |   2       |     6     |
| 6         | 5         |   1       |     6     |
| 7         | 5         |   2       |     6     |
| 8         | 7         |   3       |     6     |
+-----------+-----------+-----------+-----------+

Confirm

+-----------+-----------+-----------+
|confirmID  |resvationID| confirmed |
+-----------+-----------+-----------+
| 1         | 1         |   1       |
| 2         | 2         |   1       |
| 3         | 3         |   0       |
| 4         | 4         |   0       |
| 5         | 5         |   1       |
| 6         | 6         |   1       |
| 7         | 7         |   0       |
| 8         | 8         |   1       |
+-----------+-----------+-----------+

Upvotes: 2

Views: 101

Answers (3)

Ranjit Singh
Ranjit Singh

Reputation: 3735

Apply inner join on all table

select day,month from table_reservation inner join table_member on table_member.member_id=table_reservation.member_id
inner join confirm on confirm.reservationid=table_reservation.reservationid where confirm.confirmid=1
and table_member.last_name like 'Steven'

Upvotes: 0

pixelsucht
pixelsucht

Reputation: 228

Select c.*, r.*, m.*
from confirm c
left join table_reservation r on c.reservationID = r.reservationID
left join table_member m on m.member_id = r.Member_id
Where c.confirmed = 1
and m.name = "Steven"

I did not test it, but that should point you to the way.

(Edit: second "where" was a typo)

Upvotes: 0

marc_s
marc_s

Reputation: 754598

Does something like this work for you?

 SELECT
     m.Member_ID, m.Last_Name,
     r.Day, r.Month, 
     c.ConfirmID
 FROM 
     table_member m
 INNER JOIN 
     table_reservation r ON r.Member_ID = m.Member_ID
 INNER JOIN
     Confirm c ON c.resvationID = r.resvationID
 WHERE
     m.Last_Name = 'Steven' AND c.confirmed = 1

Upvotes: 3

Related Questions