多表SQL交叉引用搜索难度

Question

I have three tables in my database; WORKER_DETAIL, STORE_WORKER, and STORE_NAME. Entries from each table include:

For WORKER_DETAIL (4 fields = worker sequence number, name, birthday, gender):

WSN Name Birthday Gender

001 John 1/1/1964 Male

002 Dave 2/2/1980 Male

003 Jane 3/3/1975 Female

For STORE_NAME (3 Fields = Store Sequence Number, Store Name, City)

SSN Name City

001 StoreA Los Angeles

002 StoreB San Francisco

003 StoreC Miami

and the last table, STORE_WORKER, brings the two together (3 fields = WSN, SSN, Pay)

WSN SSN Pay

001 001 $50 (John, who works in StoreA, gets paid $50)

003 001 $40 (Jane, who works in StoreA, gets paid $40)

002 003 $60 (Dave, who works in StoreC, gets paid $60)

I am currently making a search form, that will allow users to input the name of the Worker. The search form will then take the input name, match it with the corresponding WSN, take the WSN and match it with the corresponding SSN, take the SSN, then match it with the corresponding Store Name and City, then output that information (The Store Name and City from the STORE_NAME table).

But I am not sure how to do this in the form of a SQL statement. From what I understand, the SQL syntax involves either JOIN or UNION, but I'm not sure how to go about it.

Answer 1

The query that you want is a join of the tables:

select w.wsn, ssn.name, ssn.city
from store_worker sw join
     worker_detail w
     on sw.wsn = w.wsn join
     store_name s
     on sw.ssn = s.ssn
where w.name = $WORKERNAME;

The where clause compares the worker name to the input worker name.