How to join two tables when the ON clause is variable

Question

To simplify the description, I'll use a member table and a mail table. The mail table has the following structure:

-------------------------------
FromMember  int  NOT NULL (FK)
ToMember    int  NOT NULL (FK)
...
-------------------------------

I want to write a query which will return all the mail for a particular member whether he is the FromMember or the ToMember. That's easy just use an OR clause in the WHERE clause.

But where it gets complicated is that I want to join the mail table to the member table to retrieve the member info for the other member. So if I'm the FromMember than the join clause to the member table would use the ToMember field and if I'm the ToMember than the join clause would use the FromMember field. Basically, the ON clause needs to join with two different fields based on a specific condition. Is that possible?

My solution so far is to use UNION ALL as follows:

SELECT * FROM mail INNER JOIN member ON mail.ToMember = member.MemberID
WHERE mail.FromMember = @me
UNION ALL
SELECT * FROM mail INNER JOIN member ON mail.FromMember = member.MemberID
WHERE mail.ToMember = @me

However, I would like to put this query in a view because the resultset will be used in many places in my code. That means that I would need a parametrized view which I think I need to implement using a User-Defined Table Function.

Basically, I'm looking for two answers:
Is there a better way to write that query instead of UNION ALL?
And should I put that query in a VIEW or a UDF?

If VIEW is the preferred method, how can I pass the @me parameter to the VIEW. I cannot move the @me param to the WHERE clause has some people have said in their answers because the WHERE clause depends on the ON clause. Pulling out the WHERE clause and then ORing them will not return the same resultset as the above UNION ALL query.

**just a note the people suggesting an "OR" in their solution: you will not use any index and you will scan the entire table.** this could become painful depending on the numer of rows and/or the number of times this query is run. using a `UNION` will allow an index hit on both `FromMember` and on `ToMember` (provided that they exist). in my experience, using the index will be tremendously faster than a table scan in most cases. — KM., Mar 27 '12 at 17:18
@KM: does the above "OR" statement apply to an OR clause in the INNER JOIN ON clause as well? as specified in the answer below by @quzary? — Cristina, Mar 27 '12 at 17:31
it doesn't matter. think of it this way you have a large phone book (thousands of pages) & you want to find a particular name. You just look up the name, & you have it quick. now, say you want to find a particular name in the "name" or the "address" fields. how do you do that? look at every entry, which is a table scan. Now say you have two phone books, one sorted by name and one sorted by address. You could do the union approach: do one quick search in the "name" sorted book. you append those results with the results from a quick search of the "address" sorted book. — KM., Mar 27 '12 at 17:41
remember the goal with SQL is fast results, aka index usage. you often have to write repetitive code (think copy paste). If you optimize your SQL for efficient keystrokes, you usually loose execution speed. — KM., Mar 27 '12 at 17:45
@KM: so my UNION ALL query is better for performance reasons, correct? If so, how can I put it in a view and still pass the "@me" param? — Cristina, Mar 27 '12 at 17:58
I'd try a table value function passing in the @me parameter and using the `UNION ALL` just like you have it. In all reality, I'd just code the `UNION ALL` in a derived table or CTE everywhere I need it. — KM., Mar 27 '12 at 18:09

score 3 · Accepted Answer · answered Mar 27 '12 at 15:04

3

1.

SELECT * 
 FROM mail 
  INNER JOIN member 
   ON (mail.FromMember = @me and mail.ToMember = member.MemberID)
    or (mail.ToMember = @me and mail.FromMember = member.MemberID)

Usually views are faster then UDFs.

answered Mar 27 '12 at 15:04

quzary

285
1
4

Duh, I basically did that by making my OR in the where, and did not even see the simplification. +1 – Justin Pihony Mar 27 '12 at 15:08
How would I send the @me parameter to a View? – Cristina Mar 27 '12 at 15:27
You don't need a parameter. Filter your view with a where. – JotaBe Mar 27 '12 at 15:50
The parameter needs to be used in the ON clause. I can't move the @me parameter from the above query to the WHERE clause can I? – Cristina Mar 27 '12 at 16:53
For sure you can but that'll make you query little more complicated: SELECT mail.* , member.*, member2.memberId me FROM mail INNER JOIN member on mail.ToMember = member.MemberID or mail.FromMember = member.MemberID INNER JOIN member member2 on mail.ToMember = member2.MemberID or mail.FromMember = member2.MemberID ON (mail.FromMember = member2.MemberId and mail.ToMember = member.MemberID) or (mail.ToMember = member2.MemberId and mail.FromMember = member.MemberID) Now you can use it as a view with "where myview.me=@me" – quzary Mar 28 '12 at 07:58

Justin Pihony · Answer 2 · 2012-03-27T15:05:41.270

1

You need to flip your query and do something like this:

SELECT member.* 
FROM member
    LEFT JOIN mail AS ToMail 
        ON ToMail.ToMember = member.MemberID AND ToMail.ToMember = @me
    LEFT JOIN mail AS FromMail 
        ON FromMail.FromMember = member.MemberID AND FromMail.ToMember = @me
WHERE ToMail.ToMember IS NOT NULL OR FromMail.FromMember IS NOT NULL

edited Mar 27 '12 at 15:05

answered Mar 27 '12 at 15:00

Justin Pihony

66,056
18
147
180

Jeremy Pridemore · Answer 3 · 2012-03-28T15:14:28.407

1

I don't believe that T-SQL does parameterized views. That functionality is handled via inline table functions. Here is a function that should handle your problem:

CREATE FUNCTION fExample_GetRelatedMailMembersForMember
(
    @MemberId INT
)
RETURNS TABLE
AS
RETURN
SELECT
    * -- Replace this with explicit columns
FROM mail M
INNER JOIN member MBR
    ON MBR.MemberID IN (M.FromMember, M.ToMember)
    AND MBR.MemberID <> @MemberId
WHERE M.MemberId = @MemberId

edited Mar 28 '12 at 15:14

answered Mar 27 '12 at 15:44

Jeremy Pridemore

1,995
1
14
24

the mail table does not include the field MemberID, it only contains the FromMember and ToMember fields. Therefore, I cannot have the WHERE clause as you have defined it. Am I missing something in your solution? – Cristina Mar 27 '12 at 17:30
@Cristina I know you already accepted an answer, but I went ahead and fixed mine for history sake. Because you were trying to join to `member` where it wasn't the member id you sent in, I just needed to change it to @MemberId. Glad you found a solution that worked for you. :) – Jeremy Pridemore Mar 28 '12 at 15:16
shouldn't the WHERE clause read: WHERE m.FromMember = "@me" OR m.ToMember = "@me". Just want to make sure I get this example for my own knowledge base! – Cristina Mar 28 '12 at 16:53

score 0 · Answer 4 · answered Mar 27 '12 at 16:06

Your UNION ALL query is fine. Put it in a view almost as is;

CREATE VIEW myView AS
SELECT * FROM mail INNER JOIN member ON mail.ToMember = member.MemberID
UNION ALL 
SELECT * FROM mail INNER JOIN member ON mail.FromMember = member.MemberID

Then, as stated by @JotaBe above, query the view using a WHERE clause

SELECT * FROM myView WHERE ToMember = @me OR FromMember = @me

You could also join views together in a query to create a chain if you wanted, to see mail messages that have been passed from one member to another.

How to join two tables when the ON clause is variable

4 Answers4