Correct usage of the STUFF command

Question

I'm trying to get the stuff command to work with the following code and I can not seem to get it right - sorry I'm a noob to all this.

The following code returns 21 rows as the countries.countryname has multiple records saved against the SpecSummary.specnumber - I'd like the data to output this in just 1 row. Help please!!

select SpecSummary.specnumber, countries.countryname 
from specsummary, gsmapprovedusage, gsmApprovedUsageCountryJoin, countries where 
 gsmapprovedusage.fkspecid=specsummary.specid 
 and gsmApprovedUsageCountryJoin.fkapprovedusage=gsmapprovedusage.pkid 
 and specsummary.SpecNumber = '5366443-002'
 and countries.langid=0 and countries.pkid=gsmApprovedUsageCountryJoin.fkcountry

Answer 1

Please see my comment regarding the columns you chose to JOIN on. Using your query and adjusting it so that the JOINs make sense see below a working version of your query using Fake data made possible by a feature that is in most databases called Common Table Expressions (CTE) or otherwise known as a WITH Block or a WITH Clause. The query I created produces 9 Records. I work in an Oracle Database. Just know that I did not alter your query at all with the exception of the BAD Join I mentioned. Simply Select ALL and run the query.

WITH countries AS /* This is a CTE */
(
  /* Using the WITH Clause we can populate fake data of 21 rows in this table.  */
  /* This is known as a Common Table Expresion aka CTE                          */
  SELECT 1001 AS pkid, 'Country 1' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1002 AS pkid, 'Country 2' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1003 AS pkid, 'Country 3' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1004 AS pkid, 'Country 4' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1005 AS pkid, 'Country 5' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1006 AS pkid, 'Country 6' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1007 AS pkid, 'Country 7' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1008 AS pkid, 'Country 8' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1009 AS pkid, 'Country 9' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1010 AS pkid, 'Country 10' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1011 AS pkid, 'Country 11' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1012 AS pkid, 'Country 12' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1013 AS pkid, 'Country 13' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1014 AS pkid, 'Country 14' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1015 AS pkid, 'Country 15' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1016 AS pkid, 'Country 16' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1017 AS pkid, 'Country 17' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1018 AS pkid, 'Country 18' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1019 AS pkid, 'Country 19' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1020 AS pkid, 'Country 20' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1021 AS pkid, 'Country 21' AS countryname, 0 AS langid FROM dual UNION ALL
  SELECT 1022 AS pkid, 'Country 22' AS countryname, 1 AS langid FROM dual
)
--SELECT * FROM countries; /* Uncomment this to select rows from the countries table */
, gsmapprovedusagecountryjoin AS /* This is a CTE */
( /* Rows commented out to simulate the fact that not every record in this table */
  /*   has a country in it that exists in the countries table.  This may or may  */
  /*   not be true in your case but the fact is the possibility still remains.   */
  /* Without any knowledge of how your Database is structured and what data is   */
  /*  being stored in your tables I can only make assumptions.                   */
  SELECT 5425 AS primary_key, 1001 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5424 AS primary_key, 1002 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5423 AS primary_key, 1003 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5422 AS primary_key, 1004 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5421 AS primary_key, 1005 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5420 AS primary_key, 1006 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5419 AS primary_key, 1007 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5418 AS primary_key, 1008 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5438 AS primary_key, 1009 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5437 AS primary_key, 1010 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5436 AS primary_key, 1011 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5435 AS primary_key, 1012 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5434 AS primary_key, 1013 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5433 AS primary_key, 1014 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5432 AS primary_key, 1015 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5430 AS primary_key, 1016 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5431 AS primary_key, 1017 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5429 AS primary_key, 1018 AS fkapprovedusage  FROM dual UNION ALL
  --SELECT 5428 AS primary_key, 1019 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5427 AS primary_key, 1020 AS fkapprovedusage  FROM dual UNION ALL
  SELECT 5426 AS primary_key, 1021 AS fkapprovedusage  FROM dual

)
--SELECT * FROM gsmapprovedusagecountryjoin;
, gsmapprovedusage AS
( /* Rows commented out to simulate the fact that not every record in this table */
  /*   has a country in it that exists in the countries table.                   */
  SELECT 7158 AS primary_key, 1001 AS pkid, 2001 AS fkspecid  FROM dual UNION ALL
  SELECT 7157 AS primary_key, 1002 AS pkid, 2002 AS fkspecid  FROM dual UNION ALL
  SELECT 7156 AS primary_key, 1003 AS pkid, 2003 AS fkspecid  FROM dual UNION ALL
  --SELECT 7155 AS primary_key, 1004 AS pkid, 2004 AS fkspecid  FROM dual UNION ALL
  SELECT 7163 AS primary_key, 1005 AS pkid, 2005 AS fkspecid  FROM dual UNION ALL
  SELECT 7162 AS primary_key, 1006 AS pkid, 2006 AS fkspecid  FROM dual UNION ALL
  SELECT 7161 AS primary_key, 1007 AS pkid, 2007 AS fkspecid  FROM dual UNION ALL
  SELECT 7160 AS primary_key, 1008 AS pkid, 2008 AS fkspecid  FROM dual UNION ALL
  SELECT 7159 AS primary_key, 1009 AS pkid, 2009 AS fkspecid  FROM dual UNION ALL
  SELECT 7170 AS primary_key, 1010 AS pkid, 2010 AS fkspecid  FROM dual UNION ALL
  SELECT 7169 AS primary_key, 1011 AS pkid, 2011 AS fkspecid  FROM dual UNION ALL
  SELECT 7168 AS primary_key, 1012 AS pkid, 2012 AS fkspecid  FROM dual UNION ALL
  SELECT 7167 AS primary_key, 1013 AS pkid, 2013 AS fkspecid  FROM dual UNION ALL
  --SELECT 7166 AS primary_key, 1014 AS pkid, 2014 AS fkspecid  FROM dual UNION ALL
  SELECT 7165 AS primary_key, 1015 AS pkid, 2015 AS fkspecid  FROM dual UNION ALL
  SELECT 7164 AS primary_key, 1016 AS pkid, 2016 AS fkspecid  FROM dual UNION ALL
  SELECT 7154 AS primary_key, 1017 AS pkid, 2017 AS fkspecid  FROM dual UNION ALL
  SELECT 7153 AS primary_key, 1018 AS pkid, 2018 AS fkspecid  FROM dual UNION ALL
  SELECT 7173 AS primary_key, 1019 AS pkid, 2019 AS fkspecid  FROM dual UNION ALL
  SELECT 7172 AS primary_key, 1020 AS pkid, 2020 AS fkspecid  FROM dual UNION ALL
  SELECT 7171 AS primary_key, 1021 AS pkid, 2021 AS fkspecid  FROM dual

)
--SELECT * FROM gsmapprovedusage;
, specsummary AS
(
  SELECT 9245 AS primary_key, 2001 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  --SELECT 9246 AS primary_key, 2002 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9252 AS primary_key, 2003 AS specid, '5366443-005' AS specnumber  FROM dual UNION ALL
  SELECT 9251 AS primary_key, 2004 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9250 AS primary_key, 2005 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  --SELECT 9249 AS primary_key, 2006 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9258 AS primary_key, 2007 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9257 AS primary_key, 2008 AS specid, '5366443-007' AS specnumber  FROM dual UNION ALL
  --SELECT 9256 AS primary_key, 2009 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9255 AS primary_key, 2010 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9254 AS primary_key, 2011 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9253 AS primary_key, 2012 AS specid, '5366443-010' AS specnumber  FROM dual UNION ALL
  SELECT 9263 AS primary_key, 2013 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9262 AS primary_key, 2014 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9261 AS primary_key, 2015 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  --SELECT 9260 AS primary_key, 2016 AS specid, '5366443-150' AS specnumber  FROM dual UNION ALL
  SELECT 9259 AS primary_key, 2017 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9265 AS primary_key, 2018 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  --SELECT 9264 AS primary_key, 2019 AS specid, '5366443-055' AS specnumber  FROM dual UNION ALL
  SELECT 9248 AS primary_key, 2020 AS specid, '5366443-002' AS specnumber  FROM dual UNION ALL
  SELECT 9247 AS primary_key, 2021 AS specid, '5366443-002' AS specnumber  FROM dual
)
--SELECT * FROM specsummary;


/* Alias your tables for easier readability as well as using the ANSI JOIN 
Syntax */
SELECT ss.specnumber, c.countryname
FROM specsummary ss /* Not sure what data is stored here without making assumptions */
  JOIN gsmapprovedusage u ON u.fkspecid = ss.specid /* Not sure what data is stored here */
  JOIN gsmapprovedusagecountryjoin gc ON gc.fkapprovedusage = u.pkid /* Not sure what data is stored here */
  JOIN countries c ON c.pkid = gc.fkapprovedusage /* Lookup Table for Country Names */
WHERE /* Keep your JOINs out of the WHERE clause if you can. */
  /* The WHERE clause should mainly be reserved for your Filters */
  c.langid = 0
  AND ss.specnumber = '5366443-002'
;

If you need/want a better answer provide enough information so that individuals don't have to guess or make assumptions about the data in your tables. I have made several assumptions while creating the 'Fake' data in the query I provided that produces the 9 rows you see in the image I posted here.

Answer 2

It's hard to provide an answer due to the lack of knowing what data is stored in your tables. Which tables contain unique values and which ones don't. To figure out where your 21 rows are coming from one needs to know how the data is being stored. Are these JOINs one-to-one or one-to-many. In the meantime I have cleaned up your query using ANSI Join syntax. Please try to move away from adding all of your JOINs and your Filters in the WHERE clause. For better readability keep your JOINs with your tables and keep your Filters in the WHERE clause.

/* Alias your tables for easier readability as well as using the ANSI JOIN 
Syntax */
SELECT ss.specnumber, c.countryname
FROM specsummary ss /* Not sure what data is stored here without making assumptions */
  JOIN gsmapprovedusage u ON u.fkspecid = ss.specid /* Not sure what data is stored here */
  JOIN gsmapprovedusagecountryjoin gc ON gc.fkapprovedusage = u.pkid /* Not sure what data is stored here */
  JOIN countries c ON c.pkid = gc.fkcountry /* Lookup Table for Country Names */
WHERE /* Keep your JOINs out of the WHERE clause if you can. */
  /* The WHERE clause should mainly be reserved for your Filters */
  c.langid = 0
  AND ss.specnumber = '5366443-002'
;

A great read that originally moved me toward using the ANSI JOIN syntax: Please use ANSI join syntax