Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

316hitmanhart
Starting Member

2 Posts

Posted - 2010-10-13 : 13:13:48

Hi

I'm new to SQL and was wondering if anyone can help me.

I have a table with four columns called a,b,c and d.

In column a I have entered the values 1,5,9 and 13
In column b I have entered the values 2,6,10 and 14
In column c I have entered the values 3,7,11 and 15
In column d I have entered the values 4,8,12 and 16

I want to be able to run a query that can search all four columns for one value.

Currently I have the following query which works:-

select * from Table_1 where a = 7
OR b = 7
OR c = 7
OR d = 7

However I only want to have to enter the value once.

I think the query should be something like this below it doesn't work.

select * from Table_1 where a,b,c,d = 7

Thanks for any help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-13 : 13:19:32

Generally speaking if you want/need to search multiple columns for the same value that is an indication that your table is not designed properly. It sounds de-normalized. They should all be in the same column in different rows. Perhaps with some other column attribute to indicate what type of value it is.

Otherwise, one way or another, your criteria will need to explicitly reference each column individually for a given value.

EDIT:
here is an example of what I mean:


id  sourceCol  value
1          a      1
2          a      5
3          a      9
4          a     13
5          b      2
6          b      6
7          b     10
8          b     14
9          c      3
10         c      7
11         c     11
12         c     15

select [id], sourceCol where [value] = 7

Be One with the Optimizer
TG

316hitmanhart
Starting Member

2 Posts

Posted - 2010-10-13 : 13:31:26

The database is going to used my staff.

I'll explain a bit further

My company is a key cutting company. There are several makes and models of keys each with there own ID no. This ID then corrosponds to an ID of the keys my company uses. So my data will look abit like this

My Key ID No Key Ref 1 Key Ref 2 Key Ref 3
1 1234 5464 4562
2 9845 7777 4563
3 4563 2154 7895
4 0123 8465 9875

The idea here being that if someone brings in a key to be cut and the ref ID on it is 9875 then my staff would know it Key ID No 4 from our stock.

I want to be able to search all columns because my staff may not know which column 9875 is in

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-10-13 : 14:45:21

NORMALIZATION

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-13 : 15:48:20

quote:
Originally posted by 316hitmanhart

The database is going to used my staff.

I'll explain a bit further

My company is a key cutting company. There are several makes and models of keys each with there own ID no. This ID then corrosponds to an ID of the keys my company uses. So my data will look abit like this

My Key ID No Key Ref 1 Key Ref 2 Key Ref 3
1 1234 5464 4562
2 9845 7777 4563
3 4563 2154 7895
4 0123 8465 9875

The idea here being that if someone brings in a key to be cut and the ref ID on it is 9875 then my staff would know it Key ID No 4 from our stock.

I want to be able to search all columns because my staff may not know which column 9875 is in

This added detail fits perfectly with my suggestion.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources