[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

SQL: Avoiding Union queries

I'm trying to find ideas for doing a query in a different, more efficient manner.
I have a Query like:

Select [Fields], [aField]
from [Table]
where [aField] in ( 2034, 3345, 5678, 2323)
and [other where stuff]

Works great.  Gives me a separate row for every number for [aField]

The problem is that each of the numbers also have a VarChar category attached to them. For example, 2034 is a FORD, 3345 and 2323 is a VW, 5678 is a Saturn, etc.

So on the individual row, instead of the numbers I need the VarChar. In some cases, there is more than one number attributable to the same VarChar and I need to have only one row for those VarChars.

I could do a query for each Varchar and UNION them, however, it seems there should be an easier way to accomplish this.

Any ideas?
0
GNOVAK
Asked:
GNOVAK
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>each of the numbers also have a VarChar category attached to them.
Where is the varchar value located?  Same table, different table, ...

Also it would be helpful if you can give us a data mockup of what your source data looks like, and the final set.
0
 
Paul JacksonCommented:
Should be able to use a join to achieve what you need but we need to know the structure of your table(s) in order to help you further.
Is the varchar category field in the same table ?
0
 
Dale FyeCommented:
Normally, you would have a separate "lookup" table that contains the codes and the descriptions:

Code    Desc
2034     Ford
3345     VW
2323     VW
5678    Saturn

Then you would join that table to your other table to return the description

Select [Table].[Fields], [Table].[aField], Table2.[Desc]
from [Table]
INNER JOIN [Table2] ON Table.[aFields] = Table2.{aFields]
where [aField] in ( 2034, 3345, 5678, 2323)
and [other where stuff]
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
unless I'm missing something, you just need to do a join to your look up table on [aField] that has varchar description.  

That would not change what your doing with the where clause.

Jim.
0
 
Dale FyeCommented:
You might also want to consider placing an bit or small integer field (call it InclThis)  in Table2, that you could set to 0 or 1 depending on which values of [aField] you are interested in.  Then, intstead of using the IN (     ) clause in your query, you would use:

Select [Table].[Fields], [Table].[aField], Table2.[Desc]
from [Table]
INNER JOIN [Table2] ON Table.[aFields] = Table2.{aFields]
where Table2.InclThis = 1
and [other where stuff]
0
 
PortletPaulCommented:
ditto

to really give an answer we would need a bit more information

providing sample data and expected result is the best method

I'm a bit unsure what this means " In some cases, there is more than one number attributable to the same VarChar" but that could be easier to understand through view sample data
0
 
GNOVAKAuthor Commented:
I should have thought Lookup Table! I'm using them all over the place when I design, however, when I do a query like this under the gun, I often forget that.
Thanks for the response and being on my wavelength for the question.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now