CODEWITHSUNDEEP
mysqlsql
user535673
user535673

Reputation: 918

Show a row for every result between 3 mySQL tables

I have two tables that can have multiple records linking to another table, but I don't want mySQL to combine rows together. Example:

In this example there are three records that link to test_main – one test_veg and two test_fruit. I want to get those 3 rows to give a result like:

field1               vegName  fruitName
-------------------  -------  ---------
stuff in main table  cabbage  NULL
stuff in main table  NULL     apple
stuff in main table  NULL     pear

I would also like records from test_main that don't have any test_veg or test_fruit records linked to them.

This seems so simple but I can’t get it to work. Any ideas?

If I only had two tables (e.g. test_main and test_veg), a left join would work. With 3 tables, two left joins return only two rows:

SELECT  test_main.field1, test_veg.vegName, test_fruit.fruitName
FROM test_main
LEFT  JOIN test_veg ON test_veg.mainID = test_main.mainID
LEFT  JOIN test_fruit ON test_fruit.mainID = test_main.mainID
WHERE test_main.mainID=1

field1               vegName  fruitName
-------------------  -------  ---------
stuff in main table  cabbage  apple
stuff in main table  cabbage  pear

NB I’m stuck with mySQL3, which means no fancy things like SELECTs within WHEREs, nor UNIONs.

Upvotes: 2

Views: 113

Answers (2)

sgeddes
sgeddes

Reputation: 62841

There is a very cool article I read a while back about tricking MySQL3.x into mimicking a UNION. You need to create a DUMMY table with as many rows as needed for the Unions. So if you have 1 union (which is 2 different select statements), you'd need 2 rows in the dummy table.

Try something like this:

SELECT m.field1, v.vegName, f.fruitName
FROM test_main m
INNER JOIN dummy d 
  ON d.id < 3
LEFT JOIN test_veg v 
  ON v.mainID = m.mainID 
  and d.id = 1
LEFT JOIN test_fruit f 
  ON f.mainID = m.mainID 
  and d.id = 2
WHERE m.mainid=1

Here is the SQL Fiddle to better help you follow along.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In your query, you can add the following to the where clause:

test_veg.mainID is NULL and test_fruit.main_id is NULL

This will get main things that are neither fruits nor vegetables.

However, I think you have a problem with your data.

Everything on the same output row has the same MaindID. So, cabbage needs to have the same mainID as apple and pear. However, if I understand the question correctly, then mainId is supposed to be distinguishing between things like apples and pears.

Upvotes: 0

Related Questions