SQL Server - Inner Join Problem?

Asked By Prabhakaran on 06-Jan-12 06:27 AM

hi friends,

i have query using inner join, between the query, based on column condition i have append the remaining inner join query..

how to do it in sql server 2005

thanx in advance..

Suchit shah replied to Prabhakaran on 06-Jan-12 06:32 AM

You can just do it like

select tab1.col1 , tab2.col1,tab2.col2 from Tab1 INNER JOIN
tab2 ON tab1.Id = tab2.ID
where tab1.col3 = "something"

in this way u can do it

smr replied to Prabhakaran on 06-Jan-12 06:44 AM

hi

Below is a Example for SQL inner join, SQL Left Join and SQL Right Join.

GuestID	Name
1001	Nick
1002	Jack
1003	Apple

Table A

Product	GuestID
Table	1002
Chair	1003
Almari	1002

Table B

SQL inner join
Example for SQL inner join
- Join more than 1 table to get other table data
1. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID
Result :

Name	Product
Jack	Table
Apple	Chair
Jack	Almari

2. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID WHERE B.Product = 'tABLE'

Result:

Name	Product
Jack	Table

smr replied to Prabhakaran on 06-Jan-12 06:45 AM

hi

Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.

USE AdventureWorks;

GO

SELECT DISTINCT pv1.ProductID, pv1.VendorID

FROM Purchasing.ProductVendor pv1

INNER JOIN Purchasing.ProductVendor pv2

ON pv1.ProductID = pv2.ProductID

AND pv1.VendorID = pv2.VendorID

ORDER BY pv1.ProductID

Web Star replied to Prabhakaran on 06-Jan-12 06:53 AM

You query is not clear what you want actually?
Confusion point is "based on column condition i have append the remaining inner join query ".

I think as you know well how can do inner join
eg
Select t1.*, t2.* From tblname t1 INNER JOIN tblnameothertable t2 ON t1.id = t2.id

If you want to append more condition with that inner join than simply add as follow

Select t1.*, t2.* From tblname t1 INNER JOIN tblnameothertable t2 ON t1.id = t2.id AND t1.Code = T2.Code

If you want to append other query than try this

Select t1.*, t2.* From tblname t1 INNER JOIN tblnameothertable t2 ON t1.id = t2.id AND t1.Code = Select Code From othertabel where id = @id)

you can also use inner join within subquery condition if you want

Prabhakaran replied to Web Star on 06-Jan-12 07:09 AM

k..

for example a table contains projectid, fileid, qaid,etc..

inner join a.projectid = b.projectid
if qaid<>'' i ill use some inner join query other wise i omit this query
where <some condition>...

Web Star replied to Prabhakaran on 06-Jan-12 07:58 AM

if you want to run inner join query based on condition than simply use as folllows

if( @qaid <> '')
Begin
--put here your inner join query as follwos
Select t1.*, t2.* From tblname t1 INNER JOIN tblnameothertable t2 ON t1.id = t2.id AND t1.Code = Select Code From othertabel where id = @id)

End
Else
Beigin
-- if you want to run any thing here placed otherwise left blank.
End

F Cali replied to Prabhakaran on 06-Jan-12 08:02 PM

Based on your question, it looks like you are doing some kind of searching function from your tables. Here's one way of doing it:

SELECT *
FROM [dbo].[Table1] A INNER JOIN [dbo].[Table2] B
ON A.[ProjectID] = B.[ProjectID]
INNER JOIN [dbo].[Table3] C
ON B.[FileID] = C.[FileID]
WHERE A.[QAID] = @QAID OR @QAID = ''

Regards,
http://www.sql-server-helper.com/sql-server-2012/index.aspx http://www.sql-server-helper.com/fr/message-d-erreur/message-1-500.aspx http://www.sql-server-helper.com/error-messages/msg-1-500.aspx