How to write a select to retrieve the tbl B Branch and Email ? 1

[GarHeard]

If I have:

table tblA with a field:
------------------------------------------------
[Account Number] defined as nvarchar 10

table B with 2 fields:
-----------------------------------------
Branch defined as nvarchar 3
Email defined as ntext 16

sample of records
--------------------
tbl A

[Account Number]
---------------------
001 123456
001 234567
001 245466
003 345633
003 465454


tbl B

Branch Email
--------- ---------
001 joe.smith@verizon.com
002 jane.doe@verizon.com
003 jeff.smith@verizon.com

How would you write a select to retrieve the tbl B Branch and Email values only if they exist on tbl A ?

Left([Account Number],3)= tblB.Branch

001 joe.smith@verizon.com
003 jeff.smith@verizon.com

Since there is no branch 002 among the [Account Number] values, it is bypassed.
Also, I only want to write out 1 record per branch even though there are mulitple occurrences of a "branch" (the first 3 positions of the [Account Number] in tbl A.

 

[PHV]

SELECT DISTINCT tblB.Branch, tblB.Email
FROM tblA INNER JOIN tblB ON Left(tblA.[Account Number],3)= tblB.Branch

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarHeard]

I tried:

SELECT DISTINCT tblB.Branch, tblB.Email
FROM tblA INNER JOIN tblB ON Left(tblA.[Account Number],3)= tblB.Branch

The field Email is defined as ntext. Do you suppose this is causing the following error ?

Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

If I strip out the field Email, the SELECT clause works fine. Is there a way to cast the Email field to eliminate the error ?



By the way:

I am using Access as the front end and SQL Server as the back end database. I am working with an ADP file.

thanks for all your help.

 

[PHV]

And this ?
SELECT tblB.Branch, First(tblB.Email) As theEmail
FROM tblA INNER JOIN tblB ON Left(tblA.[Account Number],3)= tblB.Branch
GROUP BY tblB.Branch;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarHeard]

Server: Msg 195, Level 15, State 10, Line 1
'First' is not a recognized function name.

The SQL Server seems to take exception with First as a function name.

 

[PHV]

And this ?
SELECT tblB.Branch, tblB.Email
FROM tblB
WHERE EXISTS (SELECT * FROM tblA WHERE Left(tblA.[Account Number],3)=tblB.Branch)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarHeard]

thanks for your perserverance PH.

SELECT tblB.Branch, tblB.Email
FROM tblB
WHERE EXISTS (SELECT * FROM tblA WHERE Left(tblA.[Account Number],3)=tblB.Branch

this worked perfectly!!!!!!!!!!!!!!!!!!!!!