More not in issue

30 Sep 2005 2:16 PM

qjlee

Hi,

I have three tables, one is the client information table (T1) contain an
uniqueid for the client, the other is client address table (T2), which
contains different type of address for the client (e.g. business address,
home address) and the different type of address is identified by addtype_c,
and it is link to T1 via clientid. The third table (T3) contains the media
request from the clients and the address (add_c) they want the media sent to.
and of course, it has the fields clientid.

I need to generate the mailing lable for the client according to the address
that the client want the materials sent to. However, how can I create a
query to alert me that the address type that the clients want the materials
sent to is not in the address table.

Thanks in advance.

30 Sep 2005 3:34 PM

Jerry Spivey

One option change your business logic to require a ship-to address when the
client is filling in this information. Or add a WHERE clause to your query
to exclude NULL addresses or if you use a DEFAULT to exclude addresses = to
the DEFAULT.

HTH

Jerry
Show quote

"qjlee" <qj***@discussions.microsoft.com> wrote in message
news:421420FC-090A-499F-9715-32A63D9A2930@microsoft.com...
> Hi,
>
> I have three tables, one is the client information table (T1) contain an
> uniqueid for the client, the other is client address table (T2), which
> contains different type of address for the client (e.g. business address,
> home address) and the different type of address is identified by
> addtype_c,
> and it is link to T1 via clientid. The third table (T3) contains the
> media
> request from the clients and the address (add_c) they want the media sent
> to.
> and of course, it has the fields clientid.
>
> I need to generate the mailing lable for the client according to the
> address
> that the client want the materials sent to. However, how can I create a
> query to alert me that the address type that the clients want the
> materials
> sent to is not in the address table.
>
> Thanks in advance.
>
>

30 Sep 2005 9:07 PM

Hugo Kornelis

On Fri, 30 Sep 2005 07:16:04 -0700, qjlee wrote:

Show quote

>Hi,
>
>I have three tables, one is the client information table (T1) contain an
>uniqueid for the client, the other is client address table (T2), which
>contains different type of address for the client (e.g. business address,
>home address) and the different type of address is identified by addtype_c,
>and it is link to T1 via clientid. The third table (T3) contains the media
>request from the clients and the address (add_c) they want the media sent to.
>and of course, it has the fields clientid.
>
>I need to generate the mailing lable for the client according to the address
>that the client want the materials sent to. However, how can I create a
>query to alert me that the address type that the clients want the materials
>sent to is not in the address table.
>
>Thanks in advance.
>

Hi qjlee,

Since you didn't post DDL and sample data (www.aspfaq.com/5006), I'll
have to make some wild assumptions about your tables.

SELECT    T3.ClientID, T3.add_c
FROM      T3
WHERE NOT EXISTS
(SELECT *
FROM    T2
WHERE   T2.ClientID    = T3.ClientID
AND     T2.AddressType = T3.AddressType)

or

SELECT    T3.ClientID, T3.add_c
FROM      T3
LEFT JOIN T2
     ON   T2.ClientID    = T3.ClientID
     AND T2.AddressType = T3.AddressType
WHERE     T2.ClientID IS NULL

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)