CODEWITHSUNDEEP
mysqlsql
Mario
Mario

Reputation: 4998

how to implement a sql query that requires some one to one joins and then many to many?

Could please advise me on how to implement this query that requires first linking tables that have a one-to-one relationship and then need to link in a many-to-many relationship, I share an example of the structure of the tables:

table1
    id
    name
    date

table2
    id
    table1_id
    country

table3
    id
    first_name
    last_name

table4
    id
    table3_id
    type
    city

table1_table3
    id
    table1_id
    table3_id

table5
    id
    somefield

table1_table5
    id
    table1_id
    table5_id

Now I try this query but I do not know how I should implement the many-to-many bond

select
  table1.id                             id,
  table3.first_name                     first_name,
  table3.last_name                      last_name,
  table4.type                           type,
  table4.city                           city,
  table2.country                        country,
  table5.somefield                      somefield
from
  table1 table1
    inner join
  table2 table2 on table1.id = table2.table1_id
    inner  join
  table3 table3
    inner join
  table4 table4 on table4.table3_id = table3.id
    inner join
  table5 table5
where
  table1.date > '2018-05-04'

Thank you very much in advance

Upvotes: 0

Views: 69

Answers (2)

The Impaler
The Impaler

Reputation: 48865

Just use normal join relationships:

select
  t1.id                             id,
  t3.first_name                     first_name,
  t3.last_name                      last_name,
  t4.type                           type,
  t4.city                           city,
  t2.country                        country,
  t5.somefield                      somefield
from table1 t1
    join table2 t2 on t1.id = t2.table1_id
    join table1_table3 t13 on t13.table1_id = t1.id
    join table3 t3 on t3.id = t13.table3_id
    join table4 t4 on t4.table3_id = t3.id
    join table1_table5 t15 on t15.table1_id = t1.id
    join table5 t5 on t5.id = t15.table5_id
where
  t1.date > '2018-05-04'

Also:

  • I improved readability by using shorter aliases such as t1.

  • I replaced inner join by just join since inner is redundant. You can add then back if you want to.

Upvotes: 1

Der U
Der U

Reputation: 3335

not sure if this works for you, but table table1_table5 should probably be joined with an on-clause like this

join table1_table5
on table1_table5.table1_id = table1.id

and below that table table5 should propably be joined like this

join table5 table5
on table1_table5.table5_id = table5.id

btw: you can use and for multiple conditions in an on-clause

Upvotes: 2

Related Questions