Parallel SQL queries with aligned data, in MS Access

I have three tables, fruit, person and vegetable

Person

personID personName
-------- ----------
1        Ken

Fruit

personID fruitname
-------- -----
1        apple
1        orange

Vegetable

personID vegetableName
-------- -------------
1        carrot
1        tomato
1        potato

And I want the output to be like this...

personName fruitName vegetableName
---------- --------- -------------
1      开发者_如何学Go    apple     carrot
1          orange    tomato
1                    potato

It lessen the duplication of outputs.. is this even possible? can when I tried it before the values keep repeating? Is their a way to avoid it?

---

This is only possible if you also add a "position" column to the fruit and vegetables tables, and use this as an additional join column.

Records are not sorted in SQL. So, if you want the sorted, you ALWAYS need a sort criteria, because the SQL standard does not enforce any kind of sort order else.

If an alphabetical sort order is enought, you could dynamically add a position column by something like this (will not work in MS ACCESS, but something similar will do):

 SELECT f1.personid, f1.fruitname, count(*) as position
   FROM fruit f1 outer join fruit f2 on f1.fruitname = f2.fruitname 
                                    and f1.personid = f2.personid  
  WHERE f2.fruitname < f1.fruitname
  GROUP BY f1.personid, f1.fruitname

This query has a position, starting from 0, with the names "before".

Now you can do something like:

select f.personid, f.fruitname, v.vegetablename 
  from (*fruitquery*) f outer join (*vegetaryquery*) v on f.personid = v.personid
                                                      and f.positionid = v.positionid;