Question 189303 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can't write a query for three tables

I need to write the following query. Suppose we have a table structure like this:

main_table:

id PRIMARY KEY
column1,
column2,
column3

table_1:

id PRIMARY KEY
main_table_id REFERENCES main_table NOT NULL,
column1,
column2,
column3

table_2:

id PRIMARY KEY
table_1_id REFERENCES table1 NOT NULL,
necessary_column

I need to get data from the main_table that has a given value in table_2.necessary_column through a link to the table_1. How can I do this?

I wrote a query for 2 tables, but I don’t know how to do this with 3 tables.

Tags:

2

Answers


  1. 0

    get data from the main_table that has a column necessary_column from table_2 through a link to the table_1

    There are many ways. Demonstrating a query with EXISTS, so that you get unique rows from main_table:

    SELECT *
FROM   main_table m
WHERE EXISTS (
   SELECT  -- can be empty
   FROM   table_1 t1
   JOIN   table_2 t2 ON t2.table_1_id = t1.id
   WHERE  t1.main_table_id = m.id
   AND    t2.necessary_column = $input
   );

    You could also just join all three tables, but that might multiply rows, and you might have to add another expensive DISTINCT:

    SELECT m.* -- or: DISTINCT m.*
FROM   main_table m
JOIN   table_1 t1 ON t1.main_table_id = m.id
JOIN   table_2 t2 ON t2.table_1_id = t1.id
WHERE  t2.necessary_column = $input;
    Login or Signup to reply.
  2. 0

    The question description is not very clear. So, it is hard to answer for sure. But, it seems that you need to better understand the difference between inner join and left join and that we can chain these operations.

    Let’s try to use some more clear as the structure below:

    enter image description here

    Creating these tables and populating with some data

    
-- create
CREATE TABLE USER (
  user_id INTEGER PRIMARY KEY,
  user_name TEXT NOT NULL
);

CREATE TABLE DOG (
  dog_id INTEGER PRIMARY KEY,
  dog_name TEXT NOT NULL,
  ower_id INTEGER,
  FOREIGN KEY (ower_id) REFERENCES USER(user_id)
);

CREATE TABLE TOY (
  toy_id INTEGER PRIMARY KEY,
  dog_id INTEGER,
  toy_name TEXT NOT NULL,
  FOREIGN KEY (dog_id) REFERENCES DOG(dog_id)
);

-- insert
INSERT INTO USER VALUES (1001, 'Anna'); -- Anna has 2 dogs
INSERT INTO USER VALUES (1002, 'Bob');  -- Bob has 1 dog
INSERT INTO USER VALUES (1003, 'Carl'); -- Carl does not have dogs


INSERT INTO DOG VALUES (2001, 'Snoopy'   , 1001); -- Snoopy belongs to Anna and have 2 toys
INSERT INTO DOG VALUES (2002, 'Fluffy'   , 1001); -- Fluffy belongs to Anna and have 1 toy
INSERT INTO DOG VALUES (2003, 'Snow Ball', 1002); -- Snow Ball belongs to Bob and have no toys
INSERT INTO DOG VALUES (2004, 'Zorro',     NULL); -- Zorro has one toy but no owner

INSERT INTO TOY VALUES (3001, 2001, 'Bone');  -- Bone belongs to Snoopy
INSERT INTO TOY VALUES (3002, 2001, 'Stick'); -- Stick belongs to Snoopy
INSERT INTO TOY VALUES (3003, 2002, 'Ball');  -- Ball belongs to Fluffy
INSERT INTO TOY VALUES (3004, 2004, 'Mask');  -- Mask belongs to Zorro

    Queries

    If you want to chain the relationship between these three tables that require all the connections, you can do that in any of the directions.

    In other words, the result of this query:

    SELECT 
  user_name,
  dog_name,
  toy_name
FROM TOY -- go to all toys
INNER JOIN DOG ON DOG.dog_id = toy.dog_id -- then search for the dogs owners of the toys (required)
INNER JOIN USER ON user.user_id = dog.ower_id; -- then search for the users owners of the dogs

-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna      | Snoopy   | Bone     |
-- | Anna      | Snoopy   | Stick    |
-- | Anna      | Fluffy   | Ball     |
-- |-----------|----------|----------|

    Is the same the result of this one:

    SELECT
  user_name,
  dog_name,
  toy_name
FROM USER -- go to all users
INNER JOIN DOG ON dog.ower_id = user.user_id -- then go to the users dogs (required)
INNER JOIN TOY ON toy.dog_id = dog.dog_id; -- then go to the dogs toys (required)

-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna      | Snoopy   | Bone     |
-- | Anna      | Snoopy   | Stick    |
-- | Anna      | Fluffy   | Ball     |
-- |-----------|----------|----------|

    But, when we chain the queries using left join, the order of the chain makes a difference. As you can see in the following queries:

    SELECT 
  user_name,
  dog_name,
  toy_name
FROM TOY -- go to all toys
LEFT JOIN DOG ON DOG.dog_id = toy.dog_id -- then search for the dogs owners of the toys (optional)
LEFT JOIN USER ON user.user_id = dog.ower_id; -- then search for the users owners of the dogs (optional)

-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna      | Snoopy   | Bone     |
-- | Anna      | Snoopy   | Stick    |
-- | Anna      | Fluffy   | Ball     |
-- | NULL      | Zorro    | Mask     | -- Notice that Zorro has no owner
-- |-----------|----------|----------|

SELECT
  user_name,
  dog_name,
  toy_name
FROM USER -- go to all users
LEFT JOIN DOG ON dog.ower_id = user.user_id -- then go to the users dogs (optional)
LEFT JOIN TOY ON toy.dog_id = dog.dog_id; -- then go to the dogs toys (optional)

-- |-----------|-----------|----------|
-- | user_name | dog_name  | toy_name |
-- |-----------|-----------|----------|
-- | Anna      | Snoopy    | Bone     |
-- | Anna      | Snoopy    | Stick    |
-- | Anna      | Fluffy    | Ball     |
-- | Bob       | Snow Ball | NULL     | -- Notice that Snow Ball have no toys
-- | Carl      | NULL      | NULL     | -- Notice that Carl have no dogs
-- |-----------|-----------|----------|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search