SQL Server Performance Forum – Threads Archive

Problem with select !

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

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

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

Sorry i understand wrong what you look for <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I’ll take a look and i’ll answer to you. p.s thanks for the answer about triggers <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> btw: do you know a application to generate reports about the operation with triggers, in a db(update, delete, insert…)? Jack Bower software developer IATAR Studio http://iatar.port5.com</a>

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

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

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

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: <pre id="code"> SELECT MIN(fname) , MIN(lname) , MIN(position) , MIN(Salutation) FROM #test GROUP BY id </pre id="code"> MIN() "automatically" removes NULLs. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

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

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=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

>>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

quote:
COALESCE is supported in all RDBMS

…Supposed to be supported… —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

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

NULL != ‘null’! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] You surely can use the queries above under PHP against SQL Server. You don’t use MySQL, do you? 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=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

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

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

]]>