Results 1 to 3 of 3

Thread: Stuck

  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Unanswered: Stuck

    I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.

    Code:
    Select 	a.company
    From 	dbo.contact1 as a, 
         	(
         		Select DISTINCT accountno 
         		From dbo.contsupp
         		Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
         	) as b
    Where 	a.accountno = b.accountno
    Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.

    Code:
     ------------------------
    |dbo.contact1            |
    |------------------------|
    |accountno  |  company   |
    |------------------------|
    |5123       | Alpha      | 
    |4158       | Beta       |
    |2121       | Gamma      |
    |5555       | Omega      |
     ------------------------
    
     --------------------------------------------
    |dbo.contsupp                                |
    |--------------------------------------------|
    |accountno  |  contact        |  contsupref  |
    |--------------------------------------------|
    |5123       |Product Inventory|device 01     |
    |2121       |product note     |the note      |
    |2121       |Product Inventory|device 02     |
    |5123       |Email Address    |a@b.com       |
    |4158       |ladkafndkaldkfna |device stopped|
     --------------------------------------------
    **note: i am using MSSQL 2000

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    How about something like this (untested):
    Code:
    Select 	a.company, c.EMAILAddr
    From 	dbo.contact1 as a 
    inner join
         	(
         		Select DISTINCT accountno DeviceAccountno
         		From dbo.contsupp
         		Where  contact LIKE 'Product Inventory' 
                    AND     contsupref LIKE '%device%'
         	) as b 
    on a.accountno = b.DeviceAccountno
    left outer join
         	(
         		Select DISTINCT accountno EMAILAccountno, 
                              contsupref EMAILAddr
         		From dbo.contsupp
         		Where  rtrim(contact) = 'Email Address' 
                    AND     contsupref LIKE '%@%'
         	) as c
    on b.DeviceAccountno = c.EMAILAccountno
    It's essentially inline views (or at least that's how I've always thought of sub-queries). If there can be multiple EMAIL addresses, then you might want to use "TOP 1" in a subordinate inline view to discard the extra ones.

    That's crazy that the table contsupp contains such a disparity of data, without even a "type" column, and zero to many itterations.
    Last edited by vich; 03-07-08 at 17:03.

  3. #3
    Join Date
    Jun 2007
    Posts
    12
    Thank you so much for your fast response. I tested it out this morning and it works perfectly.

    Yeah it is crazy, everything gets thrown into contsupp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •