OR in a JOIN - Database Discussion Boards

Table-Valued functions are awesome, like a view with input parameters. Like this:

CREATE FUNCTION [dbo].[Function_Name] (@inputParameter int) RETURNS table AS Return (<select clause> <from clause> WHERE <filterFieldName> = @inputParameter [<group by clause>])

Works in sql 2000+.

"Go forth into the source" - Neal Morse

Hi,

I want to send mails to the attendee of a particular event. So please tell me how can i send emails in c#.

Thanks..In advance Smile | :)

There's plenty of examples on the 'net showing how to send emails from C#.

How do I send mail using C#?[^]

Hope this helps getting you started Smile | :)

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Have a look at this tip: Sending an Email in C# with or without attachments: generic routine.[^]

BTW, this is forum is specific to database related issues/questions. Next time, please use correct forum - C# Forum for C# queries[^] or post in Q&A section[^].

Sandeep Mewara
[My last tip/trick]: Server side Delimiters in ASP.NET[^]

You can create a Window Service for same.

- Happy Coding -
Vishal Vashishta

A quick question about the mapping between CLR types and their TSQL ones.

From what I can see within the CLR you have SQLString and SQLChars which are nvarchar(4000) and nvarchar(max)...what if the callee expects to pass you a varchar(5)?

Can you tell the CLR the length it should expect? Or would you have to specifically code this check?

I wouldn't bother -- the engine will figure it out. And deal with an Exception if one is thrown.

I'm having some trouble with a JOIN that involves an OR (in SQL Server). For example:

SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field1=B.Field1
OR A.Field2=B.Field2

Quite correctly this produces two output rows for each row in TableA that matches TableB via both Field1 and Field2* -- but for this JOIN I want to output only one row when both match (preferably the result of the Field1 match, and only report the Field2 match if Field1 doesn't match).

* Clarification -- when it matches two rows in TableB; one via Field1 and the other via Field2.

I haven't done much searching for pointers because I don't think it's possible, however I'm posting here just in case someone here knows of a way or a simple (SQL only) work-around.

Edit -- Here's an example:

SELECT * FROM TableA

ID Field1 Field2
-- ------ ------
10 A      E
11 E      D
12 A      D

SELECT * FROM TableB

ID Field1 Field2
-- ------ ------
20 A      B
21 C      D

10 will match only 20 ; 11 will match only 21 ; 12 will match both 20 and 21 -- so I want 20.

ID Field1 Field2 ID Field1 Field2
10 A      E      20 A      B      <-- I want this row
11 E      D      21 C      D      <-- I want this row
12 A      D      20 A      B      <-- I want this row
12 A      D      21 C      D      <-- I don't want this row

Luc's and pmpdesign's suggestions yield the same output.

Here's a variation of Bernhard's suggestion, which seems to work:

WITH cte1 AS
(
SELECT A.ID aID
, A.Field1 aField1
, A.Field2 aField2
, B.ID bID
, B.Field1 bField1
, B.Field2 bField2
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
)
, cte2 AS
(
SELECT A.ID aID
, A.Field1 aField1
, A.Field2 aField2
, B.ID bID
, B.Field1 bField1
, B.Field2 bField2
FROM TableA A 
INNER JOIN TableB B
ON A.Field2=B.Field2
)
  SELECT *
  FROM cte1
UNION ALL
  SELECT C2.* 
  FROM cte2 C2
  LEFT OUTER JOIN cte1 C1
  ON C2.aID=C1.aID
  WHERE C1.aID IS NULL

modified 6-Jun-12 16:38pm.

Is it possible to try this through some stored procedure?

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

I don't know whether or not such a stored procedure could be devised, but if so, it could be executed.

Ah, I didn't know logic operators were allowed in the ON clause, but as they are, why not just exploit them to the fullest:

SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field1=B.Field1
OR (A.Field1<>B.Field1 AND A.Field2=B.Field2)

Maybe now is the time for a Java | [Coffee]

?

Luc Pattyn [My Articles] Nil Volentibus Arduum

Checked it out, and it seems to work Big Grin | :-D

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Thanks.

Luc Pattyn [My Articles] Nil Volentibus Arduum

The

hadn't worked. Neither did the tequila. Trying it now...

Edit:

No, that doesn't work -- because there are two different rows.

modified 6-Jun-12 0:40am.

Do you have a sample data set that you are using?

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

I'll cobble something up.

If it is possible, that would be cool so there can be a data set to test against and see what the expected results are Smile | :)

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

I added it to the post with some clarification.

Coolness! I will check it out soon and see if there's anything I can add to this thread in terms of a solution Smile | :)

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Without a dataset I can't be sure, but have you tried

SQL

SELECT DISTINCT * FROM...

No, that won't work -- the two resultant rows are distinct.

A complicated UNION:

SQL

SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
UNION
SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field2=B.Field2
WHERE A.ID NOT IN
(
SELECT ID
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
)

assuming that ID is the primary key of TableA.

Possibly, but no, no primary key.

Tried it, and it seems to work. Thumbs Up | :thumbsup:

It may take a while to run on the data though. Sigh | :sigh:

modified 6-Jun-12 11:54am.

To have the duplicates removed, I think a union would do that.

SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field1=B.Field1
UNION
SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field1<>B.Field1 AND A.Field2=B.Field2
;

Try that.

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

I think that's basically what Luc suggested -- and it doesn't work.