如何在第二个表的联盟中内部加入第一个表（某些条件的结果） [英] How Do I Inner Join With First Table(Result Of Some Conditions ) In A Union With Second Table

问题描述

我有两个表FoodList和AlternateFoodList。我想要预约的所有食物和替代食物



预约

AID	日期
1	1-1-2013
2	2-1-2014

FoodLsit

FoodAptID	AID	FoodID	QTY
1	1	1	1
2	1	2	2

AlternateFoodList

FoodAlternateID	FoodAptID	FoodID	QTY
1	1	3	1
2	1	5	2

requiredResult

FoodAlternateID	FoodA ptID	AID	FoodID	QTY	A_F
	1	1	1	1	F
1	1	1	3	1	A
2	1	1	5	1	A

i尝试这样的查询

i have two table FoodList and AlternateFoodList . I want All Food and Alternate Food For a person in an appointment

Appointment

AID	Date
1	1-1-2013
2	2-1-2014

FoodLsit

FoodAptID	AID	FoodID	QTY
1	1	1	1
2	1	2	2

AlternateFoodList

FoodAlternateID	FoodAptID	FoodID	QTY
1	1	3	1
2	1	5	2

requiredResult is

FoodAlternateID	FoodAptID	AID	FoodID	QTY	A_F
	1	1	1	1	F
1	1	1	3	1	A
2	1	1	5	1	A

i tried query like this

 select null as 'FoodAlternateID',P. *,"F" as A_F from  FoodAptID P inner join Appointment A on A.AID=P.AID where A.Date ='1-1-2013'
union
(some query where i able to use A.Date ='1-1-2013' in sub table )

PLZ帮助

PLZ help

推荐答案

很抱歉，但你的问题是不清楚还是。



看看例子：

Sorry, but your question is not clear still.

Have a look at example:

DECLARE @Appointment TABLE (AID INT IDENTITY(1,1),  [Date] DATETIME)
INSERT INTO @Appointment ([Date])
VALUES ('2013-01-01'), ('2014-01-02')

DECLARE @FoodLsit TABLE (FoodAptID INT IDENTITY(1,1), AID INT, FoodID INT, QTY INT)
INSERT INTO @FoodLsit (AID, FoodID, QTY)
VALUES(1, 1, 1), (1, 2, 2)

DECLARE @AlternateFoodList TABLE (FoodAlternateID   INT IDENTITY(1,1), FoodAptID INT, FoodID INT, QTY INT)
INSERT INTO @AlternateFoodList (FoodAptID, FoodID, QTY)
VALUES(1, 3, 1), (1, 5, 2)


SELECT AFL.FoodAlternateID , FL.FoodAptID, AFL.FoodID, FL.QTY , A.AID, A.Date
FROM @AlternateFoodList AS AFL INNER JOIN @FoodLsit AS FL ON AFL.FoodAptID = FL.FoodAptID INNER JOIN @Appointment AS A ON FL.AID = A.AID
WHERE A.Date = '2013-01-01'

有关JOIN的更多信息，请参阅： SQL连接的可视化表示 [ ^ ]

这篇关于如何在第二个表的联盟中内部加入第一个表（某些条件的结果）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！