Help With Blocking On Querying Two Joined Tables
Dec 8, 2005
I get a 90-120 second blocking when send 15 or so simultaneous queries
to SQL Server 2000 that query a view made up of two joined tables.
After each query is blocking for the same amount of time they all
return. Further identical queries of this type work in 3-4 seconds
(caching?) until hours later where it happens again. If I query the
tables directly (without the view) I still get the same blocking. If I
remove the join (it is a simple inner join on two columns) I do not get
the blocking.
Any ideas?
View 3 Replies
ADVERTISEMENT
Jan 18, 2006
This seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
View 2 Replies
View Related
May 15, 2002
Hi all -
Heres the deal,
I have six tables that are loosly related,
four in one join, two in the other....
in the first join there is a column called commentID -
and is based on a join of the four tables, the results of the
query is based on a where clause
and this needs to be tied into the second set of joined
tables (they are joined on a commentID themselves)
I have both the queries and joins working -
The primary query (4 joined tables) needs to do a lookup on the second (2 joined tables) and add its selected value, into the final result set....
thanks a million for the help!!
take care
tony
View 1 Replies
View Related
Feb 1, 2008
I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join.
The 1st table (Application) holds a job applicant's name and some other data
The 2nd table (Jobs) holds the Job name and test type
The 3rd table (Locations) holds the locations
Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.
When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )
Here is the SPROC that is currently in place but is displaying a row for each location.
CREATE PROCEDURE sp_AdminListApplicants
@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)
AS
IF @JobID <> 9999
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
ELSE
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
GO
Miranda
View 5 Replies
View Related
Dec 7, 2006
I am using SQL Server 2005 to publish joined tables for SQL Mobile subscribers for merge replication and column level tracking.
Using Management Studio I am trying to join tables and specify row filters on the joined tables. I.E. table 1 is joined with table 2. I need to define row filters for table 1 and row filters specific to table 2.
An example would be: Table 1 is a customer table that I filter on a specific customer. Table 2 might be an orders table that I need to join to get the customers orders but I also want to filter for open orders only.
When I specify the row filter for table 2 the join appears to be ignored and I receive the complete table 2 with the row filter applied.
I have searched the online books and the web and I have not run accross an example of using both joins and row filters where the filters are specified for both joined tables.
Is this possible via the Management Studio?
Thanks,
Ron
View 4 Replies
View Related
Sep 12, 2006
Hi!
I have 4 tables inner joined. Two of tables have ~500,000 rows, while other 2 have ~60,000. There are 4-5 WHERE conditions for 3 tables.
Is it normal that a query lasts ~13-15 seconds? I tried indexing in all ways, subselects, temp tables etc, nothing helped.
I think it is unuseful to use indexes because WHERE conditions apply not to one, but to 3 tables.
Is there anyone who is expert in this topic?
Thanx
B
View 2 Replies
View Related
Apr 25, 2008
Hello, i have this problem
Table1
tbl1
pcb varchar(30)serial varchar(30)result varchar(30)
tbl2:
pcb varchar(30)date_time varchar(30)result varchar(30) data1 varchar(30)data2 varchar(30),
what i need is query the tbl1 for a range of serials,get the pcb and for those pcb's query the tbl2 for data1,data2
The resultSet should be a join on the two tables, Columns {serial} from tbl1 and {pcb,date_time,data1,data2} from tbl2
Please follow my simple example:
Suppose tbl1 has these 2 records
tbl1 = pcb1,sn1,pass
pcb2,sn2,pass
pcb3,sn3,pass
tbl2= pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6
where date1 is the most recent date and date6 the least recent
Request:what i want is for serial>=sn1 and serial<=sn2,get the pcbs from tbl1(which are pcb1 and pcb2) and based on these, query the tbl2
for the other data but retrieve only most recent records.
The correct ResultSet should be
pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3
and not
pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb1,date5,pass,dataX5,dataY5
pcb2,date3,pass,dataX3,dataY3
pcb2,date6,pass,dataX6,dataY6
What i already did is this:
select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb
from tbl2
left JOIN tbl1
ON tbl2.Pcb=tbl1.pcb
where tbl1.serial>='1' and tbl1.serial<='53'
and tbl2."Result" like 'pass' and tbl1."result" like 'pass'
group by tbl2.pcb,tbl1.serial;
This works correctly for getting serial from tbl1, date_time and pcb from tbl2.But unfortunately i also need data1 and data2 columns from tbl2.
If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records (by using this OR philosophy).I mean, it would give all records containing (pcb1,pcb2),much like my example
How can i resolve this issue?
Thank you very much
View 1 Replies
View Related
Jun 11, 2008
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
View 1 Replies
View Related
Feb 14, 2014
I have four tables (all inner joined) and currently they give me the results i need. However, my boss has now asked me to return all associated accounts as well.
I am currently pulling data from the four tables to make up my results table, and the returned results are based on the loan types in my loans tables having a loan type of '1A'
So if the loan type is 1A I get a result.
However, Mr Smith (for example) may have three loans but only one of them is type '1A'. The other two might be type '5H' and '2'.
What I need to be able to do is return all the associated accounts of any customer that has a type '1A' loan.
This is my code:
Select c.customernumber, l.accountsuffix, c.forename, c.surname, lt.code, l.balance, j.journeynumber from customers c
inner join loanagreements l on c.customerid = l.customerid
inner join loantypes lt on l.loantypeid = lt.loantypeid
inner join journeys j on c.journeyid = j.journeyid
Where j.journeynumber = 93
and lt.code = '1a'
and l.balance >0
View 6 Replies
View Related
Nov 27, 2007
Hello,
I'm working on a query for a report. I've done this before and it works, but I think it's a little slow due to the joins and I'm wondering if I'm doing this the best way.
This is from a Microsoft CRM system. I'm only using the LEAD table. There is a field on the lead table called StateCode. When a user "Qualifies" a lead, the statecode changes. The report requires a column for total leads, a column for # of leads qualified, and a column for % of leads qualified. There are other columns, but those three will illustrate the problem.
Because total leads means all statecode values are included, and Qualified leads means only one statecode value is included, I can't get those two values from the same query (that I know of). So what I do is take two queries, one for total leads, and one for qualified leads, put them in parenthesis and name them, and then join them on the name of the leadsource, like below. I often end up with 10 or 15 of these "Query Tables" in my main query. Is this the best way?
Code Block
SELECT * FROM
(
SELECT
LeadSource
, COUNT(CreatedOn)
FROM
Leads
GROUP BY
LeadSource
) as A
LEFT OUTER JOIN
(
SELECT
LeadSource
, Count(CreatedOn)
, Count(CreatedOn) / (SELECT COUNT(CreatedOn) FROM leads) AS "% of Leads Qualified from this Lead Source"
FROM
Leads
WHERE
StateCode = 2
GROUP BY
LeadSource
) as B
ON
A.LeadSource = B.LeadSource
Thanks,
Andy
View 3 Replies
View Related
Mar 2, 2007
I have created the following view:
Create view vwOrderItemTotal2
AS
SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName
When I present my data in a GridView, it works fine. For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this:
26 Milk
Now I need to filter my data by OrderDate and Zipcode. I created this new view:
Create view vwOrderItemTotal5
AS
SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems]
FROM Orders INNER JOIN OrderItems
ON Orders.OrderID = OrderItems.fkOrderID
GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate
When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this:
1 Milk
5 Milk
6 Milk
6 Milk
3 Milk
1 Milk
4 Milk
But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view. Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet).
I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause. Thanks for any help provided in solving this.
View 4 Replies
View Related
Apr 7, 2008
I've read that if particular tables are frequently queried together through a join then these tables should be placed on different devices on different physical disks.
What does this mean exactly and how would you configure this?
Is this a common practice in high-performance real-world environments (or should it be)?
View 3 Replies
View Related
May 12, 2008
Each row of my datagrid comes from two tables, A and B, which are (left) joined: not every row from table A has a corresponding row in table B. I think this is quite a common scenario.If I want to edit a row in my datagrid which contains data from both Table A and Table B then presumably I can just use an UPDATE statement behind the scenes.But what happens if I want to edit a particular row in the datagrid which contains data from Table A but no corresponding data from table B? I can't use an UPDATE statement because the record in Table B doesn't yet exist. So what do I do?Does anyone know the answer to this, or could you point me to a good tutorial please?
View 4 Replies
View Related
Sep 30, 2013
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
View 4 Replies
View Related
Jan 11, 2014
I joined different tables and got a result like this:
result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL
Now I want to count the results by counting the processes and group them by the result.
But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).
But now the result "best" disappears, because that column has no date.
Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.
I want to have this result for process4:
count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best
But I got only:
count | result
------ -------
1 | good
1 | bad
I have tried a lot, but nothing works.
The whole result (best, good, ok, bad) are stored in an other table and I joined it.
View 4 Replies
View Related
Nov 5, 2014
I have a query which returns all parts and labour lines for a particular work order. It returns all parts lines seperately, but the labour lines are repeated for each row. What I want to accomplish for a given work order, is a list of all the parts lines, followed underneath by a list of all labour lines.This is the code from the report:
select
h.worknumber,
--- Select parts lines and charges
wp.description as [charges desc],
case
when wp.charge_to_cust = 1 then wp.sale_price
[code]...
For this example what I'd like to see is 5 lines here - the labour description and charge under charges description, unit price, qty and est_parts_sale etc, and of course, there could be more than 1 labour line.
View 2 Replies
View Related
May 3, 2007
Hi
I'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black
SELECT *
from members m, client c
where C.ClientID = m.ClientID
AND c.ClientID in (87,86)
AND m.email in ('dassd@fdskjh.com','asdfas@sdfd.net', etc...)
my results will give me two of the same email addresses but with different ClientID's, now when it
finds the same email it needs to make them both "RED"
Please help, any advice would be helpful
Kind Regards
Carel Greaves
View 1 Replies
View Related
Jul 29, 2015
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
'Mailing1, Mailing2, Mailing3'
View 9 Replies
View Related
Oct 21, 2014
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State
[code]...
View 1 Replies
View Related
Sep 28, 2007
We have an archive table which keeps each instance of a sales order that was archived under a "Verion No" field. Each time the sales order is archived it is entered into the archive tables (Sales Header Archive, Sales Line Archive). What I am trying to do is write a query to return all sales orders but only the most recent archived version.
For example this table layout is similar to what I am working with. Version No, Order No and Customer No. are the keys between the Header and Line tables, Customer Name column in the output is from only the Sales Header Archive table
SALES LINE ARCHIVE TABLE
Version No - Order No. - Customer No -----> (other columns)
1 s-5 1000
2 s-5 1000
1 s-6 2000
1 s-7 3000
2 s-7 3000
3 s-7 3000
1 s-8 4000
1 s-9 2000
2 s-9 2000
Here is what I need to output to show:
RESULTS OF JOINED TABLES
Version No - Order No - Customer No - Customer Name ---> (other columns)
2 s-5 1000 Something, Inc.
1 s-6 2000 Acme
3 s-7 3000 Company, LLC
1 s-8 4000 Blah & Associates
2 s-9 2000 Acme
It should return the last Version No of each Sales order.
Does that make sense? It is something probably easy... But, I've spent two days using multiples and multiples of different ways, that just aren't working: I'm about to dropkick my server cabinet...
View 4 Replies
View Related
Jul 20, 2005
Hello Newsgroup,I have written a very short program to get information from a wholetable out of a database. the problem is that other people couldn'twork on this table during the process. It seems that my program locksthe whole table.I used the DataReader from the .NET Framework. Can you please take alook at the code and give me any solution??? Thank very much, NilsDim strSQL As String = "SELECT * FROM TESTTABLE"Dim Conn As System.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection("Data Source=1.1.1.1;User ID=sa;Password=secret;Persist Security Info=True;Initial Catalog=TestDB")Conn.Open()Dim SqlCmd As SqlCommand = New SqlCommand(strSQL,Conn)Dim DR As System.Data.SqlClient.SqlDataReaderTryDR = SqlCmd.ExecuteReaderDo While DR.Read()<only reading with DR.item("columnname")>LoopCatch ex As Exceptionerrorhandler(ex.ToString)FinallyIf DR.IsClosed = False Then DR.Close()SqlCmd.Dispose()End Try
View 5 Replies
View Related
Nov 7, 2006
I'm using SQL server 200
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
---------------------------------------------------------------------------------------------------------------------------------
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
View 1 Replies
View Related
Jun 13, 2001
I understand that when you create a Temporary table with '#tablename', the table is only visible for the current session.
I have an application which connects to sql server with the same sql account details.The reason for explaining this is that I can have multiple concurrent sessions for the same user. Each of these sessions will run a report by calling a stored procedure which would create a temporary table of the same name.
I found that the response times vary when running the stored procedure concurrently with the same user. From viewing the activity from sql server, I noticed some blocking occurs.
Is there any way to improve on this set up
Pargat
View 2 Replies
View Related
May 23, 2008
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
View 3 Replies
View Related
Dec 13, 2007
Hi all,
lets say i have a table as follows
id name1 red2 orange3 green
and this has s join to another table via the id
table2
id event 1 stop2 stop3 start
now if i make a view and drop these two tables the jkoin is there but i would liek to report on all names who have a event of "go"but it gives me no results , which is right . but how can i output all the names and in the evnet put a empty SO
name eventred emptyorange emptygreen empty
thanksrobby
View 5 Replies
View Related
Jul 20, 2005
Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.
View 3 Replies
View Related
Nov 15, 2006
Hi all,
How do I query two tables in different databases on the same SQL Server?
In short, I want to do:
Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1
Both database 1 and 2 are on the same SQL Server.
Please advise.
Thanks,
V
View 4 Replies
View Related
Apr 18, 2006
Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.
Please email me at dickson.lau@rogers.com
Thanks again.
Dickson
View 15 Replies
View Related
Dec 27, 2006
Hi all,
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
Any ideas?
View 4 Replies
View Related
Jan 4, 2004
I am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish.
Table1 = TVs
ID/SKU_ID/Manufacturer
1 87654 Samsung
Table2 = DVD_Players
ID/SKU_ID/Manufacturer
2 765234 Kenwood
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
View 2 Replies
View Related
Jun 7, 2005
What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
View 5 Replies
View Related
Apr 23, 2008
I think this should be a dead easy one.
SQL Server 200.
Can I query to find all the tables in a single schema? I can't for the life of me find this one out!
I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!
Cheers,
G
View 12 Replies
View Related
Jul 23, 2005
Hi;I am looking for suggestions about how to solve a problem using tsql.I have been asked to create a report concerning 4 tables.Each of the 4 tables is in its own database.The 4 tables are identical in name and structure.I would like to query all 4 of these tables and get the results as ifthey were one table.All suggestions welcome, thanks.Steve
View 2 Replies
View Related
