为什么不起作用?一个简单的SQL查询

时间:2022-01-11 21:47:28

I'm new to SQL, I have 3 tables in my database:

我是SQL的新手,我的数据库中有3个表:

  • One named notifications,
  • 一个名为通知,

  • the other notifications_log,
  • 其他notifications_log,

  • and the third one is control.
  • 第三个是控制。

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