Multiple Results into one field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Results into one field

Author

Topic

stuaz
Starting Member

16 Posts

Posted - 2011-08-04 : 12:26:01

Hi, I have the following query:

select contact1.accountno, contact1.company, contact1.zip, contact1.source,contact1.key2, contact2.urepname, contact2.uregion, contact2.umlastupd, contact2.umstatus,  contact2.umallocate, contact2.umaddedby, contact2.umcamp, contact2.umsource, contact2.ummonth, contact2.umgp,
month(umlastupd) as 'MonthLastUpd',

from CONTACT1
INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO

where umlastupd is not null
order by ummonth

Results looks like:

B0100124108H%(N0?Mik	Example	BS10 5PY	VALE ACCOUNT	504	NAME *AREA*	15	2011-03-22 00:00:00.000	New	NULL	MIS	NULL	Kyocera	NULL	NULL	3
A6022258598)OM>U8Mrs	Exxamp;le Ltd.	CV21 2UU  	Dunn & Bradstreet	524	NAME *AREA*	15	2011-07-25 00:00:00.000	NULL	JCHAPMA	NAME	GCL Leads	Telemarketing	NULL	NULL	7

What I also want to do is return from another file (CONTHIST) the DATE of a matching record.

So the other file contains the same Account No's as above, in a format like below:

AccountNo Ondate Ref

What I want to do is select the ONDATE for a Record in there where the REF = 'MARKETING'.

So in theory my first query should produce a field with a DATE in it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 12:39:21

you need to add the third table also in a inner join on conditions

AccountNo = AccountNo
AND Ref='Marketing'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-04 : 15:14:06

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

You also need to read a basic book on RDBMS; rows are not records. Tables model sets so the names are plural or collective, not singular. And since they are set6s, we do not number them like elements of an array. Most of your DML problems will disappear when you learn how to write correct DDL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

stuaz
Starting Member

16 Posts

Posted - 2011-08-05 : 05:18:09

Thanks for your help Visakh16, helpful replies are always good...as opposed to random copy and pastes...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:25:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources