如果列不匹配，则从多个表中选择连接返回 null [英] select from multipe tables join return null if columns not match

问题描述

我正在尝试进行 mysql 查询，指定 userid = x 的列和 userid = y 的其他列.

I'm trying to make a mysql query specifying columns where userid = x and others where userid = y.

我的问题是(当前正在发生的事情)如果来自用户 x 的选定列与来自用户 y 的同一列的内容不匹配，则查询将返回 null(因为我有一个声明变量和只打印来自 else 的那些变量......).

My problem is (what is currently happening) if the selected column from user x doesn't match the content from the same column from user y, the query will return null (as I have a check-if that declare variables and only those variables from else are being printed... ).

我需要从 3 个表中进行选择:member、friends 和 account_type:

I need to select from 3 tables: members, friends and account_type:

来自 members.status 的 1º(以及其他列，例如姓名、月份 1-12 和照片)，其中 members.id = $_GET['id']
2º from account_type.type 我需要比较 members.acc_type where members.id = $_SESSION['user_id'].
3º 来自friends.cashed_month1-12(和列总数)，其中friends.friendID = $_GET['id']

1º from members.status (and other columns such name, month1-12 and photo) where members.id = $_GET['id']
2º from account_type.type I need to compare members.acc_type where members.id = $_SESSION['user_id'].
3º from friends.cashed_month1-12 (and column total) where friends.friendID = $_GET['id']

如何指定每列属于哪个 id?

How can I specify to what id belongs each columns?

我不知道如何在一个查询中进行，我可以单独进行...

I don't know how to make that in one only query, I can make that separately...

实际上，这就是我所拥有的:

Actually, this is what I have:

SELECT friends.*, members.*, account_type.* FROM members INNER JOIN friends ON friends.friendID = members.id
                                                              INNER JOIN account_type ON account_type.type = members.acc_type
                                                              WHERE friends.friendID = ? AND members.acc_type = ?")) {
$stmt->bind_param('isi', $_GET['id'], $_SESSION['acc_type'];

从 2º 是我遇到这个问题的地方，我选择了一列，我真的需要从什么 id 或从哪一行指定，因为有许多帐户具有相同的 acc_type，因此如果连接了，则从什么 id 指定应该工作用户的 acc_type 与朋友帐户不同.我的意思是，这个想法是:每个用户，无论 acc_type 是什么，都可以拥有不同 acc_types 的朋友.

From the 2º is where I have this problem, I select a column and I really need to specify from what id or from what row, because there are many accounts with the same acc_type so specifying from what id should work if the connected user have a different acc_type from the friend account. I mean, the idea is: every user, no matter what acc_type is, can have friends with different acc_types.

我测试过，如果已连接用户和 $_GET['id'] 用户具有相同的 acc_type，则一切正常，但如果我更改已连接用户的 acc_type，查询将不起作用.

I tested, if connected user and $_GET['id'] user have the same acc_type, everything works fine, but if I change the acc_type from connected user, the query won't work.

提前致谢！

推荐答案

最后我得到了一个有效的查询(花了我几个小时)，这个答案可能对其他人有用，因为它对我有帮助:

Finally I got a query that works (it took me a couple of hours), this answer may serve others as it helped me:

SELECT members.*, account_type.*, friends.*, membersF.*
FROM members
INNER JOIN account_type ON members.acc_type = account_type.type
INNER JOIN friends ON friends.userID = members.id
INNER JOIN members AS membersF ON membersF.id = friends.friendID
WHERE members.acc_type = ?
AND friends.userID = ?
AND membersF.id = ?

$_SESSION['acc_type'], $_SESSION['user_id'], $_GET['id']

一些解释:

INNER JOIN account_type ON members.acc_type = account_type.type

这将返回已连接用户的 acc_type，您将能够打印与该 account_type 对应的列(您可以添加许多列，除了奖金).

This will return the acc_type for the connected user and you will be able to print the columns corresponding to that account_type (you can add many columns, apart from bonus).

INNER JOIN friends ON friends.userID = members.id

在这里，它将返回该表中与用户 ID 对应的列.

Here, it will return the columns in that table corresponding to the userID.

INNER JOIN members AS membersF ON membersF.id = friends.friendID

最后，这将返回与该 ID 对应的成员的列，在本例中为朋友 ID.

And finally, this will return the columns from members corresponding to that ID, in this case the friend ID.

就是这样:)

这篇关于如果列不匹配，则从多个表中选择连接返回 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！