CROSS JOIN in VS Designer

[tma]

I'm using a SELECT statement like the following:

SELECT table1.ItemA, table2.itemB FROM table1, table2

However, I can't tell if the designer is changing that statement to:

SELECT table1.ItemA, table2.itemB FROM table1 CROSS JOIN table2

Is this the same? What's a CROSS JOIN?

 

[Jeff Dillon]

From a google search:

The cross-join is a very basic type of join that simply matches each row
from one table to every row from another table. While not very efficient, it
does illustrate the common characteristic of all joins: table unification.

An example of a cross-join would be as follows:

----------------------------------------------------------------------------
----mysql> SELECT * FROM pcs, clients;
----------------------------------------------------------------------------
----So why are there so many rows? Remember that a cross-join matches up
each row of one table with every row of the second table. Therefore, a join
involving two tables in which one table has 3 rows and the second table has
4 rows would result in 12 rows.

---Not sure this is what you want. You should specify how table1 and table2
are related. If they are not related, then you should be using 2 queries
most likely

Jeff

 

[tma]

Actually I don't WANT a CROSS JOIN as much as to pull data from two tables.

The two tables are related by ItemID as PK.

 

[Sylvain Lafontaine]

The CROSS JOIN is the cartesian product of two tables and it occurs when you
don't indicate the nature of the join beetween those two tables. In your
case, you should write this instead:

SELECT table1.ItemA, table2.itemB FROM table1 INNER JOIN table2 ON
table1.ItemID = table2.ItemID

An older syntax that you may also encounter is the following:

SELECT table1.ItemA, table2.itemB FROM table1 INNER JOIN table2 WHERE
table1.ItemID = table2.ItemID

However, this older syntax can lead to trouble for some complex queries.

S. L.

 

[Michelle]

You should use an INNER JOIN on ItemID.

SELECT Table1.ItemA, Table2.itemB
From Table1 INNER JOIN table 2 on Table1.ItemID =
Table2.ItemID

-----Original Message-----
Actually I don't WANT a CROSS JOIN as much as to pull data from two tables.

The two tables are related by ItemID as PK.

From a google search:

The cross-join is a very basic type of join that simply matches each row
from one table to every row from another table. While

not very efficient,
it

does illustrate the common characteristic of all joins: table unification.

An example of a cross-join would be as follows:

-------------------------------------------------------

------------------- ------------------- table. Therefore, a
join

involving two tables in which one table has 3 rows and

the second table
has

4 rows would result in 12 rows.

---Not sure this is what you want. You should specify

how table1 and
table2

are related. If they are not related, then you should be using 2 queries
most likely

Jeff

.