mySQL结合表查询数据

Question

Just doing some mySQL problems and have figured out all but the very last one (of course).

I have two tables.

The Question asked is "Write a query to return a list of all authors who live in the same city as any publisher."

Apologies as I'm not allowed to post images. But It's easy to figure out the headings to it's value nevertheless. I've added commas into the first few to separate them so you know the general workings.

Publishers table

PUB_ID PUB_NAME CITY STATE COUNTRY
0736 New Moon, Palo Alto, CA, USA
0877 Binnet & Hardley, Washington, DC, USA
1389 Algodata, Berkeley, CA, USA
1622 Five Lakes, Chicago, IL, USA
1756 Ramona Publishers Oakland CA USA
9901 GGG&G Munich NULL NULL
9952 Scootney Books New York NY USA
9999 Lucerne Publishing Paris NULL France

Authors table

AU_ID LNAME FNAME PHONE ADDRESS CITY STATE CONTRACT
172 White Johnson, 496-7223, 10932 Bigge Rd Menlo Park, CA 1
213 Green Marjorie, 986-7020, 309 63rd St. Oakland, CA 2
238 Carson Cheryl, 548-7723, 589 Darwin Ln, Berkeley, CA 1
267 O‟Leary Michael 286-2428 22 Cleveland Av San Jose CA 1
274 Straight Dean 834-2919 5420 College Av Oakland CA 2

I understand I have to use an Inner Join statement but how to do that AND match up the authors that live in the same city as a publisher is what is confusing me.

Answer 1

SELECT a.*
FROM Authors AS a
JOIN (SELECT DISTINCT city, state
      FROM Publishers) AS p
ON a.city = p.city AND a.state = p.state

DISTINCT prevents it from producing multiple results for an author if there's more than one publisher in their city.

You can also do it with a correlated subquery:

SELECT *
FROM Authors AS a
WHERE EXISTS (SELECT *
              FROM Publishers AS p
              WHERE p.city = a.city AND p.state = a.state)