SQL Server Performance

Problem with select !

Discussion in 'SQL Server 2005 General Developer Questions' started by DilliGrg, Nov 16, 2006.

  1. DilliGrg Member

    Hi everyone,
    I need help for this sample query. How can I get the result set in one row instead of two rows with some null? I can't update the value null with some data in the table because I don't know the data to update with. Data comes from two different sources. Any help would be greatly appreciated.



    create table #test
    (id int
    ,fname varchar(20)
    ,lname varchar(40)
    ,position char(10)
    ,Salutation varchar(20)
    )

    insert into #test(id, fname, lname)
    values (1, 'Dave', 'Cooper')

    insert into #test(id, position, Salutation)
    values (1, 'Manager', 'Sr')

    select * from #test

    drop table #test


    Current result set:
    id fname lname position Salutation
    ----------- -------------------- ---------------------------------------- ---------- --------------------
    1 Dave Cooper NULL NULL
    1 NULL NULL Manager Sr

    (2 row(s) affected)



    Desired result set:

    id fname lname position Salutation
    ----------- -------------------- ---------------------------------------- ---------- --------------------
    1 Dave Cooper Manager Sr

    (1 row(s) affected)



    Thanks,
    DilliGrg
  2. revive New Member

    Hi,

    You have this:
    1.insert into #test(id, fname, lname)
    values (1, 'Dave', 'Cooper')

    table will be:
    id fname lname position salutation
    --- ------ ------------- ------------ ------------
    1 Dave Cooper Nothing(null) nothig(null)

    2.insert into #test(id, position, Salutation)
    values (1, 'Manager', 'Sr')

    table will be:
    id fname lname position salutation
    --- ------ ------------- ------------ ------------
    1 null null manager sr

    Basicaly you insert 2 data(rows) in the table. The null text apear to you because those filed are nulls. To avoid this you can wrote:
    CREATE TABLE #test(
    id int,
    fname varchar(40) COLLATE Latin1_General_CI_AS NOT NULL,
    position varchar(40) COLLATE Latin1_General_CI_AS NOT NULL,
    ...


    About inserting data you can:

    insert into #test(id, fname, lname, position, Salutation) values (1, 'Dave', 'Cooper', 'manager', 'sr')

    or

    when inserting you can add a where id='1' clause. So on your second insert, you will insert only position and salutation, for all id='1'. Now, depind what you want to do.

    Thanks,

    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  3. DilliGrg Member

    Revive,
    Thanks for the reply. It's not that easy, may be just for me, to get the result set I want. Basically I am looking for select not insert. The insert here was just an example. If the table already has two rows, how can I get as one, that's my question?




    Sample Date:
    id fname lname position Salutation
    ----------- -------------------- ---------------------------------------- ---------- --------------------
    1 Dave Cooper NULL NULL
    1 NULL NULL Manager Sr

    (2 row(s) affected)


    Desired result set:

    id fname lname position Salutation
    ----------- -------------------- ---------------------------------------- ---------- --------------------
    1 Dave Cooper Manager Sr

    (1 row(s) affected)



    Thanks,
    DilliGrg
  4. revive New Member

    Sorry i understand wrong what you look for <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />I'll take a look and i'll answer to you. <br /><br />p.s thanks for the answer about triggers <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /> btw: do you know a application to generate reports about the operation with triggers, in a db(update, delete, insert...)?<br /><br /><u><b>Jack Bower</b></u><br />software developer<br />IATAR Studio<br /<a target="_blank" href=http://iatar.port5.com>http://iatar.port5.com</a>
  5. revive New Member

    Hi,

    I made a quick example for 4 rows added:

    insert into test(id, fname, lname) values (1, 'Dave', 'Cooper')
    insert into test(id, position, Salutation) values (1, 'Manager', 'Sr')
    insert into test(id, fname, lname) values (2, 'Jack', 'Bower')
    insert into test(id, position, Salutation) values (2, 'Manager', 'Sr')


    select t1.fname, t1.lname, t2.position, t2.salutation from test as t1, test as t2 where t1.fname != 'null' and t2.position != 'null' and t1.id = t2.id


    i will have:
    Dave,Cooper,Manager,Sr
    Jack,Bower,Manager,Sr

    Please tell me if this is what you need.

    Thanks,


    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  6. FrankKalis Moderator


    SELECT COALESCE(MIN(fname), MIN(fname))
    , COALESCE(MIN(lname), MIN(lname))
    , COALESCE(MIN(position), MIN(position))
    , COALESCE(MIN(Salutation), MIN(Salutation))
    FROM #test
    GROUP BY id


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. revive New Member

    Hi Frank,

    This one works, cool.
    Can you tell me what COALESCE is, here?

    Thanks,

    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  8. FrankKalis Moderator

    COALESCE is the ANSI SQL version of T-SQL ISNULL() and replaces a NULL marker with a given expression. In DilliGrg's sample data there are always 2 rows where always only one contains data and the other one not. So, when grouping by ID you effectively use COALESCE to merge those two rows into one. Actually I wasn't totally awake this morning when I posted it. There is an even simpler solution:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT <br /> MIN(fname)<br /> , MIN(lname)<br /> , MIN(position)<br /> , MIN(Salutation)<br /> FROM #test<br /> GROUP BY id<br /></font id="code"></pre id="code"><br />MIN() "automatically" removes NULLs. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. revive New Member

    Thanks for response.
    I asked cose in sql i didn't knew to exist 'SELECT COALESCE'.
    The example i gave was made to work in sql.
    So this COALESCE is for MSQL. I mean just for t-sql? Good to remeber that. Thanks again.


    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  10. FrankKalis Moderator

    COALESCE is supposed to be portable as it is ANSI SQL. ISNULL is a Microsoft specific adaption of COALESCE. Both are available in SQL Server. The difference between them is explained in BOL. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  11. Madhivanan Moderator

    >>So this COALESCE is for MSQL.

    No. Only ISNULL is for MS SQL.
    COALESCE is supported in all RDBMS

    Madhivanan

    Failing to plan is Planning to fail
  12. FrankKalis Moderator

    quote:
    COALESCE is supported in all RDBMS
    ...Supposed to be supported...

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  13. revive New Member

    I am playing a lot with php. And if i try do the same, under php, SELECT COALESCE isn't recognited.
    Here i have to use somethign like this: select t1.fname, t1.lname, t2.position, t2.salutation from test as t1, test as t2 where t1.fname != 'null' and t2.position != 'null' and t1.id = t2.id.

    I will try here SELECT MIN(fname)... to see if its works

    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  14. FrankKalis Moderator

    NULL != 'null'! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />You surely can use the queries above under PHP against SQL Server. You don't use MySQL, do you?<br />If it doesn't work, please start a new thread. We don't want to hijack this one here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. DilliGrg Member


    Thanks all for the reply. I used Frank's second approach without COALESCE, and looks like it is working. I will have to wait though until it is completely tested.

    Thanks,
    DilliGrg
  16. Madhivanan Moderator

    quote:Originally posted by revive

    I am playing a lot with php. And if i try do the same, under php, SELECT COALESCE isn't recognited.
    Here i have to use somethign like this: select t1.fname, t1.lname, t2.position, t2.salutation from test as t1, test as t2 where t1.fname != 'null' and t2.position != 'null' and t1.id = t2.id.

    I will try here SELECT MIN(fname)... to see if its works

    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
    Try
    select t1.fname, t1.lname, t2.position, t2.salutation from test as t1, test as t2 where t1.fname is not null and t2.position is not null and t1.id = t2.id

    Madhivanan

    Failing to plan is Planning to fail

Share This Page