I'm trying to figure out a query that matches up combinations.

The tables should look like this:

Starting tables:

Table I

Field 1 Field 2 Field 3

a b c

d e f

Table 2

Field A Field B Field C

g h i

j k l


Ending up with:

Field 1 Field A Field 2 Field 3 Field B
Field C
a g b c
h i
a j b c
k l
d g e f
h i
d j e f
k l



It used to be considered a programming mistake to get these particular
results, but I can't remember what causes it.

On 10/3/2010 9:41 PM, Charles wrote:
How do the two tables relate?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 4 Oct, 02:41, Charles <ccha... (AT) gmail (DOT) com> wrote:
Not sure why you want this but this'll do it:
SELECT
t1.field1 `Field 1`,
t2.fielda `Field A`,
t1.field2 `Field 2`,
t1.field3 `Field 3`,
t2.fieldb `Field B`,
t2.fieldc `Field C`
FROM table1 t1
JOIN table2 t2 ON 1

This is based on:
CREATE TABLE `table1` (
`field1` char(1) NOT NULL DEFAULT '',
`field2` char(1) NOT NULL DEFAULT '',
`field3` char(1) NOT NULL DEFAULT '',
PRIMARY KEY (`field1`)
)
INSERT INTO `table1` VALUES ('a','b','c'),('d','e','f');
CREATE TABLE `table2` (
`fielda` char(1) NOT NULL DEFAULT '',
`fieldb` char(1) NOT NULL DEFAULT '',
`fieldc` char(1) NOT NULL DEFAULT '',
PRIMARY KEY (`fielda`)
)
INSERT INTO `table2` VALUES ('g','h','i'),('j','k','l');

On 4 Oct, 10:38, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Actually the query can be slightly simplified to:
SELECT
t1.field1 `Field 1`,
t2.fielda `Field A`,
t1.field2 `Field 2`,
t1.field3 `Field 3`,
t2.fieldb `Field B`,
t2.fieldc `Field C`
FROM table1 t1
JOIN table2 t2

The result is called the "cartesian product"