CODEWITHSUNDEEP
pythonsqldatabasesqlite
Har
Har

Reputation: 3918

Simplifying an SQL statement -- Many to many relationship

I have a generic problem when selecting records from a table with many-many relationships suppose as an example I have the two tables as follows:

 MainTable:
  id <-- Primary key
  description

 DataTable:
  id <-- Primary key
  data1
  data2
  data3
  description FOREIGN KEY of Table(MainTable)

I currently have the following query:

myelements = executeSQL(select distinct description from MainTable order by description)

and then I use the elements obtained from this query to perform yet another query:

for item in myelements:
    executeSQL(select data1 from DataTable where description = item)

Is there a way to remove this for loop and have a single sql statement to perform this operation?

I would like to bundle up all records from the DataTable for all of the available descriptions and store them in code. so that I have something as:

map[description] = every row of that description in DataTable

Upvotes: 0

Views: 88

Answers (1)

Rida BENHAMMANE
Rida BENHAMMANE

Reputation: 4129

Try to use join like this :

SELECT MT.description, group_concat(DT.data1) 
FROM DataTable DT JOIN MainTable MT ON (DT.description = MT.description)
GROUP BY MT.description
ORDER BY MT.description

Upvotes: 1

Related Questions