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


Top 5 Contributors of the Month
Imran Ghani
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#

  

sir

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 ?

Thanks

 

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/>
ROW_NUMBER() 

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
=============

Date

patientID

GPNo
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

  

Hi,

I have 4 tables of

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

Tables relationships as followed:

Clnt--->Pol----->Addrs
Clnt--->Pol----->Cov
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 



clnt.CustomerID, 

clnt.Name, 

pol.policyID, 

pol.productId, 

pol.Expiry,

cov.name, 

cov.Limit,



from clnt,pol

left join cov on cov.polID = pol.polID 

where 

clnt.Custome

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

  

Hi,

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? 

Like 

My Current Select Statement in SP looks like :

Select

         FName,LName,UniqueRef,City

From 

       tblContact

Where 

     UniqueRef='@UniqueRef'

 

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 

Select

         FName,LName,UniqueRef,City

From 

       tblContact

Where 

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

  

Hi,

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,

Jas


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?

Thanks

Paul




Categories: 
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