CODEWITHSUNDEEP
sqlsql-serverselectsql-server-2008-r2
omega
omega

Reputation: 43973

sql server - how to modify values in a query statement?

I have a statement like this:

select lastname,firstname,email,floorid 
from employee 
where locationid=1 
    and (statusid=1 or statusid=3) 
order by floorid,lastname,firstname,email

The problem is the column floorid. The result of this query is showing the id of the floors.

There is this table called floor (has like 30 rows), which has columns id and floornumber. The floorid (in above statement) values match the id of the table floor.

I want the above query to switch the floorid values into the associated values of the floornumber column in the floor table.

Can anyone show me how to do this please? I am using Microsoft sql server 2008 r2.

I am new to sql and I need a clear and understandable method if possible.

Upvotes: 2

Views: 241

Answers (3)

phadaphunk
phadaphunk

Reputation: 13313

You need to use a join.
This will join the two tables on a certain field.
This way you can SELECTcolumns from more than one table at the time.

When you join two tables you have to specify on which column you want to join them.
In your example, you'd have to do this:
from employee join floor on employee.floorid = floor.id

Since you are new to SQL you must know a few things. With the other enaswers you have on this question, people use aliases instead of repeating the table name.
from employee a join floor b
means that from now on the table employee will be known as a and the table floor as b. This is really usefull when you have a lot of joins to do.

Now let's say both table have a column name. In your select you have to say from which table you want to pick the column name. If you only write this
SELECT name from Employee a join floor b on a.id = b.id
the compiler won't understand from which table you want to get the column name. You would have to specify it like this :
SELECT Employee.name from Employee a join floor b on a.id = b.id
or if you prefer with aliases :
SELECT a.name from Employee a join floor b on a.id = b.id

Finally there are many type of joins.

  • Inner join ( what you are using because simply typing Join will refer to an inner join.
  • Left outer join
  • Right outer join
  • Self join
  • ...

To should refer to this article about joins to know how to use them correctly.
Hope this helps.

Upvotes: 1

mykola
mykola

Reputation: 1658

select   lastname,
         firstname,
         email,
         floor.floornumber
from     employee
inner join floor on floor.id = employee.floorid
where    locationid = 1
         and (statusid = 1 or statusid = 3)
order by floorid, lastname, firstname, email

Upvotes: 5

stb
stb

Reputation: 3513

You have to do a simple join where you check, if the floorid matches the id of your floor table. Then you use the floornumber of the table floor.

select a.lastname,a.firstname,a.email,b.floornumber 
from employee a
join floor b on a.floorid = b.id
where a.locationid=1 and (a.statusid=1 or a.statusid=3) 
order by a.floorid,a.lastname,a.firstname,a.email

Upvotes: 1

Related Questions