问题描述:

I'm having a bit of an issue regarding multi-querying with PHP. My database has the following tables, with their schema shown below:

First table - Users

(PK) UserID,

UserFullName

UserID FullName

1000 Arthur Whitney

2000 Alex Schwartz

3000 Eva Kilpatrick

Second table - Invites

(PK) RowID,

InvName, InvSender, InvSenderTotal, InvReceiver, InvReceiverTotal, InvStatus

RowID InvName InvSenderID InvSenderTotal InvReceiverID InvReceiverTotal InvStatus

1 Fair 1000 10 2000 10 Sent

2 Party 2000 45 1000 45 Sent

3 Cinema 2000 12 1000 12 Sent

4 Vacation 3000 15 1000 5 Expired

Say for example when user Alex Schwartz, with unique ID 2000, is logged in, I would like to have it that he can see the name of the invite, the invite sender's name (if he is the invite receiver), the invite receiver's name (if he is the invite sender) and both the invite sender and receiver's totals.

I have devised the following two queries, which yield the results that I intended:

QUERY1

select i.InvName, i.InvSenderTotal, i.InvReceiverTotal, u.FullName FROM Invites as i INNER JOIN Users as u ON u.UserID = i.InvReceiverID

WHERE i.InvStatus = 'Sent' AND i.InvSenderID = 2000

This shows me the Invite Name, Invite Sender and Receivers' totals and the Invite Receiver's name when Alex Schwartz was sending the invite. Expected output here would be specific details of Row IDs 2 and 3.

The following query shows the inverse:

QUERY2

select i.InvName, i.InvSenderTotal, i.InvReceiverTotal, u.FullName FROM Invites as i INNER JOIN Users as u ON u.UserID = i.InvSenderID

WHERE i.InvStatus = 'Sent' AND i.InvReceiverID = 2000

So this enables Alex Schwartz to see the name of the person who sent the invite, along with the same data. Expected output here would be specific details of Row ID 1.

Hence, I am able to get the data I want, but I require two different queries in order to get it. This has presented problems in PHP whereby only one query seems to carry out. So my question is essentially, does anyone know how I could combine these two queries into one? I am also aware of multiQuerying and have tried to implement this, but have not had much success with that either.

Thanks in advance of your help.

网友答案:

Why not use UNION.

select i.InvName, i.InvSenderTotal, i.InvReceiverTotal, u.FullName FROM Invites as i INNER JOIN Users as u ON u.UserID = i.InvReceiverID 
WHERE i.InvStatus = 'Sent' AND i.InvSenderID = 2000
UNION
select i.InvName, i.InvSenderTotal, i.InvReceiverTotal, u.FullName FROM Invites as i INNER JOIN Users as u ON u.UserID = i.InvSenderID 
WHERE i.InvStatus = 'Sent' AND i.InvReceiverID = 2000
网友答案:

As a note, you can do this without union all (which is preferred over union unless you intentionally need to remove duplicates):

select i.InvName, i.InvSenderTotal, i.InvReceiverTotal, u.FullName
FROM Invites i INNER JOIN
     Users u
     ON u.UserID IN (i.InvReceiverID, i.InvSenderID)
WHERE i.InvStatus = 'Sent' AND i.InvSenderID = 2000;
相关阅读:
Top