Optimized Query for inner Join

Question

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 11-Sep-13 0:14am

HimanshuJain2409

Updated 11-Sep-13 21:15pm

v3

Add a Solution

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

4 solutions

Solution 1

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]

Posted 11-Sep-13 8:48am

Maciej Los

Updated 11-Sep-13 21:07pm

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

Solution 2

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.

Posted 11-Sep-13 20:58pm

Bernhard Hiller

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!

Solution 4

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

Posted 11-Sep-13 21:51pm

Fredrik Bornander

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

HimanshuJain2409 · Accepted Answer · 2013-09-11T21:41:00

Solution 3

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)

Posted 11-Sep-13 21:41pm

HimanshuJain2409

Comments

Herman<T>.Instance 12-Sep-13 5:56am

you could also use Row_Number() OVER() function