Solved

SQL: Avoiding Union queries

Posted on 2014-04-24
7
338 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
7 Comments
 
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.
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 47

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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.

Jim.
0
 
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]
0
 
LVL 48

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now