Joining every row from 1 table to every row from another

Thread view:

radnor - 24 Nov 2005 19:42 GMT

Hello!

Answer is propably simple, but I'm struggling and can't solve this
problem. I have two tables:

Table GARMENT

STYLE_NO    DESCRIPTION    LABOUR_COST    NOTIONS
1        Trousers     18        xxx
2        Long Skirt     15        Zip/ 1 cm off
3        Shorts         10        Zip/ Clip

Table MATERIAL

MATERIAL_NO    FABRIC    COLOUR    PATTERN    COST
1        Silk     Black     Plain         7
2        Silk     Red     Abstract     10
3        Cotton     Yellow     Stripe         3

The thing is to estimate costs of making any garment with any
materials. I need to join all garments to all materials, like

STYLE_NO    DESCRIPTION    (....)    METERIAL_NO
1        XXX            1
1                    2
1                    3
2                    1
2                    2
2                    3
3                    1
3                    2
3                    3

and so on.. Could anyone please help me with this by giving some
examples of Oracle or MySQL query? Many thanks in advance!

Greetings
Kamil

jak zawsze pozdrawiający
radnor

Signature

O (=> useer(maupa)go2.pl 05011474(maupa)napier.ac.uk
)/\_,_[__] GG: 5004286 ICQ: 45007845
T>_ [ ] Wariat: człowiek cierpiący na dużą niezależność intelektualną.

Reply to this Message

Bill Karwin - 24 Nov 2005 20:37 GMT

> The thing is to estimate costs of making any garment with any
> materials. I need to join all garments to all materials, !

You're trying to produce what's called a Cartesian product.
This can be done in SQL simply by omitting any join condition. The
default join (with no condition limiting it) joins every row in one
table to every row in the other table:

SELECT GARMENT.*, MATERIAL.MATERIAL_NO
FROM GARMENT, MATERIAL;

This should work in Oracle, MySQL, or any other system that supports the
most rudimentary features of SQL.

Regards,
Bill K.

Reply to this Message

radnor - 24 Nov 2005 21:17 GMT

>SELECT GARMENT.*, MATERIAL.MATERIAL_NO
>FROM GARMENT, MATERIAL;
[quoted text clipped - 4 lines]
>Regards,
>Bill K.

Doh, I really need some rest. I've been making some subqueries to get
this instead of reminding simplest way. Thank you!

Regards
Kamil

jak zawsze pozdrawiający
radnor

Signature

O (=> useer(maupa)go2.pl 05011474(maupa)napier.ac.uk
)/\_,_[__] GG: 5004286 ICQ: 45007845
T>_ [ ] Wariat: człowiek cierpiący na dużą niezależność intelektualną.

Reply to this Message

Joining every row from 1 table to every row from another

Free Magazines