Multiple Lookup Fields in Same Table

[JulesDBQ]

I have a table that contains at least 4 fields that are based on a source field in another table. So, for example....Table A has fields 1,2,3,4 which are each integers based on a field in Table B. (In this case, it is staff names).

Does anyone know of an uncomplicated way to query this data? In Access you just have a Lookup Value for the field, but this doesn't seem to be an easy task in SQL Server. Any thoughts?

I basically want to be able to query these results for outputting reports, etc. Thanks in advance!

 

[SQLDenis]

do a search for the split function that way you can join the result of this function with your table

but more important is: why do you violate the rules of a database? why is this not normalized? This is a -1 normal form design

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

You could join tableB four times (If I understood you correctly)

SELECT Fld1,
       Fld2,
       Fld3,
       Fld4,
       Tbl1.AField,
       Tbl2.AField,
       Tbl3.AField,
       Tbl4.AField
FROM MainTable
INNER JOINF TableB Tbl1 ON MainTable.Fld1 = Tbl1.IdField
INNER JOINF TableB Tbl2 ON MainTable.Fld2 = Tbl2.IdField
INNER JOINF TableB Tbl3 ON MainTable.Fld3 = Tbl3.IdField
INNER JOINF TableB Tbl4 ON MainTable.Fld4 = Tbl4.IdField

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

The question you are asking is very vague, but using
a join will work in relating these two tables.

example:

select * from tableA a inner join tableB b on a.field1 = b.staffName

I may be totally off, but I can't tell based off your question.



<.

 

[bborissov]

My mistake;

INNER JOINF

must be

INNER JOIN

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

Okay I misread that i thought you had '1,2,3,4' in a column

even though you should have those in a third table

assuming that you have an employee and a user table then you need a UserEmployee table

table UserEmployee(Userid,EmployeeId)

1,1
1,2
1,3
1,4

then your query becomes

select * from user u join UserEmployee ue on u.Userid = ue.Userid
join Employee e on ue.EmployeeID = e.Employee


and then of course people will ask how to concatenate the employees into 1 column...never ending story.. ;-)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[JulesDBQ]

Thanks to all for the quick response! I really appreciate your help. I had success!!