.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

problem with inner join

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi, I'm trying to create an sql query with multiple inner joins, but I can't manage this. I have 3 tables (bookings, accountplan, costs), where the data come from. In accountplan are hold the accounts, in costs the cost codes and their names, in bookings the booked events. Based on a booking event, I need the account name, the name of the cost code and all data from bookings (I need the cred account name too, but I can't figure out yet, how I can get this) I tried this: "SELECT bookings.*, accountplan.accountname, costs.costname FROM bookings INNER JOIN accountplan ON bookings.debaccount=accountplan.account INNER JOIN costs ON bookings.costcode=costs.costcode" It is here a where clause too, but I tested it, it's ok. The above query results no rows. If I remove the costs table and the inner join with it, it works. What am I missing?

View Complete Post

More Related Resource Links

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

problem in join two pages with session

hi every body I have two web pages one for inbox messages and second for details for one message in first page I have gridview include name the sender , address the message and details message alongside with buttonfield to go  to another page to view  full message the code of gridview<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1" Width="648px" onrowcommand="GridView1_RowCommand"> <Columns> <asp:BoundField DataField="sender" HeaderText="sender" SortExpression="sender" /> <asp:BoundField DataField="address" HeaderText="address" SortExpression="address" /> <asp:BoundField DataField="details" HeaderText="details" SortExpression="details" /> <asp:ButtonField Text="More..." CommandName="Show_More" /> <asp:TemplateField ShowHeader="False"> <ItemTemplate> </ItemTemplate>

Problem with CTE return only last 5 dates , Join on dates ?

Declare @todate datetime, @fromdate datetime Select @fromdate=DateAdd(dd, DateDiff(dd, 0, GetDate()), 0) ,@todate=DateAdd(dd, DateDiff(dd, 0, @fromdate) -5, 0) -- select @fromdate as Today,@todate as N'5 days Back' ;With DateSequence( Date ) as ( Select @fromdate as Date union all Select dateadd(day, -1, Date) from DateSequence where Date >= @todate ) select * from DateSequence I'm missing something i just need the CTE to return last 5 dates from current date . Does join on dates , really join on datetime ? select ds.Date,et.* from DateSequence ds left outer join emp_timings et on ds.Date=et.dt and et.id=100 Above query efficient to pick last five days records ? Thanks in advance .Rajkumar Yelugu

Joining Dimtime table by a referenced Join create a problem



I am trying to join a dimtime table by a refernced join to an orderlines table which is the fact.

(the referenced join was defined in the relationship matrix of the cube ).

The referenced table is the ordersheader which had no measures in it but only used a linking table between those two other.

The join from the dimtime to the ordersheader  is done by date and from ordersheader to orderlines by orderid

The problem is that in the cube browser measures are shown as they were multiplied across all dates (like the join was done in a wrong way )

I have created an sql query and the logic is working just fine when quering directly from the DB


What am I doing wrong

query join problem

I am using CTE and joining it to other tables in my query.  The issue is I am not creating my joins correctly.  I am using the CTE twice but the calues in it should only appear one time on amount based on the amount type.  attached is the code
declare @dt datetime
set @dt = getdate();

With itmlvl as (





select distinct top 10000 dnc.EM_ITEM_NUM
			,dnc.amt as DNC 
			,pur.Amt as PUR_AMT
			,psr.AMT as PSR_AMT
			,pur.RBT_TYP_ID purRBT_TYP_ID
			,pur.DSCR purDSCR
			,psr.RBT_TYP_ID psrRBT_TYP_ID
			,psr.DSCR psrDSCR
 from geprs_dnc..T_Item_cost_approved dnc
		join reference..T_iw_em_item ref 
		on ref.em_item_num = dnc.em_item_num

		INNER JOIN itmlvl 
		ON itmlvl.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		INNER JOIN itmlvl pur
		ON pur.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		and pur.RBT_TYP_ID = 101
		INNER JOIN itmlvl psr
		ON psr.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		and psr.RBT_TYP_ID = 102

where dnc.COST_ID =32

So for PUR and PSR there should only be one amount for the given date range ...i've

Join Expression Not SHi All, I use Access 2007, and I have the following SQL problem: I have two t

Hi All,

I use Access 2007, and I have the following SQL problem:

I have two tables TransformerTypePeriodBOMProducts and TransformerTypePeriodProducts. The first table has 4 fields: TransformerTypeID, OutputProductID, PeriodID and BOM, while the second has the following 4 fields: TransformerTypeID, OutputProductID,PeriodID and AssemblyCapacity. So as you can see, there are 3 fields (TransformerTypeID, OutputProductID and PeriodID) that are in both tables.

I need to delete all records in the first table that have the field AssemblyCapacity in the second table equal to 0 (and of course have similar TransformerTypeID, OutputProductID and PeriodID fields).

I tried many trials, the last I got was:

SELECT TransformerTypeAssemblyPeriodBOMProducts.*
FROM ((TransformerTypeAssemblyPeriodBOMProducts INNER JOIN TransformerTypeAssemblyPeriodProducts ON TransformerTypeAssemblyPeriodBOMProducts.TransformerTypeID=TransformerTypeAssemb
lyPeriodProducts.TransformerTypeID) INNER JOIN TransformerTypeAssemblyPeriodProducts ON TransformerTypeAssemblyPeriodBOMProducts.OutputProductID=TransformerTypeAssembly
PeriodProducts.OutputProductID) INNER JOIN TransformerTypeAssemblyPeriodProducts ON TransformerTypeAssemblyPeriodBOMProducts .PeriodID=TransformerTypeAssemblyPeriodProducts.PeriodID
DELETE * FROM TransformerTypeAssemblyPeriodB

My problem in update inner join



I have a temp table CTE, with column introducerId and 3 rows as show below.  My update query should affect on 3 rows(Or  times). But it affect only on two rows because “2” is repeated twice in CTE. How can I force it to update 3 times (member.id is unique). In other word: it should update m.RoI where m.id=2, twice

update m set m.RoI = m.RoI +  dbo.udfGet_myFunc(t.CPT)


Table JOIN Problem


I write the following queries :

select y.date, y.item,y.ingredient, (CAST(z.rate AS DECIMAL(10,4)) * CAST(y.qty AS DECIMAL(10,4))) FROM master.recipe_ingredient AS x JOIN master.ingredient as z on x.ingredient = z.item JOIN executive.consumption_Entry as p ON x.recid = p.recid JOIN executive.consumption_total_ingredient AS y ON p.cid = y.cid

and the output is:

10/09/2010    REG    MAIDA    491.37600000
10/09/2010    REG    MAIDA    1152.89800000
10/09/2010    REG    MAIDA    169.44000000
10/09/2010    REG    MAIDA    1306.10000000
10/09/2010    REG    MAIDA    3400.44900000
10/09/2010    REG    MAIDA    1742.40800000
10/09/2010    REG    MAIDA    1553.20000000
10/09/2010    REG    MAIDA    3812.40000000
10/09/2010    REG    MAIDA    1629.44800000
10/09/2010    REG    MAIDA    29652.00000000

10/09/2010    REG    SUGAR &nbs

problem with sql data source - view with INNER JOIN



I have a question. I created a new data source (sql database connection) - a view that contains INNER JOIN statement. After that I created a page and put Edit Item Form on it based on this data source. When I open a page and try to update some records I get an error: "The data source control failed to execute the update command."

There is no error when I use a view without INNER JOIN...

Can I solve it? Should I write my own UPDATE statement?

Thank you.

Ms sql server left join problem


Hi, i have 2 tables(prduction and transact) that contain data for transactional updates per product, stock is calculated by doing sum() on one column in each table that contains the weight and to get the result per product i use group by product. My problem is that if than one value exists in the transact table, the sum(production.plannedtonnage) result is doubled for every record in the transact table. How can i fix this, any help would be greatly appreciated!

select distinct production.product

Problem with join


HI All,

am using sqlserver 2005

i have 3 tables




I wrote the query like

select  p1.people_id,p1.fullname,(substring(p1.designation,1,40)+'...')designation,p1.company_id,c1.company_name,p1.city,p1.state,p1.country,p1.user_id from people p1,company c1 where c1.company_id=p1.company_id and p1.verify_status='Yes' and p1.royalty='Yes' and p1.user_id='abc0000001' and p1.delete_status='No' union  select p1.people_id,p1.fullname,p1.designation,p1.company_id,c1.company_name,p1.city,p1.state,p1.country,p1.user_id from BackupPeople p1,company c1 where c1.company_id=p1.company_id and p1.user_id='abc0000001'

But i want this joins.

am unable to write the query with join

please help me in this.

Thanks in advance,


Left join Problem?


Dear all.......

Problem is this................

Table 1:   studentprofile


Rollno                 studentname             scholarid         class              section   

   1                               A                              1                 VI                   K

   2                                B                &nbs

Problem to group radio button across gridview rows

In general no one can easily group or make a single selection from radio button list inside gridview rows. There are a lot of way to make a single or unique selection from list. Here i want to share how i can address this problem in the most easiest way. Googling the problem most of the cases i saw the grouping in horizontal way. Thats why i tried to group the radio buttons in vertical manner. Let i have a requirement like below:

Regrading Sharpoint 2010 problem and solutions

We can write your question and problem on this blogger.you get ans.use.

This sharpoint 2010 problem solving threads

Hi friend,

you can use this thread to solving problem and solutions.

Virtual Path Problem


 I get the following error on last line below (working on remote host): 'E:\web\sfcascom\ls_admin/uploads/images/001.JPG' is not a valid virtual path,

Note that the slashes go in different directions

I've tried a number of things but nothing seems to work. Thanks-


Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.Web.UI.WebControls

Imports System.Drawing.Imaging.ImageCodecInfo
Partial Class Protected_imageupload
    Inherits System.Web.UI.Page
    Private Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        ' Initialize variables
        Dim sSavePath As String
        Dim sThumbExtension As String
        Dim intThumbWidth As Integer
        Dim intThumbHeight As Integer
        Dim sFilename As String = ""
        ' Set constant values
        'sSavePath = "images/"
        sSavePath = Server.MapPath("~/") + "ls_admin/uploads/images/"

        sThumbExtension = "_thumb"
        intThumbWidth = 160
        intThumbHeight = 120

        ' If file field isn?t empty
        If FilUpload.PostedFile IsNot Nothing Then
            ' Check file size (mustn?t be 0)
            Dim myFile As Http

Strange Photo upload problem


I'm working project it's live on shared hosting

we'er uploading photos to a file on the server everything works fine the photo is resized renamed and sent to the folder

the upload page returns the thumbnail and the full size photo it's there all the photos have to be approved by an admin

twice now after they have been approved they disapear I checked the folder they are not there could there be anywhere

on the server they could get hung up before they go into the folder I have it setup so when you delete the data base entry it deletes the photo from the folder othe than that you need to open the folder and delete the photo this has hapened twice now I tried to recreate it with no luck

thaks for your help in advance

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