Selecting from two unrelated tables causes duplicates

Question

When a query is completed, the table is filled with all possible combinations, whether they are related or not, without a JOIN or UNION. However if you use a JOIN or UNION it only prints one of each related item (thus removing duplicates) I Believe... I could be wrong, please correct me if this is the case.

However if a selection is made from two unrelated tables, Say for example:

Car             Furniture
-------         ------------
Make            Material
Model           Style
HP

And I wanted to select the Model of the car, and the material of furniture, how would I do this without getting a list like this?

XR6    Wood
XR6    Leather
XR6    Plastic
XR6    Metal
XR8    Wood
XR8    Leather
etc.....

instead, get a list like this:

XR6       Wood
XR8       Leather
Charger   Plastic
          Metal

it depends on what you are trying to do... like are you trying to pull just one model with one type of material? and would that be something you specify in your query? ----- also add some sql attempts in here so we can better understand what you're getting at — John Ruddell, Mar 20 '14 at 23:05
The query is a generic one. Something like `SELECT Model, Material FROM Car, Furniture;` My use of this is to populate some combo boxes on a form. — Ben, Mar 20 '14 at 23:06
do you have something that is common between the two tables? when you do `FROM Car, Furniture;` it is doing a `cross join` which is why you are getting the messed up results — John Ruddell, Mar 20 '14 at 23:07
@JohnRuddell no there isn't. I'm using these fields to create a new table, that has these relations. (if that makes sense) — Ben, Mar 20 '14 at 23:10
yea so then you want to use a `UNION` like the answer you got... basically thats the best way to join two tables when theres no common columns like an ID — John Ruddell, Mar 20 '14 at 23:15

score 0 · Answer 1 · answered Mar 20 '14 at 23:55

The way you do this in MySQL is to enumerate the values in two subqueries combined by union all. Then do an aggregation:

select rn, max(model) as model, max(material) as material
from ((select @rn := @rn + 1 as rn, c.model, NULL as material
       from cars c cross join
            (select @rn := 0) const
      ) union all
      (select @rnf := @rndf + 1 as rn, NULL as model, f.material
       from furniture f cross join
            (select @rnf := 0) const
      )
     ) cf
group by rn;

Selecting from two unrelated tables causes duplicates

1 Answers1