.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

multiple join in one statement

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

1. select -----

from table1 t1 join table2 t2 on t1.a=t2.a

left join table3 t3 on t1.a=t3.b

2. select -----

from table1 t1 join table2 t2 on t1.a=t2.a

left join table3 t3 on t2.a=t3.b


I'm not sure about the behavior of above 2 statements. What kind of result do i expect?

When should i use both kind of joins?


View Complete Post

More Related Resource Links

how to multiple condition in if statement in asp.net C#



i am using if condition to find out a time

if(time=="08 AM)


response.write"shift A";


but problem is how can i put multiple condition in if like

if(time=="06 AM" and "08 AM" and "07 AM")

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

Update Statement help from Multiple database

Hi I am having two separate datbase on same sever one datasbase name private from where i am executing my below query and other name is ODS Table name in Private=[A].[DimCapabilitySpecialty] Table name in ODS = [A].[CapabilitySpecialty]   both of them having column SpecialtyCd   i want to update the flag value of ODS table where there is particular specialitycd missing in source means table in private database   so for that i wrote below query   update A.DimCapabilitySpecialty set ActiveInd ='I' where SpecialtyCd !=[ODS].[A].[CapabilitySpecialty].[SpecialtyCd]   But it is giving me this error   Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "ODS.CapabilitySpecialty.SpecialtyCd" could not be bound.

using join when a column may have multiple values

Have 2 tables. Table A has among several columns one called "product_code," which contains 4-digit numerals. Table B has just 2 columns, "product_code," the same 4-digit numerals used in the same column in Table A, and "product_description," which includes a VARCHAR string describing the product referenced by the code. I'm querying Table A and trying to include the "product_description" from Table B with each record returned. Am using a LEFT JOIN like this: SELECT  * FROM Table_A LEFT OUTER JOIN Table_B ON Table_A.product_code = Table_B.prod_code This works fine EXCEPT in cases where Table A has more than one value in "product_code," in which case I get no match in Table B and "NULL" is returned for "product_description." When there is more than one value in the "product_code" column in Table A for a particular record, the values are separated by commas (for example: 1002,1003,9856). How can I get this to work so that for records that have multiple produce codes in table A I get multiple product descriptions from Table B?   Thanks      

Cannot get distinct records from a multiple inner join request


Hello everybody,

This is my first post here because this request is giving me serious headache.

What I'm trying to do : This is a small search engine. I ask SQL Server 2008 to return the 20 records I need in a specific page from a larger set of 1000 records. Then I want it to return only distinct records on the key t_ye.numenr (VID).

This returns doubled or tripled records. What am I doing wrong ?



declare @explicitMaximum int <br/>
declare @chosenPage int <br/>
declare @recordsPerPage int <br/>
set @explicitMaximum = 1000 <br/>
set @chosenPage = 1 <br/>
set @recordsPerPage =20; <br/>
WITH X AS (<br/>
SELECT DISTINCT TOP(@explicitMaximum) t_ye.numenr AS VID, <br/>

LINQ to Entity - using join for multiple table


I have my SQL query which needs to be conerted to Linq to Entity,

select pfr_sa.SID,pfr_sa.SourceGroupID,tx.txroll_cadaccountnumber,tx.txroll_StreetNumb

It is possible to alter multiple columns within a single alter table statement?


It is possible to alter multiple columns within a single alter table statement?

I tried & searched not getting it.

Alter table au_de alter column m_user char(9),c_user char(9)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.


Alter table au_de alter column m_user char(9),alter column c_user char(9)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Need help with SELECT statement for multiple tables


Here's my current SELECT statement:

SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId

This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script.  There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table).  I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site.

So how can I adjust that SELECT statement so this one is combined with it somehow?:

SELECT Email FROM ExtraEmails WHERE Rules = 'true'

help with multiple tables join


hi all,

i am having trouble with selecting data across tables.

When practitioner login, it has to automatically match the GPNo with the UserName

string name = HttpContext.Current.User.Identity.Name;

SELECT  Practitioner.GPNo FFROM Practitioner WHERE Practitioner.UserName=@UserName

And then, it has to select the date from Consultation Table

SELECT Consultation.Date FROM Consultation WHERE Practitioner.GPNo=Consultation.GPNo

and also the patient first name and last name

SELECT Patient.firstName, Patient.lastName, FROM Patient WHERE Patient.PatientID=Consultation.patientID

is it possible to combine these statement into one statement with JOIN?

thank you

Patient Table 

patientID firstName lastName
Consultation Table



Pracitioner Table  =============
GPNo UserName

Insert multiple rows with a single INSERT statement


With SQL Server 2005 Express coming out, I have switched from MySql.  I'm having trouble with a very simple INSERT statement that has previously worked in both MySql and Oracle.  The statement is as follows:

INSERT INTO pantscolor_t (procode,color,pic) VALUES
 ('74251', 'Black', '511black.jpg'),
 ('74251', 'Charcoal', '511charcoal.jpg'),
 ('74251', 'Khaki', '511khaki.jpg'),
 ('74251', 'Navy', '511navy.jpg'),
 ('74251', 'OD Green', '511odgreen.jpg');

However, when I attempt to execute this statement with Management Studio Express I get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Any ideas?

SQL Server 2005 Multiple Join



I have 4 tables of

  • Clnt(for Clients)
  • Pol (for insurance policies)
  • addrs(for address insured)
  • cov(coverage) 

Tables relationships as followed:

the only field to link Cov and Addrs is LocationID

For each policy (depending on type of policy) we may or may not have an "insured address". also we may or may not have a coverage.

I need to write a query to get the list of all Insurance policies with the name and customerId of the client. I want to make sure I get the list of all policies. now if there's an address under a policy I should get the address too and if there's a coverage under a policy I should get the coverage.

If it was only the coverage I would write something like this:

select distinct 








from clnt,pol

left join cov on cov.polID = pol.polID 



Join two tables on multiple columns


SQL Server 2008 R1

I have been successfully joining 2 tables on multiple fields with TSQL like:

SELECT a.* FROM a JOIN b ON b.field1 = a.field2 AND b.field3 = a.field4

My questions is:

1. Is this the best way to do this?

2. Are there limitations on how many fields you can JOIN ON or the size of these fields?

Now the specifics.

This application is inserting property conveyances (sales) from a source file (CombList), possibly with field corrections (CombListChanges). There are over 500,000 records in the source file each with a matching correction record. The UPDATE (shown below) updates the SourceID of the inserted records (approximately 350,000). The Conveyance INSERT (not shown) and the Conveyance UPDATE (shown below) each took 3-5 minutes to run.

The second query is an INSERT matching the Conveyances with a Real Property record. As written it runs for more than 2 hours and expands the tempdb to 10s of Gigabytes in size. I have never had it successfully execute. However if I run just the SELECT portion and eliminate the deedamount (money) and deedtype (nvarchar(10)) JOINs it runs in about 30 seconds (and gives me slightly too many records). The SourcePage (nvarchar(100)) is made up of a concatenation of several fields and text.

3. Any idea on why this INSERT won't run in a reasonable time?

Writing multiple if statement in where clause in Stored Procedure



I have one Stored Procedure which returns some value. now i have some different conditions in my select query so can i write them in my where clause instead of writing the whole select query again for different conditions? 


My Current Select Statement in SP looks like :








Now i have two more conditions in my Select Statement that are

@ProductList varchar(max),

@StatusList Varchar(max)

and they gets the list of id of their name from another function that is made for them respectively fun_GetProductId and fun_GetStatusId

so with this my Select Statement in SP will look like 






     (UniqueRef='@UniqueRef' or @UniqueRef='') and 

    (Productid in (Select * From fun_GetProductId(@ProductList))) and

   (StatusId in (Select * From fun_GetStatusId(@StatusList))) 

Selecting data from multiple tables whilst using a union statement.



any help on this would be great.

I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different  database that contains information regarding the group of the data.

I so far have the following:

SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx

I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.

Thanks again,


How to find the statement which causes a 'FailedOperationException' when multiple commands are exe


When executing a large .sql file contents, which creates a lot of objects on a database, running the ExecuteNonQuery over a Database object, we're getting a FailedOperationException. Is there a way to find which of the SQL statements passed in the string is causing the exception, and have some more 'familiar' error message?

Are there other options to run the multiple T-SQL statements created with the SQL Server Management Studio database script generation feature?

We are using the SMO on a .NET Fx 3.5 app running against a SQL Server 2008 instance on a 32 bits computer.

Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt

Multiple calculations in a single Statement in SQL Ce


Hi guys

I have a table that has a date, amount of litres, price per litre. The table is updated every month, what I have been trying to do is display the total revenue for each year ( amount of litres * price per litre this will give me the revenue for each month and then add each month together to return that years revenue) I can't seem to get this statement right, any tips?



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend