Inserting A JOIN In This Query?

May 12, 2006

Hi folks, I'm having some trouble here.

In a database, there's a table that contains information about items or units in a flow; tbl_item. One of the columns is named SubLocationID (int) and it says where in the flow the unit is located.

Another table is called tbl_sublocation, and it contains information on each sublocation, where of course SubLocationID is the primary key. In this table, there's a column named SubLocationName which gives the user a name to relate to instead of just a number.


Code:


SELECT
SubLocationID,
count(SubLocationID) AS Units

FROM
tbl_item

GROUP BY
SubLocationID

HAVING (COUNT(SubLocationID) > 1)



Now, I use this query to get information if there's any sublocations that have two or more items on them. It works just fine, but I want to display SubLocationName from tbl_sublocation instead of SubLocationID, and I just can't figure out how.

Anyone got any sugestions? Thanks in advance.

View 4 Replies


ADVERTISEMENT

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'm using SQL Server 2005.



A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).



The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).

If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.



If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.



So, this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



and this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC



But this does't:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



What should I be looking for here to understand why this is happening?



Thanks,

john















View 1 Replies View Related

SQL Query Question - JOIN Or Not To JOIN

Jan 2, 2006

I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.

View 3 Replies View Related

Inserting One Query Before Another

Jul 23, 2005

I am having trouble inserting two queries...I am trying to insert annew item(pk) into a table in one query, and then inserting an item(fk)in another table that relates to the other item just created in thefirst. The problem is that in order for the second query to work, thefirst has to exist in the first table before it will work. Is there away of running the first query first and then the next. Here is what Ihave:<cfquery name="q_insert" datasource="#dsn#">INSERT INTO eccn(eccn_num,eccn_brief_descrip,eccn_full_descrip,eccn_reason_controlled)VALUES('1D001','TEST','TEST INPUT AND SEARCH','TESTING THE INPUT AND SEARCH PERFORMANCE')</cfquery><cfquery name="q_insert2" datasource="#dsn#">INSERT INTO eccN_interrelationship(eccn_num,related_eccn_num)VALUES('1D001','1B001')</cfquery>

View 1 Replies View Related

Help With Inserting WMI Query Into SQL

Jan 18, 2007

Hi, I'm a compleate noob and trying to learn c# and wmi. I have used WMI queries in vbscripts but I want to take things a bit further now and read a server name from a SQL db and query WMI (eg. disk space available) then record it back into SQL. I'm really struggling on how to do this.

I written the code to query WMI (SELECT * FROM Win32_LogicalDisk) but referencing it to DB's is something I can't do!

Can anyone help with this?

View 1 Replies View Related

Inserting Variables Into A Query Using SqlDataSource

Jan 4, 2008

 Hi all,
I was wondering if anyone can help me figure out how to insert a
variable into a query using Visual Studio 2005 with the SqlDataSource
control. I cant seem to be able to enter a var into the query
parameters.

this is my SqlDataSource:
        <asp: SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:imLookinLikeConnectionString
%>"            DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID"           
SelectCommand="SELECT tblDiaryEntries.DiaryEntryID,
tblDiaryEntries.EntryDate, tblDiaryEntries.Subject,
tblDiaryEntries.DiaryEntry, aspnet_Users.UserName FROM tblDiaryEntries
INNER JOIN aspnet_Users ON tblDiaryEntries.UserID = aspnet_Users.UserId
WHERE UserName=@UserName ORDER BY tblDiaryEntries.EntryDate DESC"           
UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry,
[EntryDate] = @EntryDate, [Subject] = @Subject WHERE [DiaryEntryID] =
@DiaryEntryID" InsertCommand="INSERT INTO tblDiaryEntries(UserID,
EntryDate, Subject, DiaryEntry) VALUES (@UserId, GETDATE(), @Subject,
@DiaryEntry)">            <DeleteParameters>                <asp: Parameter Name="DiaryEntryID" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp: Parameter Name="DiaryEntry" Type="String" />                <asp: Parameter Name="EntryDate" Type="String" />                <asp: Parameter Name="Subject" Type="String" />                <asp: Parameter Name="DiaryEntryID" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp: Parameter Name="DiaryEntry" Type="String" />                <asp: Parameter Name="EntryDate" Type="String" />                <asp: Parameter Name="Subject" Type="String" />                <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" />                <asp: Parameter Name="UserId" />            </InsertParameters>            <SelectParameters>                <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" />            </SelectParameters>        </asp: SqlDataSource>
What I want to do is to tell the SqlDataSource that  @UserName = this.User.Identity.Name, but I only know how to do that in
code-behind, not sure how to insert it into the code above.Any ideas?

View 3 Replies View Related

Error Inserting To Database: Parameterized Query

Jun 11, 2008

Hi, Im struggling with this insert statement, I want to use with a AJAX validation Post Form page.
Its quite straght forward, if a search query returns null the insert these values. The search query does work, what I mean by that is that txt field values seem to pass for search but not insert. Any help out there cheers Paul if (RowCount == 0)
{String strSQL = "INSERT INTO Mail_List (FirstName, Email) VALUES( @FirstName, @Email )";
 
try
{mySqlConn = new SqlConnection(strSqlConn);
mySqlConn.Open();SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand(strSQL, mySqlConn);cmd.Parameters.AddWithValue("@FirstName", Request.Form["FirstName"]);cmd.Parameters.AddWithValue("@Email", Request.Form["Email"]);
cmd.ExecuteNonQuery();
lblStatus.Text = "Registration Successful";
}

View 2 Replies View Related

Inserting Serial No Column In Result Of A Query

Apr 21, 2005

Hello Friends
                     My problem is
                     Suppose Query is - Select * from tbl_Employee
                     TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
                     I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
                     Serial   Name Post
                        1      XYZ   QER
                        2      SDF   OPO
                        3      WER   IPO
            If any body knows please post the solution its urgent.

View 11 Replies View Related

Dynamically Inserting A List In WHERE Clause Of A Query

Jul 8, 2014

What I need?: A way of dynamically inserting a list in the WHERE clause of a query. (in the form, WHERE ID = 1,2,3,6,9 etc)

Imagine an example DB with 3 columns Student ID, Name, Teacher_ID. (Lets assume Teacher_ID with value 100 means its the Headmaster)

I need to create a list with Student ID's, who are directly/indirectly under the Headmaster. Example:

Headmaster
Teacher 1 (ID 200)
Teacher 2 (ID 250)
Student 1 (ID 300)
Director
Teacher 4
Student 5

In the above example, since I only want those students/teachers under the headmaster, either directly/indirectly, my list would contain Teacher 1, Teacher 2, and Student 1. (In my case, just their ID's, so 200, 250, 300)

Director, Teacher 4 and Teacher 5 wouldnt be in the list since theyre not directly/indirectly Headmaster.

View 2 Replies View Related

Inserting Results From Query Into New Temp Table

Sep 17, 2013

I am try to insert the results from the query into new temp table but keep geeting an error for Incorrect syntax near ')'.

select * into tempCosting
from
(
select top 10* from itemCode itm
where itm.type= 1
)

View 3 Replies View Related

Inserting Into A Tmp Table Using A View --- Please Help Using SQL Query Analyzer

Nov 30, 2006

Hi there,I struggle to get this going i would like to insert data into 2 tmptables in a view.If i run the code on it's own it works perfectly until i want to createa view it complains about the INSERTthis is my codeCreate view dbo.vew_SwitchesAsINSERT INTO tmpInsSelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.INAME,MB.NOfrom Cars BIVLEFT JOIN MountainBikes MBON MB.ID = BIV.IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'IN'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC------ Step 2 -------Into tmpOutsInsert Into tmpOuts ---- All Switches In ----SelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.NAME,MB.NOfrom Cars BIVLEFT JOIN Mountainbikes MBON MB.ID = BIV._IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'OUT'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC----------------------Step 3 ----------------SelectDistinctins.DATE,ins.ID,ins.NAME ,insIND,ins.AMOUNT/100 as AmountIn,outs.IND,outs.AMOUNT/100 as AmountOut,outs.NAME


Quote:

View 4 Replies View Related

Query Notification Stopped Working When Inserting Row

Mar 27, 2008

I'm using query notifications and it worked fine as long as I just edited the data that the "query points to" (one table). That is, my graphical represenation of the data (in a gridview) was updated correctly with the new data 1ms after I changed it, just like it should.

When I inserted a new row to the table the query notification stopped working, meaning my gridview wasn't updated. Any idea of why? Also, after this, not even editing of the data made the query notification trigger so it must have totally stopped.

Thanks in advance
ripern

View 3 Replies View Related

Change In Query For Inserting Float Values

Jan 2, 2008



Hi

I was using a simple query to insert data in fields of type decimal


INSERT INTO Table (stringValue,intvalue) VALUES ('myString ',

" + numericvalue + ")



a query looked like this

INSERT INTO Table (stringValue,intvalue) VALUES ('myString',125)

I had to make a change to accept float point values ( 0,012 ) so I change the fields from decimal to float in the DB

Now I'm having problems in the query because of the comma. The new query looks like this

INSERT INTO Table (stringValue,intvalue) VALUES ('myString',0,012) so because of the comma the engine see three values instead two

How can write the query to insert this float values?

thanks

View 4 Replies View Related

Problem With Making A Sqldatasource For Inserting Using The Query Builder

Aug 19, 2007

I want to make a sqldatasource to insert data ito a table with values from textfields page. I want to use the sqldatasource programically (not bind it to a formcontrol).
I drag a sqldatasource from the toolbox to the design surface and start configuring the datasource. I spesify a custom sql statement, select the "Insert" tab and insert the table I want to store into the builder. Then I select the fields and the values (parameters from tue textfields. I test it with the "Execute query" button and the results is stored in the table. (everything seems ok) I press the "OK" button but both the "Next" and "Finish" buttons are dissabled, o I can not store the query.
What is going wrong. Can someone please help me ?
Tom Knardahl

View 2 Replies View Related

Problem In Inserting Date In Sql Server 7 Through Insert Query

Jul 20, 2005

hello myself avinashi am developing on application having vb 6 as front end and sql server 7as back end.when i use insert query to insert data in table then the date value ofthat query is going as 01/01/1900my query is as followsStrSql = "Insert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"StrSql = StrSql & " Values(" & txtTransactionID.text & "," &txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),2)& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),2)& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),2)+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),0)& ")"and its output isInsert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)in above query though i used cdate to voucherdate value still it save indatabase as 01/01/1900 though here it shows right dateplz help me its a very big issue for me & i really just fed of thisproblem

View 2 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

Problem Inserting Sql Query Into Database Float Datatype Field Using SQL Transaction

Aug 22, 2006

I have this problem of inserting my query into database field. My code is as of below.
The @AVERAGESCORE parameter is derived from
Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)
and the value returned is  (averagescore.toString("0.00"))
However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?
  Try
Dim i As Integer
For i = 0 To arraySql.Count - 1
myCommand = New SqlCommand
Dim consolidatedobjitem As ConsolidatedObjItem = arraySql(i)
myCommand.CommandText = sqlStr
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
With myCommand.Parameters

.Add(New SqlParameter("@AVERAGESCORE", consolidatedobjitem.getaveragescore))

End With
myCommand.ExecuteNonQuery()
Next
myTrans.Commit()
myConnection.Close()
Catch ex As Exception
Console.Write(ex.Message)
myTrans.Rollback()
myConnection.Close()
End Try 

View 7 Replies View Related

DELETE Query - Can't JOIN. Need Sub-query?..

Mar 30, 2007

I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?

What I initially tried to do was:

Code:

DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1


Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1

How can I do this without using JOIN. Use a sub-query? How?

Thanks

View 4 Replies View Related

Enquiry On Join 3 Query In 1 Query

Sep 29, 2007

Code:

SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total,
MAX(RecJobAds.PostedDt) as PostedDt,
MAX(RecJobAds.JobTitle) as JobTitle,
RecJobAds.JobId,
MAX (AgencyCandidate.AgencyId) as agentID,
MAX(RecJobAds.AdStatus) as status,
MAX(RecJobAds.CompanyId) as CompanyId,
MAX(RecJobAds.RecId) as RecId
FROM RecJobAds
LEFT JOIN AgencyCandidate
ON RecJobAds.JobId = AgencyCandidate.JobId
GROUP BY RecJobAds.JobId
ORDER BY Total ASC



i have the above query. but i would like to join in a third table, where my query is


Code:

select * from RecruiterMA where Activated = '1'.



anyidea in which place i can put the second query in the first query? thank you very much!

View 4 Replies View Related

Join First Query With A Query That Contains Unions

Jun 10, 2013

I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.

Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,

[Code] .....

Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND

[Code] .....

View 9 Replies View Related

T-Sql And X-Query Join

Nov 30, 2007

Hi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos>    <ContactsInfos>         <ContactID>1</ContactID>    </ContactsInfos>    <ContactsInfos>         <ContactID>2</ContactID>    </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts    ContactID : int    FirstName : varchar(200)    LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?

View 1 Replies View Related

Query Inner Join With Top

Mar 5, 2008

Hello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA.I wish to select  A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first  bot_date>B.hist_date  and after this, if we still gets multiple records then the top record will be selected (order by date)
Any ideas?

View 7 Replies View Related

Can't Join This Query

Jul 21, 2004

I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.


Code:


SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
WHERE news_id =41

SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54



my previous attempts are

trouble with query.

I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.

now when I'm editing the article I want to get the name of the author from the users table.

I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.

they both need to lookup the user_fname and user_lname fields from the users table.

my current sql is below


Code:

SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41



which gives me

Code:

news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author *
41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green



Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
every way I try doesn't work.

this is what I'm currently trying to get it with.


Code:


SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (

SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
FROM news
WHERE news.edit_author = users.user_id
)
WHERE news_id =41




Heres a different approach that I thought would work


Code:


SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (

SELECT concat( users.user_fname, users.user_lname ) AS edit
FROM users
WHERE users.user_id =54
) AS edit
WHERE news_id =41



but no i get this error

Code:


MySQL said:

#1064 - You have an error in your SQL syntax near '(
SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE' at line 4



I also tried


Code:


SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id
WHERE news_id=41



but got this error

Code:


MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE us' at line 2



can anyone help me with this query its been driving me up the wall all morning.

View 2 Replies View Related

Join Query

Jul 28, 2004

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

EmpidDatePts(DIv1) Pts(DIv2) Pts(DIv3)
V00107-24-2004NullNull25
V00207-24-200420Null25
V00307-24-2004Null30NUll
V00107-23-200415NullNUll
V00207-23-2004102525
V00107-22-2004Null1025

I'm badly in need of help. Any sort of help is appreciated.


M.L.Srinivas

View 3 Replies View Related

Join And Sum Query

May 30, 2012

how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....

Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal
FROM dbo.Cust P
INNER JOIN
(SELECT
CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
Sum(Balance) as Bal
FROM dbo.CAsh
Where Accumulator='DP'
GROUP BY CustID) A ON P.CustID = A.CustID

The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.

Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
FROM dbo.Cust P
INNER JOIN

[code]....

View 14 Replies View Related

Self Join - Query Reg

Apr 8, 2008

I've a table like

Object ObjIdParentObjId
------ ----------------
Obj1 |101 |0
Obj2 |102 |101
Obj3 |103 |0
Obj4 |104 |102


If the 'ParentObjId' is 0 it means no more parent,

If I filter the Object value as Obj4, it should show the following rows


Obj4 |104 |102
Obj2 |102 |101
Obj1 |101 |0
Guide to me write a SQL for this situvation?

View 17 Replies View Related

Query JOIN

Jun 10, 2008

i have two tables field1 is the primary key:
table1
--------------------
field1|field2|field3
---------------------

table2
---------------------
field1|field4|field5
---------------------
what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.

is that possible, i used inner, right joins but all in vain.

what I need is a output like this

output
---------------------------------------
field1|field2|field3|field4|field5
---------------------------------------

View 5 Replies View Related

Join Query

May 17, 2006

i hav 2 tables which contain a common field called ID.
Table A Table B
ID ID
a a
b b
c
d

Now i want results from Table A in such way that ID's Which are in table B do not get selected in table A.
how do i use join query for this

View 2 Replies View Related

Inner Join Query

Mar 15, 2007

Hi

I have two tables and one linking table they look something like this

<<<<<<<Application Table>>>>>>

AppID AppName
1 MS Word
2 Excel
3 SoftGrid

<<<<<<System Table>>>>>>>>

SysID SysName
1 System 1
2 System 2
3 Server 1

<<<<<<Link Table>>>>>>>>>

SysID AppID
1 1
1 3

How can i retirve the data so that it picks up and displays all the applications that are not on the the server as well as those that are

for example the output im trying to get is as follows from the data above.......

SysName AppName Y/N
System 1 MS Word Y
System 1 Excel N
System 1 SoftGrid Y
System 2 MS Word N
System 2 Excel N
System 2 SoftGrid N
Server 1 MS Word N
Server 1 Excel N
Server 1 SoftGrid N

i want to able to get a dynamically generated Y/N colunm

Im ok with doing innerjoin queries but the problem is how do i get the rest of the results that have no links to show that there it does not exist.

Thanks

View 3 Replies View Related

Sub Query In Inner Join

Jun 4, 2007

Hi,
I need to write a query to get the data in hierarchy from a table.
Like emp table where each employee(EMPID) has an manager and manager is also an employee(EMPID).
There could be 2 level of managers. Mean an employee who is a manager can also have manager.
So i need to write query which shows three columns with column1=toplevel manager, column2=secondlevel manager and column3=team member.

Any help will be appreciated.

Thanks,
Nadeem

View 7 Replies View Related

Join Query

Jul 12, 2007

I use the following query

select distinct safetyvideo2007.user_id,safetyvideoview.name
from safetyvideo2007,safetyvideoview where safetyvideo2007.user_id=safetyvideoview.user_id
order by safetyvideoview.name asc

this retrive records only where first table user_id= second table user_id

user_id......name
---------------------
p0088421Khan
p0038040Peter
p0031344Jones
....

table1 = safetyvideo2007 user_id colum
table2 = safetyvideoview user_id and name column

I want the it retreive records from safetyvideo2007 all records
if first table only have user_id column no name column available.

ie. show table safetyvideo2007 all user_id if second table safetyvideoview user_id does not found ? ie.
it found user_id in second table then ok otherwise it retreive
all records from safetyvideo2007.

like this.
user_id.......name
---------------------
p0088421Khan
p0038040
p0031344Jones
p0031345Martin
p0031347Wagner
p0031357
....
...

display all safetyvideo2007 user_id if second table safetyvideoview
user_id not found ?

regards
Martin

View 2 Replies View Related

Join Query Help

Jul 25, 2007

Hi,

I have a table with following fields..

tblEmployee
-----------

EmpID int,
EmpName char(20),
MgrID int


I want the name of all employees and names of correspoding managers..

Please help me as soon as possible..

Thanks in Advance..

Regards,
Asha Sivan

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved