Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 'Combining' fields

    Not concatenation, more... err.. I don't know what you'd call it.

    Code:
    SELECT
    	DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
    	[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
    	[C01241 Internal Data].[Col15] AS InternalDataEmail
    FROM [C01241 Opened]
    LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
    LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
    (Apologies for the table/col names, this is all very temporary)

    So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.

    At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.

    What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.

    Does that make sense?

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    look into union if I understand your question corretly this should work

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    I might not be understanding UNION correctly, but if I do, then I should have added that the two 'original data' tables have absolutely no similarities in structure. Does this make a difference?

    Edit:
    (sorry, that was a stupid thing to say and I've just realised why Thanks )
    Last edited by Spudhead; 07-12-06 at 12:16.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the Coalesce function:
    Code:
    SELECT	DISTINCT
    	[C01241 Opened].[Col004] AS OpenerEmail,
    	Coalesce([C01241 External Data].[DMCEMAIL], [C01241 Internal Data].[Col15]) AS DataEmail
    FROM	[C01241 Opened]
    	LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
    	LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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