In one select statement, how to fetch specific name with its surname?

Question

For example,

declare @name varchar(10)
set @name = 'John'

Here my table:

| name     |   surname   |
|----------|-------------|
| John     |  Black      |
| John     |  Reese      |
| Kevin    |  Love       |
| Lionel   |  Messi      |

i need to fetch John Black! if name is john, then check lastname in one Select query. else fetch the others. Thank you!

i'm sorry, i couldn't clarify myself. Let me try once more.

First, my variable is 'John'. If name is john, i want to fetch the records whose name is John but at the same time i want to update those lastnames into 'BLACK'

So you want the result set from the query that returns all name = 'john' to be used in another procedure? — Jacob H, Aug 24 '17 at 15:30
select * from mytable where name = @name and surname = 'black' — GuidoG, Aug 24 '17 at 15:30
How about: `select name, surname from table where name='John' and surname='Black'` — bezet, Aug 24 '17 at 15:31
Do you mean you want to return the TOP result based on firstname? Or do you want to return all results where firstname is john? this is relatively confusing. Why not just use a second parameter for surname? then you could use coalesce to return all if surname parameter is empty — Master Yoda, Aug 24 '17 at 15:33
please edit our question and include the outcome you expect. Maybe then we can understand your question — GuidoG, Aug 24 '17 at 15:48
@CelbisH. So whats happening? did any of the answers provide a solution to your question? — Master Yoda, Aug 25 '17 at 08:58

score 0 · Answer 1 · answered Aug 24 '17 at 15:33

0

You can use order by:

select top 1 t.*
from t
where t.name = 'John'
order by (case when t.surname = 'Black' then 1 else 2 end);

This does all the logic in a single query.

answered Aug 24 '17 at 15:33

Gordon Linoff

1,242,037
58
646
786

statement says "*fetch the others*" suggesting more than 1 record – Dalorzo Aug 24 '17 at 15:37

Master Yoda · Answer 2 · 2017-08-24T16:22:36.830

Why not include a second parameter for surname?

You can use the coalesce() method to determine if surname is empty or null. If its not empty it will return all records matched with both surname and forename. If surname is null it will return all record that match with forename ('john')

DECLARE @forename VARCHAR(10)
SET @forename = 'John'

DECLARE @surname VARCHAR(10) = NULL

SELECT       t.*
FROM         yourTable t
WHERE        t.forename = @forename
AND          (t.surname = COALESCE(@surname, t.surname) OR @surname IS NULL)

UPDATE

From your latest update it sounds like you just want to update the surname for all records with forename 'john' using the surname of the first record returned.

DECLARE @forename VARCHAR(10)
DECLARE @surname VARCHAR(10) = NULL
SET @forename = 'John'

--get surname of first record matching name 'john'
SET @surname = 
(
   SELECT TOP 1 surname
   FROM YourTable
   WHERE forename = @forename
)

--update all surnames of records in your table with name 'john'
--to surname of first matched record returned
Update YourTable
SET  surname = @surname
WHERE forename = @forename

Simon · Answer 3 · 2017-08-24T16:14:32.213

0

UPDATE 
fooTable
SET
surname = 'Black'
FROM
fooTable
WHERE
name = @name

SELECT * FROM fooTable

I think this is what OP is asking for, but I am not certain.

For reference: How do I UPDATE from a SELECT in SQL Server?

edited Aug 24 '17 at 16:14

answered Aug 24 '17 at 15:49

Simon

1,201
9
18

Close, but i think they want to update the surname of all matching records based on the top result returned using the @name parameter – Master Yoda Aug 24 '17 at 16:00
@MasterYoda My apologies, the force must not be with me today. What do you mean based on the top result? If 'John' is returned as a top result, and it updates all surnames that match the top result, how will that be different than updating all surnames that return with the match 'John'? Please enlighten! :-) – Simon Aug 24 '17 at 16:14
1

the first pass should get all results for name 'john' it should then get the surname for the first result only. Using this surname he wants to update all other 'john' results with the surname from the first result. I think thats what hes after. Hard to say :) – Master Yoda Aug 24 '17 at 16:17

score 0 · Answer 4 · answered Aug 24 '17 at 16:13

Here are two solutions that give you the desired result. The second one uses pure SQL to get the return set, whereas the first uses a combination of T-SQL and SQL.

create   procedure [dbo].[getName] @name varchar(100), @surname varchar(100)
as
begin

declare @l_found smallint;

select @l_found = count(*) 
  from nameTable 
 where name    = @name 
   and surname = @surname;
print 'found: ' + cast(@l_found as varchar)

if @l_found = 0  
   select * 
     from nameTable 
    where name = @name;
else 
   select * 
     from nameTable 
    where 
     name = @name 
      and surname = @surname;

end;

Alternative

alter   procedure [dbo].[getName] @name varchar(100), @surname varchar(100)
as
begin

with foundit as ( select count(*) c 
                    from nameTable 
                   where name    = @name 
                     and surname = @surname )
select * 
  from nameTable 
 where name    = @name 
   and surname = @surname
union all
select * from nameTable
where name = @name 
  and 0    = (select c from foundit);
end;

In one select statement, how to fetch specific name with its surname?

4 Answers4