SQL Server Performance

T-Sql-Joining two tables

Discussion in 'T-SQL Performance Tuning for Developers' started by rak1791, May 3, 2006.

  1. rak1791 New Member

    Hi

    I have two tables

    Table 1
    Col1 Clo2
    a xy
    a as

    Table 2
    Col1 Clo2
    a op
    a qr

    After joining these two tables I want result set as following

    a xy op
    a as qr

    Would be greate if some one can help me to write query for the same.

    Regards
    Rak****.
  2. Madhivanan Moderator

  3. FrankKalis Moderator

  4. FrankKalis Moderator

  5. Madhivanan Moderator

    quote:Originally posted by FrankKalis

    Btw Madhivanan, your query returns a wrong resultset.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
    Yes it is.

    rak1791, col1 should be unique to get proper result

    Madhivanan

    Failing to plan is Planning to fail
  6. khtan New Member

    What rak1791 wants does not makes sense to me at all. Anyway this gives the result that he wanted


    declare @table1 table
    (
    col1varchar(10),
    col2varchar(10)
    )
    declare @table2 table
    (
    col1varchar(10),
    col2varchar(10)
    )
    insert into @table1
    select'a', 'xy'union all
    select'a', 'as'

    insert into @table2
    select'a', 'op'union all
    select'a', 'qr'

    selectt1.col1, t1.col2, t2.col2
    from
    (
    selectcol1, col2, id = (select count(*) from @table1 x where x.col1 = t.col1 and x.col2 >= t.col2)
    from@table1 t
    ) t1
    inner join
    (
    selectcol1, col2, id = (select count(*) from @table2 x where x.col1 = t.col1 and x.col2 <= t.col2)
    from@table2 t
    ) t2
    ont1.id= t2.id

    /* RESULT :
    col1 col2 col2
    ---------- ---------- ----------
    a xy op
    a as qr

    (2 row(s) affected)
    */



    KH
  7. Madhivanan Moderator

    But I prefer having identity column in the table than generating and using that in query [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  8. rak1791 New Member

    Dear Madhivanan

    Thanking you for replying me.
    Your query will give cross join result.

    it will give total 8 record as follows

    a xy op
    a as qr
    a xy op
    a as qr
    a xy op
    a as qr
    a xy op
    a as qr

    I cannot use set rowcount 2. Because I don#%92t know how many records would be in both the tables. Moreover I am looking for a query, which can run without setting rowcount. My ultimate goal is to create a view.

    Dear FrankKalis

    Thanking you for replying me.

    Actually I have 11 tables with all relationship established. I want to create a view involving all tease tables. I have created some sub views from those sub view I would be creating main view. Among those views, two views are like the example I have given. If situation is like the example I have given, how to solve? That#%92s my question.


    Regards
    Rak
  9. FrankKalis Moderator

    quote:
    it will give total 8 record as follows

    a xy op
    a as qr
    a xy op
    a as qr
    a xy op
    a as qr
    a xy op
    a as qr
    Based on your sample data, the resultset should have 4 rows. What does your statement look like?

    To solve your problem, you should post the table structures, sample data and desired result. Looks like your doing stuff that might be better done at the client or report generator.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  10. rak1791 New Member


    Yea sorry. I will give (2 * 2)4 records.. there was a typing mistake..
  11. Madhivanan Moderator

    Post the table structures of table1 and table2. Do your tables have primary or unique keys?

    Madhivanan

    Failing to plan is Planning to fail
  12. cmdr_skywalker New Member

    Looks like you need an "unique key" so you can know which is the "first" row to select (as Khtan showed using count). I wonder why the "xy qr" or "as op" missing in the cross join result set. Anyway, here is what I found if I use Madhivanan query.

    declare @table1 table(col1varchar(10),col2varchar(10))
    declare @table2 table(col1varchar(10),col2varchar(10))
    insert into @table1 select'a', 'xy'union all select'a', 'as'
    insert into @table2 select'a', 'op'union all select'a', 'qr'

    Select t1.col1,t1.col2,t2.col2
    from @table1 t1 inner join @table2 t2 on t1.col1=t2.col1


    col1 col2 col2
    ---------- ---------- ----------
    a xy op
    a xy qr
    a as op
    a as qr


    May the Almighty God bless us all!
    www.empoweredinformation.com
  13. Madhivanan Moderator

    If there is unique query, you will get proper result

    declare @table1 table(i int identity(1,1), col1 varchar(10), col2 varchar(10))
    declare @table2 table(i int identity(1,1), col1 varchar(10), col2 varchar(10))
    insert into @table1(col1,col2) select 'a', 'xy' union all select 'a', 'as'
    insert into @table2(col1,col2) select 'a', 'op' union all select 'a', 'qr'

    Select t1.col1,t1.col2,t2.col2
    from @table1 t1 inner join @table2 t2 on t1.i=t2.i


    Madhivanan

    Failing to plan is Planning to fail
  14. FrankKalis Moderator

    quote:
    If there is unique query, you will get proper result
    But that's more or less coincidental. This will easily break when another seed or increment value is chosen, rows are deleted....

    Both tables should have a PRIMARY KEY constraint and additionally one table a FOREIGN KEY constraint referencing the other table. So, more or less something like


    CREATE TABLE table1
    (
    i INT identity(1,1) PRIMARY KEY
    , col1 VARCHAR(10)
    , col2 VARCHAR(10)
    )
    CREATE TABLE table2
    (
    id INT IDENTITY(1,1) PRIMARY KEY
    , i INT FOREIGN KEY REFERENCES table1(i) ON DELETE CASCADE
    , col1 VARCHAR(10)
    , col2 VARCHAR(10)
    )

    INSERT INTO table1(col1,col2) SELECT 'a', 'xy' UNION ALL SELECT 'a', 'as'
    INSERT INTO table2(i, col1,col2) SELECT 1, 'a', 'op' UNION ALL SELECT 2, 'a', 'qr'

    SELECT t1.col1,t1.col2,t2.col2
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.i=t2.i

    DROP TABLE table2, table1



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  15. Madhivanan Moderator

    Yes. Thats good point Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  16. cmdr_skywalker New Member

    you only need the first instance of the record to join, is that right? I guess the assumption that the "first" instance of the record is assumed with a lower value of the identity. However, if this is not correct, you should have a PRIORITY column where the PRIORITY dictates the order of precedence and not the IDENTITY column.

    May the Almighty God bless us all!
    www.empoweredinformation.com

Share This Page