SQL query to get specific rows based on one value

Question

ID        Place          Name                Type              Count
--------------------------------------------------------------------------------
7718    | UK1   |  Lemuis           | ERIS TELECOM          | 0
7713    | UK1   |  Astika LLC       | VERIDIAN              | 34
7712    | UK1   |  Angel Telecom AG | VIACLOUD              | 34
7710    | UK1   |  DDC S.r.L        | ALPHA UK              | 25
7718    | UK1   |  Customers        | WERTS                 | 0

Basically I have a variable and I want to compare that variable the the 'Type' column. If the variable matches the type then I want to return all the rows that have the same ID as the variable's ID.

For example, my variable is 'ERIS TELECOM', I need to retrieve the ID for 'ERIS TELECOM' which is 7718. Then I search the table for rows that have the ID 7718.

My desired output should be:

Table Name: FullResults

ID        Place          Name                Type              Count
--------------------------------------------------------------------------------
7718    | UK1   |  Lemuis           | ERIS TELECOM          | 0
7718    | UK1   |  Customers        | WERTS                 | 0

Is there a query that will do this?

crodie · Accepted Answer · 2014-01-06 14:38:43Z

2

SELECT *
FROM FullResults
WHERE ID = (SELECT ID 
            FROM FullResults
            WHERE Type= @variable);

I guess it will be something like this?

answered Jan 6, 2014 at 14:38

crodie

1121 gold badge1 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

oerkelens · Accepted Answer · 2014-01-06 14:39:09Z

1

Something like this should do the trick, returns all data for all ID's that have a matching type.

SELECT *
 FROM Table
WHERE ID
   IN (SELECT ID from Table where Type = 'ERIS TELECOM')

answered Jan 6, 2014 at 14:39

oerkelens

5,1711 gold badge25 silver badges30 bronze badges

Comments

Sanket J · Accepted Answer · 2014-01-06 16:52:36Z

1

You can create a stored procedure for this where you will pass a variable while executing the stored procedure.

create proc dbname.dbo.usp_get_date_from_type_value 
(
   @type_value varchar(50)
 )
as
begin

       select ID, Place, Name, Type, Count
       from dbname..table
       where ID in (select ID from dbname..table where type = @type_value)

end

Then you can run the following statement.

Exec  dbname.dbo.usp_get_date_from_type_value @type_value = 'ERIS TELECOM'

edited Jan 6, 2014 at 16:52

answered Jan 6, 2014 at 14:41

Sanket J

1871 gold badge1 silver badge12 bronze badges

Collectives™ on Stack Overflow

SQL query to get specific rows based on one value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related