我对mysql查询有疑问。故事是这样的:我有一张桌子,里面存储着有关大学旅行的信息。此表包含有关行程名称、行程 ID 和活动的属性。活动可以是 0 或 1,具体取决于行程是仍处于活动状态 (1) 还是非活动 (0)。在第二个表格中,我有关于申请旅行的学生的信息,这些属性包括:id、姓名、姓氏和学生申请的旅行的 id。我不知道mysql查询只会向我显示已申请仍然活跃的旅行的学生(活动性= 1)。
例如,让我们看一下这些表:
TRIPS
id | trip | activity
---+----------+-----------
1 | Paris | 0
2 | London | 1
3 | Belgrade | 0
4 | Prague | 1
STUDENTS
id | name | id_trip
---+----------+-----------
1 | Mark | 3
2 | Ana | 1
3 | Tom | 2
4 | Maya | 3
5 | Rachel | 4
6 | John | 2
RESULT
id | name | id_trip | trip | activity
---+----------+---------+---------+---------
3 | Tom | 2 | London | 1
5 | Rachel | 4 | Prague | 1
6 | John | 2 | London | 1
SELECT
s.id,
s.name,
s.id_trip,
t.trip,
t.activity
FROM
STUDENTS AS s
INNER JOIN TRIPS AS t ON ( t.id = s.id_trip )
WHERE
t.id = 1
希望这会起作用。
试试这个:
SELECT s.id, s.name, s.id_trip, t.name, t.activity
FROM students s
JOIN trips t
ON s.id_trip = t.id
WHERE t.activity = 1
select * from students s
join trips t
on s.id_trip = t.id
where t. activity =1
试试这个,它可能会解决你的问题:
select s.id as id, s.name as name,t.trip as trip,
t.activity as activity from trips t
join students s on
t.id = s.id_trip
where t.activity = 1