SQL Select With Multiple References to Single Table

user_264575 0
Viewed 12606

I have a SQL DB that contains multiple relational tables. There are some fields in the master table that reference another table multiple times. For example, say I have a database of salesmen who are responsible of sales for multiple states. My database has fields for State1, State2, and State3 all of which map back to a States table. I can't figure out for the life of me how to write a query to return a record with all the enumerated states. If I only needed one State field, I know I would do:

SELECT Master.Name, State.Enumeration AS 'State'
FROM MasterTable Master, StateTable State
WHERE Master.State1 = State.ID;

How can I expand this for all my State fields?

Thanks.

sql database
3 Answers

select tbl_book_con1.city,con1,tbl_book_destination.city,destination,labar_char,tbl_book_c_from.city,c_from,tbl_book_c_to.city,c_to,tbl_book_payment.city,paymentid from tbl_booking tbb_bookinner join tbl_master tbl_book_con1 on tbl_book_con1.id = tbb_book.con1
inner join tbl_master tbl_book_destination on tbl_book_destination.id = tbb_book.con1
inner join tbl_master tbl_book_c_from on tbl_book_c_from.id = tbb_book.con1
inner join tbl_master tbl_book_c_to on tbl_book_c_to.id = tbb_book.con1
inner join tbl_master tbl_book_payment on tbl_book_payment.id = tbb_book.con1

enter image description here

user_13219040
Related