self joins

self joins

Post by Isaac Blan » Wed, 20 Mar 2002 04:17:32




Quote:> what do I need to do?

Post DDL, insert script, desired output
 
 
 

self joins

Post by oj » Wed, 20 Mar 2002 04:20:56


mark,

here is an example for you...

create table #tbl(i int)
insert #tbl select 1 union select 2 union select 3
go

select *
from #tbl  t1 cross join #tbl t2
where t1.i=1

--
-oj
Rac v2.0 is now RELEASED!!!
Details on RAC and ObjectScriptr at:
http://www.rac4sql.com


Quote:> I am trying to  do a self join in which the join goes
> through a list of id numbers, performs according to a
> where clause and then spits out the results. The problem I
> am having is this: say I have a list with an id number
> listed 3 time. The self join goes through the list and
> compares the values as such; 1-1, 1-2, 1-3, 2-1,2-2,2-3,3-
> 1,3-2,3-3.I want the process to stop after the 1-1,1-2,1-3.
> what do I need to do?


 
 
 

1. Self Join vs Join between different tables

Is there a fundamental difference between joining a table with itself
or joining the table with another table?

When I attempt to join a table with itself I can't get it to do outer
joins properly.  Let me try to give and example

Counter     Date                       Value                SampleID
1           10/30/2001 11:46:29 AM     205                  48163
2           10/30/2001 11:46:29 AM     410                  48163
3           10/30/2001 11:46:29 AM     205                  48163
1           10/31/2001 11:51:29 AM     205                  48164
2           10/31/2001 11:51:29 AM     410                  48164
3           10/31/2001 11:51:29 AM     205                  48164
1           11/01/2001 12:01:29 AM     206                  48165
2           11/01/2001 12:01:29 AM     412                  48165
1           11/02/2001 12:06:29 AM     206                  48166
2           11/02/2001 12:06:29 AM     412                  48166
1           11/03/2001 11:46:29 AM      24                  48167
2           11/03/2001 11:46:29 AM      45                  48167
3           11/03/2001 11:46:29 AM      24                  48167
1           11/04/2001 11:46:29 AM      24                  48168
2           11/04/2001 11:46:29 AM      48                  48168
3           11/04/2001 11:46:29 AM      24                  48168

What I need returned is all 2 counters in the first table, all the 3
counters in the second table.

This is my basic SQL Query.  I guess it's basically an inner join (?)

select *
from table a, table b
where a.sampleid = b.sampleid
and a.counter = 2
and b.counter = 3

This returns data for 10/30, 10/31, 11/03 and 11/04.  I need the data
for 11/01 and 11/02 too even though there isn't a sampleid that
matches.

Thanks,

Abigail

2. gcc with oci

3. Self join VS equi join

4. SIGHUP while in critical section

5. Inner Join / Self Joins help!

6. Oracle Database Access Denied

7. self-join? union?--not sure

8. Can DTS transform fixed width data that overflow?

9. self join

10. Self JOIN query (I think)

11. Self-join

12. Self Join Query

13. Self JOIN query - right approach?