How to CONCATENATE various sub-selects? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to CONCATENATE various sub-selects?

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-01-24 : 09:40:27

Hi all,

I am looking to concatenate various different sub-selects but I don't know how. Below are two examples of sub-selects I am looking to CONCAT:

(SELECT
fsa1.svc
FROM
fat1,
fsa1,
ft1,
sat1
WHERE
ft1.feature_type_code = f.feature_type_code AND
f.street_code = fat1.street_code AND
f.p_no = fat1.p_no AND
fat1.attrib_type_code = 'SLOT' AND
f.street_code = fsa1.street_code AND
f.p_no = fsa1.p_no AND
sat1.smart_type_code = fsa1.smart_type_code AND
fsa1.smart_type_code = 'SLOJ') as Reg_code

(SELECT av1.att_val_name
FROM
attribute_value,
feat_attrib_type
WHERE
av1.att_type_code = fat1.att_type_code AND
av1.attrib_val_code = fat1.attrib_val_code AND
fat1.street_no = f.street_no AND
fat1.plot_no = f.plot_no AND
fat1.attrib_type_code = 'SLEB') as Elec_Supplier

If anyone can show a way of doing it I would appreciate it!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-24 : 09:45:59

SELECT
(SELECT
fsa1.svc
FROM
fat1,
fsa1,
ft1,
sat1
WHERE
ft1.feature_type_code = f.feature_type_code AND
f.street_code = fat1.street_code AND
f.p_no = fat1.p_no AND
fat1.attrib_type_code = 'SLOT' AND
f.street_code = fsa1.street_code AND
f.p_no = fsa1.p_no AND
sat1.smart_type_code = fsa1.smart_type_code AND
fsa1.smart_type_code = 'SLOJ')
+
(SELECT av1.att_val_name
FROM
attribute_value,
feat_attrib_type
WHERE
av1.att_type_code = fat1.att_type_code AND
av1.attrib_val_code = fat1.attrib_val_code AND
fat1.street_no = f.street_no AND
fat1.plot_no = f.plot_no AND
fat1.attrib_type_code = 'SLEB') as Elec_Supplier

Madhivanan

Failing to plan is Planning to fail

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-01-24 : 09:52:55

Thank you madhivanan

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-01-24 : 11:53:23

I am having one more problem with this, if one of the sub-selects doesn't output any results but the others do - when I concat them all, I am getting blank results.

Any idea how to get around this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:55:52

use SELECT COALESCE(<firstquery>,'') + COALESCE(<secondquery>,'')

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources