Solved

SQL: Avoiding Union queries

Posted on 2014-04-24
7
346 Views
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?
0
Comment
Question by:GNOVAK
[X]
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
7 Comments
 
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.
0
 
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 ?
0
 
LVL 48

Accepted Solution

by:
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]
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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.

Jim.
0
 
LVL 48

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]
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40020493
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
 

Author Closing Comment

by:GNOVAK
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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