Cartesian Join

Jennifer Mathews

unread,

Jan 28, 2010, 5:58:42 AM1/28/10

to

I have two tables:

Table Person
Field: PersonID (int)

Table Books
Field
BookID (int)
Is_Good (bit)

I need to create a SELECT that contains
a single list with
PersonID, BookID if Is_Good = 1

PersonID BookID
1 101
1 102
1 103
2 101
2 102
2 103

I have endlessly looked-up Cartesian Join
but they always show a common JOIN Field.
I can't seem to do it because there isn't
a common JOIN Field. Is there a way to do
this without a While Loop?

Thanks

Simon Whale

unread,

Jan 28, 2010, 6:30:49 AM1/28/10

to

Normally your tables would have some common identifier and you would join on
that.

But that aside to assist you, you would need to look at cursors and
inserting into a 3 table to join the two tables as you are after them

Hope this helps
Simon

"Jennifer Mathews" <walters...@live.com> wrote in message
news:%231C$cjAoKH...@TK2MSFTNGP04.phx.gbl...

Jennifer Mathews

unread,

Jan 28, 2010, 7:22:09 AM1/28/10

to

I was hoping that wasn't the answer to prevent using cursors. Thanks very much.

"Simon Whale" <si...@nospam.com> wrote in message
news:eRa%23B0AoK...@TK2MSFTNGP06.phx.gbl...

Dan Guzman

unread,

Jan 28, 2010, 7:53:04 AM1/28/10

to

> I need to create a SELECT that contains a single list with PersonID,
> BookID if Is_Good = 1

Is this what your looking for?

SELECT
Person.PersonID,
Books.BookID
FROM dbo.Person
CROSS JOIN dbo.Books
WHERE
Books.Is_Good = 1;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Jennifer Mathews" <walters...@live.com> wrote in message

news:#1C$cjAoKH...@TK2MSFTNGP04.phx.gbl...

Bob McClellan

unread,

Jan 28, 2010, 9:00:36 AM1/28/10

to

Jennifer,
A Cartesian join is when you join every row of one table to every row of
another table
PersonID
1
2
3
4
5

BookId Is_Good
1 1
2 1
3 2
4 1
5 2

this will return all 25 rows
Select p.*, b.*
from person p, books b

this will return 15 rows
Select p.*, b.*
from person p, books b
where Is_Good = 1

hth,
..bob

"Jennifer Mathews" <walters...@live.com> wrote in message

news:%231C$cjAoKH...@TK2MSFTNGP04.phx.gbl...

--CELKO--

unread,

Jan 29, 2010, 9:57:09 AM1/29/10

to

>> I have endlessly looked-up Cartesian Join but they always show a common JOIN Field [sic]. I can't seem to do it because there isn't a common JOIN Field [sic]. Is there a way to do this without a While Loop? <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

You do not have any keys, you don't know that books are identified by
an ISBN and fields are nothing like columns. You are still writing
with assembly language style bit flags. n short you have a file
system in SQL and not an RDBMS.

>> I have endlessly looked-up Cartesian Join but they always show a common JOIN Field [sic]. I can't seem to do it because there isn't a common JOIN Field [sic]. Is there a way to do this without a While Loop? <<

Loops? You even think in terms of procedural code, as if you had a mag
tape file. You used singular table because you process records from a
file one at a time instead in whole sets.

SELECT person_id, isbn
FROM People CROSS JOIN Books
WHERE good_flg = 1;

The CROSS JOIN creates all possible combination of the two tables and
has no ON clause.

If you don't know anything about RDBMS, then get a copy of the
simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

Jennifer Mathews

unread,

Feb 2, 2010, 7:23:15 AM2/2/10

to

Thanks. This looks like one of the two methods of doing it. (Always great to learn
different methodologies.)

"Bob McClellan" <bobm...@gmail.com> wrote in message
news:C46F69F7-DC35-4716...@microsoft.com...

Jennifer Mathews

unread,

Feb 2, 2010, 7:23:40 AM2/2/10

to

Thanks. This looks like one of the two methods of doing it. (Always great to learn
different methodologies.)

"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:A9119974-560C-4A2F...@microsoft.com...

Jennifer Mathews

unread,

Feb 2, 2010, 7:28:58 AM2/2/10

to

Celko,

You might have my message mixed-up with another message. DDL's have nothing to do with
my question and I posted what was need with example in the original question of the
thread.

BookID and PersonID are to most people, the keys you slammed me about below. As for the
"procedural code" you slammed me about below, I think you are mixing-up your front-end
code (C#\vb.Net\Fortran\Cobol\etc.) and a SQL Select statement of a stored procedure.

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:8609ff8d-5a99-4d7e...@z41g2000yqz.googlegroups.com...

TheSQLGuru

unread,

Feb 2, 2010, 2:05:56 PM2/2/10

to

Just ignore him Jennifer. :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Jennifer Mathews" <walters...@live.com> wrote in message

news:erYiNNAp...@TK2MSFTNGP04.phx.gbl...