如何在多个表上使用选择查询 [英] how to use select query on multiple table

问题描述

我有三个表
用户(用户名，名字，姓氏，电子邮件地址，密码)
个人资料(个人资料ID，用户ID，p_permission，profileImage)
常规(General_ID，Profile_ID，性别，城市，国家/地区，宗教，关系状态About_me)
现在我想要在这些表中基于First_Name，Last_Name，Gender等...的搜索条件(Select语句).
请帮助这是我的最后一个项目.谢谢

我有此查询，但有一些问题，请更正

i have the three tables
users(User_ID,First_Name, Last_Name,Email_address,Password)
profile(Profile_ID, User_ID, p_permission,profileImage)
General(General_ID, Profile_ID, Gender, City, Country, Religion, Relation_Status About_me)
now i want a search criteria (Select statement)within these tables based on First_Name, Last_Name, Gender,.....)
Please help this is my final project. Thanks

i have this query but it have some problems, please correct it

SELECT *
FROM general INNER JOIN profile ON general.Profile_ID = profile.Profile_ID RIGHT OUTER JOIN users ON profile.User_ID = users.User_ID
WHERE users.First_Name LIKE %var1% AND users.Last_Name LIKE %var2% AND `general`.City LIKE %var3% AND `general`.Country = var4 AND `general`.Gender = var5 AND `general`.Religion = var6 AND `general`.Relation_Status = var7

我想问的是，当我在页面中使用此查询时，仅提供一个字段(即性别)就什么也不返回.我想知道是否仅向sql语句提供性别变量，然后使用此性别变量返回所有结果.但是此查询现在无法正常工作，并返回空数组.
如果我将美国"国家/地区和所有其他字段都留空，则将与那些在总表的国家/地区中拥有美国的人一起返回.谢谢.请帮助我，我只有两天的时间来提交我的最终项目.

I want to ask that when i use this query in the page, it return nothing with only one fields provided i.e, gender. i want to know if i only provide gender variable to sql statement then its return all the result with this gender variable. but this query doesnt work now and return empty array.
if i provide Country ''USA'' and all other fields with empty, it would return with those who have USA in countary colum of general table. thanks. please help me i have only two days to submit my final project.

推荐答案

如果您将表名GENERAL放在引号中，则很可能会给您一个错误，但这仍然可以成功，具体取决于数据库供应商.

[让它又名又可理解]

选择*
从常规的INNER JOIN配置文件打开general.Profile_ID = Profile.Profile_ID
RIGHT OUTER JOIN用户打开配置文件.User_ID= users.User_ID
WHERE users.First_Name LIKE％var1％
AND users.Last_Name LIKE％var2％
AND`general`.City Like％var3％
AND`general`.Country = var4
AND`general`.Gender = var5
AND`general`.Religion = var6
AND`general`.Relation_Status = var7

所有变量测试都必须成功.

并且在GENERAL和PROFILE中必须有相同的Profile_ID-在吗?

与select *相比，从表格中选择感兴趣的特定列更有可能更清楚地显示什么是什么，什么是无效的.

It should most likely give you an error if you have the table name GENERAL in quotation marks, but this can still succeed depending on the database vendor.

[make it pretty aka intellible]

SELECT *
FROM general INNER JOIN profile ON general.Profile_ID = Profile.Profile_ID
RIGHT OUTER JOIN users ON profile.User_ID = users.User_ID
WHERE users.First_Name LIKE %var1%
AND users.Last_Name LIKE %var2%
AND `general`.City LIKE %var3%
AND `general`.Country = var4
AND `general`.Gender = var5
AND `general`.Religion = var6
AND `general`.Relation_Status = var7

all variable tests must succeed.

and there has to be the same Profile_ID in both GENERAL and PROFILE - is there ?

picking specific columns of interest from the tables as versus select * is more likely to show more clearly what is and what ain''t working.

请仅按"answer"进行发布一个答案，否则，请编辑您的帖子.

您已决定在提交提交前两天开始学习SQL?换句话说，在这一点上，如果您是否通过，则完全取决于随机陌生人的好意，因为您对SQL的理解不足以至于无法通过思考，因​​此您没有书本，等等?这听起来对您来说是一个合理的计划吗?听起来您应该通过吗?

您的选择是使用OR(显然会扩大搜索范围)，或执行类似的操作:

Please only push ''answer'' to post an answer, otherwise, edit your post.

You have decided two days before submission to start learning SQL ? In other words, at this point, if you pass or not depends entirely on the kindness of random strangers, because you do not understand SQL enough to think this through, you have no books, etc ? Does that sound like a rational plan to you ? Does it sound like you deserve to pass ?

Your options are to use OR, which obviously widens the search, or to do something like this:

WHERE (users.First_Name LIKE %var1% or var1 = '') AND

然后，它拒绝考虑以空方式传递的任何变量.话虽如此，LIKE %%仍应返回所有记录.您确定要从整个字符串而不是从头开始搜索子字符串吗?如果您想进行广泛的搜索，则可能还是要使用
.

哦，您的问题变量需要完全匹配.然后，是的，您需要执行OR技巧以忽略为空的值.

which then rejects for consideration any variables that are passed in empty. Having said that, LIKE %% should return all records anyway. Are you sure you want to search the substring through the entire string, not from the start ? If you want that expansive a search, you probably want OR anyhow.

Oh, your problem variables require a complete match. Then yes, you need to do the OR trick to ignore values that are empty.

我想问的是，当我在页面中使用此查询时，它只返回一个字段就什么也不返回即性别.我想知道是否仅向sql语句提供性别变量，然后使用该性别变量返回所有结果.但是此查询现在无法正常工作，并返回空数组.
如果我将美国"国家/地区和所有其他字段提供为空，则将与那些在总表的国家/地区中拥有美国的人一起返回.谢谢.请帮助我，我只有两天的时间来提交我的最终项目.

I want to ask that when i use this query in the page, it return nothing with only one fields provided i.e, gender. i want to know if i only provide gender variable to sql statement then its return all the result with this gender variable. but this query doesnt work now and return empty array.
if i provide Country ''USA'' and all other fields with empty, it would return with those who have USA in countary colum of general table. thanks. please help me i have only two days to submit my final project.

这篇关于如何在多个表上使用选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！