Skip to content
Advertisement

Three table join with matched and unmatched records

I have three tables:

  • store
  • article_to_store
  • article table.

I need all articles with the store name. For all articles, I want to list all stores

store table contains 3 stores. article table contains 2 articles.

article_to_store relation table maybe has 2 records. One article in 2 stores.

I need a mysql query with this result:

store.name article_to_store.id article.name
Store 1 1 Pen
Store 2 2 Pen
Store 3 null Pen
Store 1 null Erase
Store 2 null Erase
Store 3 null Erase

If I use simple join the article_to_store table always id = 1

If I use LEFT JOIN, don’t get the unmatched records.

When I remove the group by then I got 12 records.

3 stores * 2 article_to_store * 2 article = 12 record. Thats not good for me.

I would like to 3 stores * 2 article = 6 record with article_to store data if exist.

I tried many join types, but it doesn’t work. Thanks for any ideas.

Advertisement

Answer

You can use a cross join to achieve this.

A bit of an unusual scenario, but here, you’re essentially asking “For every article, I want to list all stores – that’s a cross join“. Then to get the middle column, you’re only sometimes expecting a row back, which is a left join.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement