Click here to Skip to main content
15,301,414 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hello EveryOne

I have Two Table TableA and TableB

Table A contains
AID | Description
1    |  ABC
2    |  DEF
3    |  GHI
4    |  JKL


TableB Contains
BID| AIDRefrence
1    |  1
2    |  1
3    |  2
4    |  2
5    |  6
6    |  2


My Output Table must be like This
TableC
AID | BID | Description
1 | 1| ABC (BID(1,2) Can be Any one of AIDRefrence 1)
2 | 3| DEF (BID(3,4,6) Can be Any one of AIDRefrence 2)

My Code is Like
SQL
Select a.AID, b.BID, a.description
    From (Select * from Table B where BID in (select max(BID) from TableB group By AIDRefrence )) B inner Join TableA A
    on a.aid = b.AIDRefrence


Only Problem That I face is thet TableB contains More then 50K rows and on GroupBy it returns atleast 15K rows.
Can Some one Optimize this Query.
Posted
Updated 11-Sep-13 21:15pm
v3
Comments
Herman<T>.Instance 11-Sep-13 7:11am
   
SQL server, Oracle, MySQL?
HimanshuJain2409 11-Sep-13 8:30am
   
SQL Server
Fredrik Bornander 11-Sep-13 9:53am
   
Your query makes no sense, alias A will not have column AID on it. What does the query for populating TableC actually look like?
HimanshuJain2409 12-Sep-13 2:24am
   
Table A contains AID and Description. Table C is the Required output Which I want to generate.Its code is mentioned in last
Fredrik Bornander 12-Sep-13 2:47am
   
In your query A is an alias for a select hitting only table TableB, that doesn't work as that table does not have column AID. Your example query is incorrect as it is clearly only hitting TableB.
HimanshuJain2409 12-Sep-13 3:09am
   
Reply Really Sorry For that
Select a.AID, b.BID, a.description
From TableA A inner Join (Select * from Table B where BID in (select max(BID) from TableB group By AIDRefrence )) as b
on a.aid = b.AIDRefrence

I'm not sure what you want to achieve, but see below example:
SQL
DECLARE @tab1 TABLE (AID INT IDENTITY(1,1), aDescription VARCHAR(30))

INSERT INTO @tab1 (aDescription)
SELECT 'ABC'
UNION ALL SELECT 'DEF'
UNION ALL SELECT 'GHI'
UNION ALL SELECT 'JKL'


DECLARE @tab2 TABLE (BID INT IDENTITY(1,1), AIDReference INT)

INSERT INTO @tab2 (AIDReference)
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 6
UNION ALL SELECT 2

SELECT a.AID, b.BID, a.aDescription
FROM @tab1 AS a INNER JOIN @tab2 AS b ON a.AID = b.AIDReference


Result:
1   1   ABC
1   2   ABC
2   3   DEF
2   4   DEF
2   6   DEF


For furhter information, please, see: Visual Representation of SQL Joins[^]

[EDIT]
If you want to enumerate all BIDs for each AID in one row, please, see this:

SQL
SELECT a.AID, a.aDescription, STUFF( (SELECT',' + CONVERT(VARCHAR(30), b.BID) AS 'text()'
                  FROM @tab2 AS b
                  WHERE a.AID = b.AIDReference
                  FOR XML PATH('')), 1, 1, '') AS [BIDs]
FROM @tab1 AS a
GROUP BY a.AID, a.aDescription


Result:
C#
AID Desc.. BIDs
1   ABC    1,2
2   DEF    3,4,6
3   GHI    NULL
4   JKL    NULL



[/EDIT]
   
v2
Comments
HimanshuJain2409 12-Sep-13 2:20am
   
We can not apply Union as table1 Data is Unknown
Maciej Los 12-Sep-13 2:25am
   
What?
Have a look at example one more time. I use UNION statement to insert data into temporary table. What you need is to use last SELECT statement.
HimanshuJain2409 12-Sep-13 2:37am
   
Maciej Your Query was Great...
But I need Only one row for same B.AidRefrence
If you will check this Query like
"SELECT a.AID, b.BID, a.aDescription,b.AIDReference
FROM @tab1 AS a INNER JOIN @tab2 AS b ON a.AID = b.AIDReference"
you will get
a.AID b.BID a.aDescription b.AIDRefrence
1 1 ABC 1
1 2 ABC 1
2 3 DEF 2
2 4 DEF 2
2 6 DEF 2

While I need NonDuplicating a.Aid and B.AIDRefrence
Maciej Los 12-Sep-13 3:00am
   
They aren't duplicated. When a.AID is equal 1 then b.BID is equal 1, 2. When a.AID is equal 2 then b.BID is equal 3, 4, 6. And so on. Please, be more specific. Do you want to enumerate all BID's for each AID in one row?
AID | BID
1 | 1,2
2 | 3,4,6
HimanshuJain2409 12-Sep-13 3:13am
   
Yes Maciej But I just want Only one BID Not all I have to find more columns on this Basis
Maciej Los 12-Sep-13 3:23am
   
Than use MAX:
SELECT a.AID, MAX(b.BID) AS MaxOfBID, a.aDescription
FROM @tab1 AS a INNER JOIN @tab2 AS b ON a.AID = b.AIDReference
GROUP BY a.AID, a.aDescription
HimanshuJain2409 12-Sep-13 3:33am
   
Maciej It will work only in case I have to use just these 2 Columns, But I Need to Fetch More columns With same ID.
Maciej Los 12-Sep-13 3:44am
   
Sorry, but i don't get you ;(
Since TableB contains only BID and AIDRefrence, your inner query is unnecessarily complicated.
Replace
SQL
(Select * from TableB where BID in (select max(BID) from TableB group By AIDRefrence )) A 

by
SQL
(select AIDRefrence as AID, max(BID) as BID from TableB group By AIDRefrence ) A

By the way, there must be some error in the code you show us: Select ... a.description - a does not contain a column "description", it is in TableA.
   
Comments
HimanshuJain2409 12-Sep-13 3:17am
   
Yes Bernhard! I interchanged TableA A with InnerJoin Query. My Mistake I have updated the Query.
I have to fetch multiple Columns from tableB so i used above Query.
Bernhard Hiller 12-Sep-13 3:38am
   
If you do not explain the problem correctly, you need not expect to get answers really solving the issues!
Thanks All But I have Solved this Query myself.
SQL
Declare @tbl Table(BID int)

Insert Into @tbl
Select Max(b.BID) from TableA a inner join TableB b on a.aid = b.AIDrefrence
group By b.AIDRefrence

Select a.AID, b.BID, a.description
From tableB B inner Join TableA A
    on a.aid = b.AIDRefrence
where b.bid in (Select * from @tbl)
   
Comments
Herman<T>.Instance 12-Sep-13 5:56am
   
you could also use Row_Number() OVER() function
Try excluding the inner nested select of the join.
Change
SQL
Select a.AID, b.BID, a.[Description]
from TableA as A
inner Join (Select * from TableB where BID in (select max(BID) from TableB group By AIDReference )) as B on A.aid = B.AIDReference

to
SQL
select a.AID, b.MaxBid as BID, a.[Description] from TableA as A
inner join (select max(BID) as MaxBid, AIDReference from TableB group By AIDReference) as B on B.AIDReference=A.AID


It will reduce the execution plan from 9 nodes to 5, and should take around 25% out of the execution time (depending on your data of course).

Hope this helps,
Fredrik
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900