Is this complex query possible

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

SQL Server 2005 Forums

Transact-SQL (2005)

Is this complex query possible

Author

Topic

James0816
Starting Member

9 Posts

Posted - 2010-03-03 : 11:09:09

I've never attempted anything like this and not sure if it would even be possible so I figured to call in some more minds to ponder it.

I would like to create a query that pulls data from two tables. One of the fields will be made up of results from table 2. Here's the catch, table 2 can contain multiple records. Simple? There's more. Here's my scenario:

First the data:

Table1 / Afield, Bfield, Cfield
......./ 10001, Test Record 1, Hello
......./ 10002, Test Record 2, Hello
......./ 10003, Test Record 3, Hello

Table2 / Afield, Bfield, Cfield
......./ 00001, Sample Data 1, 10001
......./ 00002, Sample Data 2, 10001
......./ 00003, Sample Data 3, 10002
......./ 00004, Sample Data 4, 10003
......./ 00005, Sample Data 5, 10003
......./ 00006, Sample Data 6, 10003

Output should look like this:

"10001", "Test Record 1", "00001, 00002"
"10002", "Test Record 2", "00003"
"10003", "Test Record 3", "00004, 00005, 00006"

So going by the output, I am trying list record in Table 1 and have an output field which will list associated record numbers from Table 2 as they relate to the data in Table 1.

Did I confuse anyone? Hope not but let me know if this requires further information to solve.

To me it seems tough to code, if at all possible to begin with.

Thanks,
Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:13:00

[code]SELECT t1.Afield, t1.Bfield,
STUFF((SELECT ',' + CAST(AField AS varchar(15)) FROM Table2 WHERE Cfield=t1.Afield FOR XML PATH('')),1,1,'')
FROM Table1 t1
[/code]

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

James0816
Starting Member

9 Posts

Posted - 2010-03-03 : 11:27:47

Haven't tried this yet but wowsers! What is the "FOR XML PATH" statement? Haven't dealt with that as yet. Getting ready to attempt to code this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:36:22

try it. and i hope you're on SQL 2005 with compatibility level 90

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

James0816
Starting Member

9 Posts

Posted - 2010-03-03 : 11:39:28

hmmmmm...that might be worth considering. I am currently working in SQLTalk. I like using this app better.

James0816
Starting Member

9 Posts

Posted - 2010-03-03 : 11:45:31

Initial run gives me a "Error: ORA-00907: missing right parenthesis". This is when running in SQLTalk.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:49:09

quote:
Originally posted by James0816

Initial run gives me a "Error: ORA-00907: missing right parenthesis". This is when running in SQLTalk.

great. so you're using Oracle and it wont work
You may be better posting this in some oracle forums then like www.orafaq.com

this is MS SQL Server forum and solutions posted here are guaranteed to work only in SQL Server

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

James0816
Starting Member

9 Posts

Posted - 2010-03-03 : 11:56:12

My apologies. I'm so used to using SQL that I automatically look for those resolutions even though an Oracle DB.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources