Results 1 to 5 of 5

Thread: joins

  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Post Unanswered: joins

    i have two tables each table having 2 column
    table1 table2
    eid ename eid ename
    1 A 3 C
    2 B 4 D

    i shld use a select query to get the following output

    eid1 eid2
    1 3
    2 4

    there is no relation btw the 2 tables except that eid column of both tables r of same data type
    the count of eid column in both table are also same

    can a blind join be done
    Last edited by bhargavi; 04-20-04 at 02:49.

  2. #2
    Join Date
    Apr 2004
    Posts
    22

    Question

    cross join?

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: joins

    Originally posted by bhargavi


    can a blind join be done
    what do u mean by a blind join?

    if u just do
    select table1.eid,table2.eid from table1,table2
    will form a cross join and generate four record set result.

  4. #4
    Join Date
    Apr 2004
    Posts
    11
    if i establish a cross join i will have an output like

    eid1 eid2
    1 3
    1 4
    2 3
    2 4


    this is not wat i want
    i have to select eid from first table and eid from second table
    and dispaly those 2 columns as the same

    select eid from table1
    output:
    1
    2
    select eid from table2
    output:
    3
    4

    some select query to combine both these outputs and provide an output
    like

    column1 column2
    1 3
    2 4


    all these in a single select query

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could run two separate queries, one on each table, saving the results into two temp tables, each of which has an identity column

    then join the temp tables based on the identity column values

    make it a full outer join in case on table has more rows than the other

    you could also do it in one query without temp tables by using ranking queries to generate derived tables

    here you can have this, i couldn't quite get it to work on some test rows i created...

    PHP Code:
    select dt1.rank
         
    dt1.eid
         
    dt1.ename
         
    dt2.rank
         
    dt2.eid
         
    dt2.ename
      from 
    (
           
    select (select count(*) from t1)
                  - 
    count(*) + as rank
                
    t1.eid
                
    t1.ename
             from table1 
    as t1
                
    table1 as t2
            where t1
    .eid <= t2.eid
           group
               by t1
    .eid
                
    t1.ename
           
    ) as dt1
    full outer
      join 
    (
           
    select (select count(*) from t1)
                  - 
    count(*) + as rank
                
    t1.eid
                
    t1.ename
             from table2 
    as t1
                
    table2 as t2
            where t1
    .eid <= t2.eid
           group
               by t1
    .eid
                
    t1.ename
           
    ) as dt2
        on dt1
    .rank dt2.rank
    order
        by dt1
    .rank
         
    dt2.rank 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •