I'm new to SQL, I have 3 tables in my database:
我是SQL的新手,我的数据库中有3个表:
- One named
notifications
, - the other
notifications_log
, - and the third one is
control
.
一个名为通知,
其他notifications_log,
第三个是控制。
Both notification
and notification_log
have notification_id
with pk-fk relation.
notification和notification_log都有notification_id和pk-fk关系。
There is also another column named control_id
in notifications
and I have this column in control
table too.
通知中还有另一个名为control_id的列,我在控制表中也有这个列。
Now what I want to do is to get rows under description
column of notification_log
table by connecting notification
table and control
table with their control_id
field. Can you help me with that?
现在我想要做的是通过将通知表和控制表与他们的control_id字段连接来获取notification_log表的description列下的行。你能帮帮我吗?
Here's something I've tried:
这是我尝试过的东西:
select c.control_name
from notifications note, notifications_log note_log, control c
where note_log.ALARM_ID = note.ALARM_ID
and note.CONTROL_ID = C.CONTROL_ID
order by control_name desc
3 个解决方案
#1
2
Use JOIN:
SELECT c.control_name, note_log.description FROM notifications note
INNER JOIN notifications_log note_log
ON note.notification_id = note_log.notification_id
INNER JOIN control c
ON note.control_id = c.control_id
ORDER BY c.control_name DESC
What you have to understand is if every record in control table has a corresponding record in other tables, otherwise it will not be showed with INNER JOIN.
您需要了解的是控制表中的每个记录是否在其他表中都有相应的记录,否则将不会显示INNER JOIN。
#2
0
select description from notification_log where notification_id in (select notification_id from notification)
从notification_log中选择说明,其中notification_id在(从通知中选择notification_id)
if you want to get only the description..
如果你只想获得描述..
hope it helps :D
希望它有所帮助:D
#3
0
From your description, it sounds as though you are selecting the wrong field in your query. Try:
从您的描述中,听起来好像您在查询中选择了错误的字段。尝试:
select distinct note_log.description
from notifications note, notifications_log note_log, control c
where note_log.notification_id = note.notification_id
and note.CONTROL_ID = C.CONTROL_ID
order by 1
#1
2
Use JOIN:
SELECT c.control_name, note_log.description FROM notifications note
INNER JOIN notifications_log note_log
ON note.notification_id = note_log.notification_id
INNER JOIN control c
ON note.control_id = c.control_id
ORDER BY c.control_name DESC
What you have to understand is if every record in control table has a corresponding record in other tables, otherwise it will not be showed with INNER JOIN.
您需要了解的是控制表中的每个记录是否在其他表中都有相应的记录,否则将不会显示INNER JOIN。
#2
0
select description from notification_log where notification_id in (select notification_id from notification)
从notification_log中选择说明,其中notification_id在(从通知中选择notification_id)
if you want to get only the description..
如果你只想获得描述..
hope it helps :D
希望它有所帮助:D
#3
0
From your description, it sounds as though you are selecting the wrong field in your query. Try:
从您的描述中,听起来好像您在查询中选择了错误的字段。尝试:
select distinct note_log.description
from notifications note, notifications_log note_log, control c
where note_log.notification_id = note.notification_id
and note.CONTROL_ID = C.CONTROL_ID
order by 1