How to JOIN this?

[tman24m]

I have a table with a clientID, employeeID and, a code. I want to join that to another table which will contain the clientID, the code and possibly the employeeID. If the employeeID does not exist, there will be a default record which will contain clientID and the code. The employeeID would be NULL

FROM #Temp tx
INNER JOIN letter l
tx.ClientID = l.ClientID
AND tx.Code = l.Code

CASE WHEN EXISTS(select id from lett where ClientID = @ClientID and Code = tx.Code and EmployeeID = tx.Employee) THEN
---AND tx.EmployeeID = l.EmployeeID---
END


That's what I'm looking for. Any thoughts? I don't want to use dynamic sql. Thanks

 

[amorous]

Why dont you first check the employeeID of the second table with respect to whatever ClientID and Code.

I mean

rs.Open "Select EmployeeID From table2 Where ClientID='"&clientID&"' AND Code='"&Code&"' "

Then you can use IF conditions like

If rs("EmployeeID")="" Then

Then write the query using join with out employeeid

Else

Write the query using join and also including employeeid

End if


Its just an idea. Hope that helps

VJ

 

[SQLBill]

So what do you want returned? You have the same records in two different tables (why?), with the exception that one table might have NULL for some records in one of the fields (employeeID). Why not just pull the data from one table and ignore the other?

-SQLBill

 

[tman24m]

Actually, I changed names to protect the innocent. So if the data doesn't make sense, just ignore that.

Essentially, the join is to a table that may contain a letter for = clientID, employeeID, and code.

However, if that join isn't created, then I need to have the join on clientID and code because there will be a default clientID letter with a null value in employeeID.

Table ClientID EmployeeID Code
#temp 1 7 PC
letter 1 7 PC

or

#temp 1 7 PC
letter 1 NULL PC


I only want it to return 1 record every time, so a left join is no good. However I am currently looking at adding a left join and using the COALESCE.

any better solutions would be welcomed
thanks

 

[SQLBill]

Sorry if I'm seeming dense here, but I still don't get it.

What is different between the two tables? The only thing I see is a possibility of a NULL for EmployeeID. In that case you only need the data from #temp table.

If there's additional columns you don't have to give us the real names/data, but we can't help unless we have a better idea of what you are really trying to accomplish. Otherwise, I can't see why you are trying to join the two tables when one has everything you need.

-SQLBill

 

[tman24m]

yeah,
the letter table contains some fields such as paragraph1, paragraph2, etc

 

[SQLBill]

So, is this what you really need....

Display everything from table Letter, but if EmployeeID is NULL use the EmployeeID from table #temp.

-SQLBill

 

[tman24m]

yes, I need to display everything from the letter table. Let me see if I can clarify this. This letter table has records in it which sometimes match up to an employee directly with clientID, employeeID, and Code. There can be multiple letters for an employee and multiple employees within a client, therefore the Code field is needed to select which type of letter to send. There are also fields in the #Temp table which are related at the client level and I need to display those as well. Does this make any more sense?

 

[SQLBill]

Basically, you want all fields from Letter unless they are NULL in which case you want the equivelant field from #temp.

That's a tough one.....I'll see what I can come up with. It can't be a straight OUTER JOIN as JOINs return data from both tables. It almost sounds like you need to use CASE. The big issue is making sure you get the correct data from #temp for the row that you are retrieving.

-SQLBill

 

[tman24m]

I ended up doing this

LEFT JOIN Letter l ON l.ClientID = @ClientID
AND e.employeeNumber = l.employeeNumber AND a.Code = l.Code
INNER JOIN letter l2 ON l2.ClientID = @ClientID
AND l2.employeeID Is Null AND l2.Code = a.Code

Then I used this in my select
, COALESCE(l.paragraph2, l2.paragraph2)


I'm in the process of testing now....it looks like it may work, not sure yet

 

[SQLBill]

Could try:

SELECT 
  ISNULL(field1, (SELECT field1 FROM #temp WHERE ClientID = @ClientID),
  ISNULL(field2, (SELECT field2 FROM #temp WHERE ClientID = @ClientID),
<rest of fields>
FROM Letter
WHERE ClientID = @ClientID

-SQLBill