MySQL回馈了许多值

Question

我有一个MariaDB数据库。

    #Create table room
create table if not exists room
(
  pk_roomNumber   INTEGER PRIMARY KEY NOT NULL,
  roomdescription VARCHAR(50),
  netmask         VARCHAR(15),
  subnetmask      VARCHAR(15)
);

#Create table client
CREATE TABLE IF NOT EXISTS client
(
  pk_macaddr       VARCHAR(17) PRIMARY KEY NOT NULL,
  ipadress         VARCHAR(15),
  hostname         VARCHAR(50),
  fk_pk_roomnumber INTEGER,
  last_seen        DATE,
  is_online        BOOLEAN
);


ALTER table client
  ADD CONSTRAINT pk_fk_roomnumber
    FOREIGN KEY fk_pk_roomnumber (fk_pk_roomnumber)
      REFERENCES room (pk_roomNumber);

我正在找131房间里的每一个客户。

我目前正在尝试使用此查询

select hostname, pk_macaddr, ipadress, roomdescription, fk_pk_roomnumber from client,room
WHERE fk_pk_roomnumber AND fk_pk_roomnumber = '131';

我的数据库中有160个客户端，我将它们分成4个房间，每个房间40个客户端，但当我开始查询时，我得到了所有160个客户端。我的查询哪里错了？

你好，卢卡斯

Answer 1

您有一个错误的联接条件..(在您的代码中，您没有连接这两个表)

select c.hostname, c.pk_macaddr, c.ipadress, r.roomdescription, r.fk_pk_roomnumber 
from client c
INNER JOIN room r ON c.fk_pk_roomnumber =  r.pk_roomnumber
  and r.pk_roomnumber  = '131';

并且你应该使用explicti join sintax (而不是ol和explicti隐式join sintax基于where)

Answer 2

你想“加入”客户和房间表：

select client.hostname, client.pk_macaddr, client.ipadress, room.roomdescription, client.fk_pk_roomnumber 
from client
inner join room
on client.fk_pk_roomnumber = room.pk_roomnumber
where room.pk_roomnumber = '131';