Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL: Avoiding Union queries

Posted on 2014-04-24
Medium Priority
Last Modified: 2014-04-24
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?
Question by:GNOVAK
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 66

Expert Comment

by:Jim Horn
ID: 40020458
>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.
LVL 29

Expert Comment

by:Paul Jackson
ID: 40020468
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 ?
LVL 48

Accepted Solution

Dale Fye earned 2000 total points
ID: 40020471
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]
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 58
ID: 40020474
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.

LVL 48

Expert Comment

by:Dale Fye
ID: 40020481
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]
LVL 49

Expert Comment

ID: 40020493

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

Author Closing Comment

ID: 40020593
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.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question