Need a real Inner/Outer Join expert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need a real Inner/Outer Join expert

Author

Topic

gotters
Starting Member

1 Post

Posted - 2006-10-05 : 13:08:21

Need some help here - dont even know if can do this:
So I have a view which is joining a few tables, one of them called X. It has a key X.genID which is a foreign key on tables A,B,C which are included in the join. X also has a field called pID which is a foreign key to the ID field on table P.
The view is retrieving all results from A,B,C and P for every genID found in X
NOW - I have another Table Y which also has a field called genID which also is a foreign key on tables A,B,C. And it has a field qID which is a foreign key for ID on Q.
Now I want the view to show all results from A,B,C for every genID in X and Y as well as show P data based on the relevant pID in X as well as all Q data based on the relevant qID in Y.
If anyone is brave enough to help me with this it would be greatly appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-05 : 13:44:58

Homework or Job Interview?

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 14:22:03

"Homework or Job Interview?"

I hope no-one ever pitches up here for interview saying "Sorry, I've only ever worked on systems with single letter table names" ...

You'll need to post scripts for DDL, a few INSERTs to populate the tables, an example of the corrupt output for the data you've provided, and the code showing how far you've got and where you are stuck.

Or we can agree a Time & Materials basis if you prefer

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 15:58:19

What's all this? You want everything out of every table?

select A.*, B.*, C.*, X.*, Y.*, P.*
from A join X on A.genid = X.genid
       join Y on A.genid = Y.genid
       join P on X.pid = P.pid
       join B on A.pk = B.pk
       join X on B.genid = X.genid
       join Y on B.genid = Y.genid
       join C on B.pk = C.pk
       join X on C.genid = X.genid
       join Y on C.genid = Y.genid

I am assuming there is some relationship between tables A, B & C. Otherwise, this could also work:

select A.*, B.*, C.*, X.*, Y.*, P.*
from A Cross Join B
     Cross Join C
     Cross Join X
     Cross Join Y
     Cross Join P
     on 1 = 1

Harsh Athalye
India.
"Nothing is Impossible"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-05 : 16:42:35

quote:

Originally posted by harsh_athalye
...this could also work:

select A.*, B.*, C.*, X.*, Y.*, P.*
from A Cross Join B
     Cross Join C
     Cross Join X
     Cross Join Y
     Cross Join P
     on 1 = 1

I don't think an ON clause is allowed in a CROSS JOIN.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-05 : 21:08:24

Learn SQL

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 00:43:34

quote:
I don't think an ON clause is allowed in a CROSS JOIN.

Oh, my mistake!!

Thanx MVJ.

BTW, It was just meant to be sarcastic joke.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-06 : 10:25:42

>>BTW, It was just meant to be sarcastic joke.

I thought so

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 10:30:07

"I thought so"

I thought that MVJ joining [Sorry!] this thread at all was pretty sarcastic! Not to mention restrained!

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-06 : 10:30:58

quote:

select A.*, B.*, C.*, X.*, Y.*, P.*
from A Cross Join B
     Cross Join C
     Cross Join X
     Cross Join Y
     Cross Join P
     on 1 = 1

Only use this if you need a scalar value from table P

Well, at least that is what the consultants do around here (No, i am NOT kidding)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-06 : 11:07:36

quote:
Originally posted by harsh_athalye
...BTW, It was just meant to be sarcastic joke...

I knew it was sarcasm, but there's no reason to have syntactically incorrect sarcasm.

I think we have to work to a higher standard.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 11:15:05

Some sort of sarcasm error perhaps?!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-06 : 11:25:25

How about a sarsasm smiley?

"Once you establish possibility, all that remains are desire and determination."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 11:43:52

Hold on guys!!

It was just an error of commission...

Are you going to hang me for it??

Harsh Athalye
India.
"Nothing is Impossible"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-06 : 13:24:34

Shouldn't the be "error of ommission"?

Or were you trying to be sarcastic? ;)

"Once you establish possibility, all that remains are desire and determination."

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 13:32:00

"Shouldn't the be "error of ommission"?"

Shouldn't that be "error of omission"? or were you trying ...

Recursion : See recursion

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 14:16:02

No, It was error of commission !!

If it would have been error of omission, it wouldn't have been error at all

Harsh Athalye
India.
"Nothing is Impossible"

blindman
Master Smack Fu Yak Hacker

2365 Posts