在mysql中加入三个表 [英] Join three table in mysql

问题描述

我有三张桌子如下



-------

桌名人物

----------

PersonId

HomeId

FirstName

电邮

性别

密码



=================== =================

表名称NoticeBoard

-------------- -----------

NoticeBoardId

NoticeTitle

通知

CreationDate
PersonId



============================ =========

表名首页

-------

HomeId

HomeNumber

HomeName





我想搜索sql查询...

如... ..

如果我想找人名，Person HomeNumber和NoticeTitle那么如何查找以下查询是行不通的..

I have three table as follow

-------
table name Person
----------
PersonId
HomeId
FirstName
Email
Gender
Password

====================================
table name NoticeBoard
-------------------------
NoticeBoardId
NoticeTitle
Notice
CreationDate
PersonId

=====================================
table name Home
-------
HomeId
HomeNumber
HomeName


I want to make search sql query...
as like..
If I want to find person name, Person HomeNumber and NoticeTitle so how to find following query can not work..

ALTER PROCEDURE dbo.sp_Custom_Select_Complain
(
	@PersonId int = NULL,
	@FirstName varchar(50) = NULL,
	@Gender varchar(50) = NULL,

	@ComplainId int = NULL,
	@ComplainTitle varchar(100) = NULL,
	@ComplainInfo varchar(400) = NULL,
	
	@HomeId int = NULL,
	@HomeNumber int = Null,
	@HomeName varchar(50) = Null

)
AS
select 
	P.FirstName,
	P.Gender,

	C.ComplainTitle,
	C.ComplainInfo,

	H.HomeNumber,
	H.HomeName

From Person P 
	Inner Join Complain C On C.PostId = P.PostId
	Inner Join Home H On H.HomeId = P.HomeId

Where 
	(@PersonId Is Null Or P.PersonId = @PersonId)
AND	(@FirstName Is Null Or P.FirstName LIKE '%' + @FirstName + '%')
AND	(@Gender Is Null Or P.Gender = @Gender) 

AND	(@ComplainTitle Is Null Or C.ComplainTitle LIKE '%' + @ComplainTitle + '%') 
AND	(@ComplainInfo Is Null Or C.ComplainInfo LIKE '%' + @ComplainInfo + '%') 

	RETURN

------------------- ---------------------------

请帮帮我......

----------------------------------------------
please help me...

推荐答案

尝试将连接更改为左外连接

Try changing your joins to left outer joins

你检查空值传递给整数值。



示例：



int？ PersonId = Null



或



int？ PersonId = 1

Hi,

did u check null value passing to integer value.

Example :

int? PersonId = Null

OR

int? PersonId = 1

这篇关于在mysql中加入三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！