php - MS SQL php查询逗号分隔列？

Question

我对 MS SQL 查询完全陌生，我所拥有的三个表设置如下:

用户表的一列 (userGroupLocID) 设置为 varchar 数据类型，该字段包含以逗号分隔的值，例如 (1,2)

Room_Location 表的列 (user_loc_id) 设置为 INT 数据类型

Client_Room 表的列 (id) 设置为数据类型 INT

我有以下有效的查询 $query = "SELECT * FROM client_room, room_location, users WHERE $useractiveid = (users.id) AND client_room.id = (room_location.user_loc_id)";

这会输出 Room_Location 表中的位置的完整列表，这没问题，但我只想显示用户表 (userGroupLocID) 中存在 id 的位置 $query = "SELECT * FROM client_room, room_location，用户 WHERE $useractiveid = (users.id) AND client_room.id = (room_location.user_loc_id) AND cast(client_room.id as varchar(10)) IN (users.userGroupLocID)";

有人可以告诉我哪里出了问题吗？

Answer 1

我最终设法解决了我自己的问题，这可能不是最好的答案，但这是它在 MS SQl 上工作的唯一方法。

<select name="event_room" id="event_room">
                        <option>Room Location</option>
                        <?php 

                            $query = "SELECT * FROM users WHERE theusername = '$userident'";

                            $result = odbc_exec($connect, $query);

                            while($row = odbc_fetch_array($result)){

                                $usegrids = explode(',', $userperenco);
                                foreach ($usegrids as $usegrid) {
                                    $querygroup = "SELECT * FROM room_location WHERE user_loc_id = '$usegrid'";
                                    $resultgroup = odbc_exec($connect, $querygroup);
                                    while($rowgroup = odbc_fetch_array($resultgroup)){
                                        echo "<option value=\"" . $rowgroup['location'] . "\">" . $rowgroup['location'] . "</option>\n";
                                    }
                                }
                            }
                        ?>
                    </select>