SQL: Avoiding Union queries

Posted on 2014-04-24
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 65

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 47

Accepted Solution

Dale Fye (Access MVP) earned 500 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]
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 57
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 48

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 42
store vs query adhoc - no show rows 4 30
T-SQL: Wrong Result 7 32
MAC Dreamweaver connect to external MS SQL Server 2 32
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

731 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