How to Do Many to Many Table Outer Joins

How do you do many to many table outer joins?

SELECT * FROM foo
  LEFT OUTER JOIN (foo2bar JOIN bar ON (foo2bar.bid = bar.bid AND zid = 30))
  USING (fid);

Tested on MySQL 5.0.51.

This is not a subquery, it just uses parentheses to specify the precedence of joins.

How to perform full outer joins on multiple tables

You don't want a FULL JOIN. You want a LEFT JOIN:

select s.name, sp.name 
from student s left outer join
     plays p
     on s.usn = p.usn left outer join
     sport sp
     on p.sport_id = sp.sport_id;

A left join keeps all rows in the first table and matching rows in the subsequent tables -- which is exactly what you want.

FULL JOIN is rarely needed. I write a lot of SQL and months go by without my using full join in any database.

In this example, a FULL JOIN would be used if you also wanted all sports that have no students. That is NULL values could appar in any column.

Multiple LEFT OUTER JOIN on multiple tables

The join on D is an inner join, the rest are left outer joins:

SELECT *
FROM TABLEA A JOIN
     TABLED D
     ON D.Z = A.Z LEFT JOIN
     TABLEB B
     ON A.X = B.X LEFT JOIN
     TABLEC C
     ON B.Y = C.Y 
WHERE MY_COL = @col_val;

I always start chains of joins with inner joins followed by the left outer join. I never use right join, and full join rather rarely. The inner joins define the rows in the result set, so they come first.

SQL JOIN many-to-many

It's possible with this little trick (OUTER JOIN on the many-to-many table, with the constraint that the GroupID has to be 3 (for Drama)

http://sqlfiddle.com/#!9/01cf3/1

SELECT elements.ID, elements.Element, groups.Genre
  FROM elements
LEFT OUTER JOIN group_elements
  ON elements.ID = group_elements.ElementID
 AND group_elements.GroupID = 3
LEFT OUTER JOIN groups
  ON group_elements.GroupID = groups.ID

LEFT OUTER JOIN means : take all the lines from the tables that preceded (the ones that are on the LEFT hand side of the LEFT OUTER JOIN, if you will), even if there's no lines corresponding to them in the following tables. The condition ON elements.ID = group_elements.ElementID AND group_elements.GroupID = 3 says that if we find anything that matches our ElementID, it also must be a drama (GroupID = 3). We then do another LEFT OUTER JOIN on the groups table, which enables us to display the Genre column, or NULL if the element was not a drama.

Multiple FULL OUTER JOIN on multiple tables

SELECT  A.column2
        , B.column2
        , C.column2
FROM 
(
    (SELECT month, column2 FROM table1) A
    FULL OUTER JOIN
    (SELECT month, column2 FROM table2) B on A.month= B.month
    FULL OUTER JOIN 
    (SELECT month, column2 FROM table3) C on ISNULL(A.month, B.month) = C.month
)

Get data using join on many to many table

I think it can help you to start your query:

select gallery_element.id, galleries.name, areas.name, stores.name from gallery_element
inner join galleries on galleries.id = gallery_element.galleries_id
inner join areas on areas.id = gallery_element.area_id
inner join stores on stores.id = gallery_element.store_id

Slick nested outer joins with many-to-many tables

I finally had the time to focus on this issue again. With my current architecture and structure, I couldn't implement @Valerii Rusakov's answer, but it helped to solve the problem tremendously. Thank you!

So here's how I did it:

def myFunction: Future[Seq[(RecordModel, Seq[Option[(TagsModel, Record_TagsModel)]], Seq[Option[(MarketsModel, Record_MarketModel)]], Seq[Option[(UrlsModel, Record_UrlModel)]])]] = {
val query = for {
  (((records, tags), markets), urls) <- (records filter (x => x.deleted === false && x.clientId === 1)
    joinLeft (tags join recordTags on (_.tagId === _.tagId)) on (_.recordId === _._2.recordId)
    joinLeft (markets join recordMarkets on (_.marketId === _.marketId)) on (_._1.recordId === _._2.recordId)
    joinLeft (urls join recordUrls on (_.urlId === _.urlId)) on (_._1._1.recordId === _._2.recordId))
} yield (records, tags, markets, urls)
db.run(query.result).map(_.toList.groupBy(_._1).map { // Group by records
  case (records, composedResult) =>
    (
      records,
      composedResult.groupBy(_._2).keys.toSeq, // Tags and RecordTags
      composedResult.groupBy(_._3).keys.toSeq, // Markets and RecordMarkets
      composedResult.groupBy(_._4).keys.toSeq // Urls and RecordUrls
    )
}.toSeq)
 }

Notice that I yield for (((records, tags), markets), urls). This allows me to access those precise attributes later on, which makes the grouping and mapping a lot easier. It's still not perfect because I have to work with the table and the relational table e.g. TagsModel, Record_TagsModel. This is just a minor issue though. Maybe some of you guys know how to resolve it. The current function returns all records regardless of tags, markets or urls.

Joining two tables with many to many relationship in sql

If you have unequal numbers of rows for each partition then you can do:

Oracle Setup:

CREATE TABLE table1 ( col1, col2, col3 ) AS
SELECT 1, 'P1', '1.0' FROM DUAL UNION ALL
SELECT 1, 'P1', '1.1' FROM DUAL UNION ALL
SELECT 1, 'P1', '1.2' FROM DUAL UNION ALL
SELECT 1, 'P2', '1.0' FROM DUAL UNION ALL
SELECT 1, 'P2', '1.2' FROM DUAL UNION ALL
SELECT 2, 'P1', '1.0' FROM DUAL;

CREATE TABLE table2 ( col1, col2, col3 ) AS
SELECT 1, 'P1', '2.0' FROM DUAL UNION ALL
SELECT 1, 'P1', '2.1' FROM DUAL UNION ALL
SELECT 1, 'P1', '2.2' FROM DUAL UNION ALL
SELECT 1, 'P2', '2.1' FROM DUAL UNION ALL
SELECT 2, 'P1', '2.0' FROM DUAL UNION ALL
SELECT 2, 'P1', '2.1' FROM DUAL;

Query:

SELECT COALESCE( t1.col1, t2.col1 ) AS col1,
       COALESCE( t1.col2, t2.col2 ) AS col2,
       t1.col3 AS t1col3,
       t2.col3 AS t2col3
FROM   (
         SELECT t.*,
                ROW_NUMBER() OVER ( PARTITION BY col1, col2
                                    ORDER BY col3 ) AS rn
         FROM   table1 t
       ) t1
       FULL OUTER JOIN
       (
         SELECT t.*,
                ROW_NUMBER() OVER ( PARTITION BY col1, col2
                                    ORDER BY col3 ) AS rn
         FROM   table2 t
       ) t2
       ON ( t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.RN = t2.rn )
ORDER BY col1, col2, t1col3 NULLS LAST, t2col3 NULLS LAST;

Output:

      COL1 COL2 T1COL3 T2COL3
---------- ---- ------ ------
         1 P1   1.0    2.0    
         1 P1   1.1    2.1    
         1 P1   1.2    2.2    
         1 P2   1.0    2.1    
         1 P2   1.2           
         2 P1   1.0    2.0    
         2 P1          2.1    

---

Related Topics