• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

query all the ID's in another table (basic)

Hi,

How can we return all the ID  have a match in another table?

select * from table1 where ID is in (select ID from table2 where ID= @ID)

Thanks.
0
EdwardPeter
Asked:
EdwardPeter
  • 4
  • 3
  • 2
2 Solutions
 
stevetheskiCommented:
you can do it like so since SQL Server doesn't offer an INTERSECT COmmand

select USE pubs
SELECT *
FROM authors
WHERE EXISTS
   (SELECT 1
   FROM publishers
   WHERE authors.city = publishers.city)

SteveTheSki
0
 
stevetheskiCommented:
if you will have to do this alot you can use a udf to make things easier to maintain.  also if you need to add parms it makes things easier

CREATE FUNCTION udf_Distinctids ( @MinId INT, @MaxId INT)
RETURNS TABLE
AS
RETURN
      SELECT CategoryID
      FROM Categories
      WHERE CategoryID BETWEEN @MinID AND @MaxId

SELECT DISTINCT CategoryId
FROM Products p
WHERE EXISTS
      (SELECT *
      FROM dbo.udf_Distinctids(1,3) DIds
      WHERE p.CategoryId = DIds.CategoryId)
0
 
dbeneitCommented:
SteveTheSki,
you shouldn't use  authors.city in  a subselect. That procuces one select for row.
The solution is a inner join
SELECT au.*
FROM authors au inner join publishers pb
on authors.city = publishers.city
where ..... any condition

Edward,

select t1.* from table1 t1 inner join table2 t2 on t1.ID= t2.ID
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
EdwardPeterAuthor Commented:
dbeneit,

what will change if we have a query like,

select * from table 1 a
join table2 b on  a.ID=b.ID
join table3 c on c.ID =a.ID
Where a.ID in (select ID from table 4 where ID = 1)   <--------this part

Thanks.

0
 
dbeneitCommented:
Edward,

I don't understand , the problem was use fields from outside the subselect in the subselect
if "1" is a constant in ID=1 and ID is the result of subselect.... you should use

Where a.ID=1

if 1 is another field of t1,t2 or t3 or you convert in a join, then

select a.*,b.*,c.* from table1 a
inner join table2 b on  a.ID=b.ID
inner join table3 c on c.ID =a.ID
inner join table4 d on a.ID=d.ID
Where d.ID=1
0
 
dbeneitCommented:
the unique diference  in this case is if you have several rows of table4 with ID=x
that produce a duplicate rows.

in this case you can use :

 select distinct a.*,b.*,c.* from table1 a
inner join table2 b on  a.ID=b.ID
inner join table3 c on c.ID =a.ID
inner join table4 d on a.ID=d.ID
Where d.ID=1

or
select distinct a.*,b.*,c.* from table1 a
inner join table2 b on  a.ID=b.ID
inner join table3 c on c.ID =a.ID
inner join (select distinct ID from table4) d on a.ID=d.ID
Where d.ID=1

or if "1" isn't a field of a,b or c tables then
select distinct a.*,b.*,c.* from table1 a
inner join table2 b on  a.ID=b.ID
inner join table3 c on c.ID =a.ID
inner join (select distinct ID from table4 Where ID=1) d on a.ID=d.ID




0
 
EdwardPeterAuthor Commented:
dbeneit,

Can you kindly assist in :

http://experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21416010.html

Please let me know if you can.

Thanks.
0
 
stevetheskiCommented:
dbeneit

Sometimes joins are better and sometimes they are not.
just for clarification purposes i am not using a subselect (that is a correlated subquery) as you are defining i am using an EXISTS which is a SEToperator.  Please look at any SQL optimizer book and you will see there is a HUGE difference.  depending on the number of rows and the types of indexes if any one may outperform the other.  Also when dealing with huge datasets I find it is easier to further manually optimize a SET than an inner join by using hints.  especially if the queries get more complicated and multiple indexes need to be used

a snippet from the following link
http://www.sql-server-performance.com/rd_interview.asp

What are some techniques for writing fast performing stored procedures?

Fast performing stored procedures are like several other areas within T-SQL. Revisiting stored procedures every six months or so, to ensure that they are still running at their optimum performance is essential. However, actual techniques themselves include working with as short a transaction area as possible, as lock contention will certainly impact performance. Recompiling your stored procedures after index additions if you are unable or not wishing to restart SQL Server, will also ensure that a procedure is using the correct index, if that stored procedure is accessing the table which has received the new index.

If you have a T-SQL command that joins several tables, and it takes a long time to return a value, first of all check out the indexes. But what you may find tends to help, is to break down the code and try to determine which join it is that is causing the performance problem. Then analyze this specific join and see why it is a problem.

Always check out a stored procedure's performance as you build it up by using the SHOWPLAN commands.

Also, try to use EXISTS, rather than a JOIN statement. An EXISTS statement will only join on a table until one record is found, rather than joining all the records .

Also, try to look at using subqueries when you are trying to find a handful of values in the subquery statement, and there is no key on the column you are looking up on.

0
 
dbeneitCommented:
stevetheski,

Ok.
 I have had greatters problems with Exist or IN (subquey) when we have used a field out of subquery inside its. I have had  times one hundred uppers than joins structures.

I think that in others querys could run... but I try no use EXIST or IN.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now