Newbie-ish Question - 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 2008 Forums

Analysis Server and Reporting Services (2008)

Newbie-ish Question

Author

Topic

Todzilla
Starting Member

8 Posts

Posted - 2014-02-07 : 13:45:45

I have a query that involves joining several tables. One table, called "AdamObjects" maps objectids to names. This table is pointed to by several different tables, to translate many items with objectids, like userids, languages and several other things, into their proper names.

I have a Query statement that works fine in SMS and returns what I want, but when I create the DataSet based on it, I get an error message of:

"Could not create a list of fields for the query. Verify that you can connect to the data and that your query syntax is correct."

My connection tests fine, so I presume it's the redundancy in field names I get back while joining several tables to the AdamObjects table, yielding multiple redundantly named fields.

Can someone advise on how to construct the query so that each of those fields is uniquely named?

Thanks so much!

Todzilla
Western Hemisphere
SQL 2008 R2 Noob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-07 : 14:53:24

Use aliases. But I don't think you should be pulling back the duplicate columns as that's a waste. Are you using "select *"? Return only the columns that you need, do not return duplicate columns.

Here's how to do aliases:

select a.column1 as alias1, b.column1 as alias2
from ...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Todzilla
Starting Member

8 Posts

Posted - 2014-02-07 : 15:12:52

Thanks Tara!

I will look into better use of aliases. And yes, I plan on only pulling in the columns I need, but I thought for example brevity to use a wildcard.

Todzilla
Western Hemisphere
SQL 2008 R2 Noob

Todzilla
Starting Member

8 Posts

Posted - 2014-02-10 : 11:20:35

On deeper review, I think I didn't state my problem adequately.

I have a table called "AdamObjects" that lists objectids and objectnames.

This table is referenced by several other tables, such as a table with userids and a different table with languageids. I need to point both to the AdamObjects to get a text version of their values.

I can alias the table, but it's the same table I'm referencing twice, so aliasing doesn't seem to buy me anything. I assumed aliasing is more for when you have redundantly named columns in two different tables.

Todzilla
Western Hemisphere
SQL 2008 R2 Noob

Todzilla
Starting Member

8 Posts

Posted - 2014-02-10 : 16:30:03

Sorry to be flooding my own thread for help, but I have more information.

It looks like the table I am querying is a "One True Lookup Table" which has three columns:

ObjectID
ObjectGUID
ObjectName

I have several other tables that have columns like:

LastOpenedBy
FileCreatedBy
VersionOwner

All of which point to ObjectID. When I do joins, and SELECT *, I get a couple or three columns redundantly named "ObjectID." When I try to explicitly SELECT ObjectName, I get an "ambiguous column name" error. While this makes sense, I'm not sure how to return both sets of values, but with unique names.

What brain dead step am I missing?

Thanks

Todzilla
Western Hemisphere
SQL 2008 R2 Noob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-10 : 16:52:59

select a.column1 as alias1, b.column1 as alias2, b.column2
from table1 a
join table2 b on a.somecolumn = b.somecolumn

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources