问题描述:

I have a table for lawyers:

CREATE TABLE lawyers (

id SERIAL PRIMARY KEY,

name VARCHAR,

name_url VARCHAR,

pic_url VARCHAR(200)

);

Imagine the whole table looks like this:

And a table for firms:

CREATE TABLE firms (

id SERIAL PRIMARY KEY,

name VARCHAR,

address JSONb

);

Whole table:

Then to map many to many relationship I'm using a map table lawyers_firms:

CREATE TABLE lawyers_firms (

lawyer_id INTEGER,

firm_id INTEGER

);

I'm not sure how to retrieve values from lawyersand from firmsgiven a lawyers_firms.firm_id.

For example:

1. SELECT name, name_url and pic_url FROM lawyers.

2. also SELECT name and address FROM firms.

3. WHERE `lawyers_firms.firm_id` = 1.

网友答案:

Try this:

SELECT l.name, l.name_url, l.pic_url, f.name, f.address 
FROM lawyers l
 inner join lawyers_firms lf
 on lf.lawyer_id = l.id 
 inner join firms f
 on f.id = lf.firm_id
 WHERE lf.firm_id = 1;
相关阅读:
Top