Having trouble with a Union - SQL Server Forums

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)

Having trouble with a Union

Author

Topic

brad_81x
Starting Member

8 Posts

Posted - 2011-06-09 : 07:11:58

Hi All,

This forum was recommended by a friend. I'm having trouble with SQL 2005 query and a little stuck, I'm not going to profess that I know loads about SQL but here goes:

SELECT country_match.ISOCountryName, country_match.ISO2char, country_match.InforCoreRegion
FROM country_match
UNION
SELECT MIDEAST.ZIPCODE, MIDEAST.SICCODE, MIDEAST.LT50M, MIDEAST.[50MTO250M], MIDEAST.GT250M
FROM MIDEAST
UNION
SELECT sic_information.[1stTierDescription], sic_information.MajorDivision
FROM sic_information INNER JOIN
MIDEAST ON sic_information.[1stTierCode] = MIDEAST.SICCODE INNER JOIN
country_match ON MIDEAST.COUNTRYCODE = country_match.WorldBase3digit

And I get error:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Not really sure what to do... Advices most welcome

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-09 : 07:29:02

Using UNION (or UNION ALL) needs for all involved SELECT lists the same number and datatype of columns because otherwise it is not possible to return ONE resultset.

No, you're never too old to Yak'n'Roll if you're too young to die.

brad_81x
Starting Member

8 Posts

Posted - 2011-06-09 : 07:34:16

shall i use INSERT then?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-09 : 08:08:31

You have posted a SELECT statement and now you are asking if you should use INSERT??
INSERT and SELECT are very very different you know?
Maybe you can tell us what you really want to do so maybe we can give a solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-09 : 08:09:07

Looking at your query, my guess is that you don't need a UNION at all. Emphasize "guess". I reformatted your query and posted it below, along with that I think you need:


-- CURRENT QUERY
SELECT
   country_match.ISOCountryName,
   country_match.ISO2char,
   country_match.InforCoreRegion
FROM
   country_match
UNION
SELECT
   MIDEAST.ZIPCODE,
   MIDEAST.SICCODE,
   MIDEAST.LT50M,
   MIDEAST.[50MTO250M],
   MIDEAST.GT250M
FROM
   MIDEAST
UNION
SELECT
   sic_information.[1stTierDescription],
   sic_information.MajorDivision
FROM
   sic_information
   INNER JOIN MIDEAST
      ON  sic_information.[1stTierCode] = MIDEAST.SICCODE
   INNER JOIN country_match
      ON  MIDEAST.COUNTRYCODE = country_match.WorldBase3digit


-- What I am guessing you need.
SELECT
   country_match.ISOCountryName,
   country_match.ISO2char,
   country_match.InforCoreRegion,
   MIDEAST.ZIPCODE,
   MIDEAST.SICCODE,
   MIDEAST.LT50M,
   MIDEAST.[50MTO250M],
   MIDEAST.GT250M,
   sic_information.[1stTierDescription],
   sic_information.MajorDivision
FROM
   sic_information
   INNER JOIN MIDEAST
      ON  sic_information.[1stTierCode] = MIDEAST.SICCODE
   INNER JOIN country_match
      ON  MIDEAST.COUNTRYCODE = country_match.WorldBase3digit

brad_81x
Starting Member

8 Posts

Posted - 2011-06-09 : 08:34:42

hi sunita - thanks, yeah the reformat looks as though its working - but I'm getting millions of duplicates across my results... if you know what i mean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources