I have the following query and I would like to combine it into one query instead of using several temp tables.
IF OBJECT_ID('Tempdb..#a') IS NOT NULL DROP TABLE #a
SELECT *
INTO #a
FROM #Web_table w WITH(NOLOCK)
WHERE w.generated_date IS NOT NULL
IF OBJECT_ID('Tempdb..#b') IS NOT NULL DROP TABLE #b
[code]....
Hi Guys,
I have to work with a poorly designed table :(, that has columns
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
Description varchar(8000) null,
ProductTypevarchar(255) null,
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question
id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
DescriptionQvarchar(8000) null,
DescriptionAvarchar(8000) null,
ProductTypevarchar(255) null,
)
SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
Any bright sparks out there that can help me?
Cheers
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
Any ideas?
Thanks in advance.
Hi All,
I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.
Would somebody be kind enough to help me out please.
To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:
(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.
I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.
SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)
Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.
Please help, not having much joy with this. Many thanks.
Is there a quick way to create a temp table that is the same as an existing table in the schema? I am used to Informix where "select * from <table A> into temp B" would create the temp table B with the same schema is table A. Is this same functionality available with MS SQL server using query analyzer?
View 2 Replies View RelatedI have a table T1 with columns: Date, Salesman, Sales. Salesman is a userid for each person and Sales is a decimal capturing sales done on Date.
From the data in T1 I would like to have a table with the following:
Salesman Sales-To-Date Sales-Last-Week
I have been able to do it with a temporary table, temp, as follows:
select Salesman, Sum(Sales) as ToDate into temp from T1 group by Salesman
with T2 as (
select Salesman, Lastweek=sum(Sales) from T1 where week>='1/28/2008' and week<='2/3/2008' group by Salesman
) select temp.Salesman, ToDate, Lastweek from temp full join T2 on temp.Salesman=T2.Salesman
How can I do the above in one query statement?
Thanks
The first query returns me the results from multiple databases, thesecond does the same thing except it puts the result into a #temptable? Could someone please show me an example of this using the firstquery? The first query uses the @exec_context and I am having achallenge trying to figure out how to make the call from within adifferent context and still insert into a #temp table.DECLARE @exec_context varchar(30)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT DBNAMEFROM DBINFOWHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')ORDER BY DBNAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @exec_context = @DBNAME + '.dbo.sp_executesql 'set @sql = N'select top 10 * from products'exec @exec_context @sqlFETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursor-------------------------------------------------------------------------------------CREATE TABLE #Test (field list here)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT NAMEFROM sysdatabasesWHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULLORDER BY NAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'INSERT INTO #Testexec (@sql)FETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursorSELECT * from #TestDROP TABLE #Test
View 1 Replies View Relateddo you have a general rule of thumb for breaking a complex query into temp tables? For someone who is not a sql specialist, a query with more than a few table joins can be complex. So a query with 10+ table joins can be overwhelming for someone who is not a sql specialist.
One strategy is to break a problem into pieces so to speak by grouping together closely related tables into temp tables and then joining those temp tables together. This simplifies complex SQL and although not as performant as one big query it's much easier to understand. So do you have a general rule of thumb as far as a threshold for the number of joins you include in a query before you break the query into temp tables?
Hi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet
View 2 Replies View RelatedHi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.
I have 3 tables Clients <-oo ClientContacts oo-> Contacts
(the <-oo means one to may relation between the tables)
A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.
My goal is to run a query which will return
Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
WHERE (Clients.WorkOnHold = 0)
The result should return values for
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category,
and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.
I tryed an INNER JOIN but it will return juts the records having contact information.
Any solutions are appreciated.
Thanks.
Clients
CREATE TABLE [Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100),
[MailingStreet] [varchar] (50),
[MailingCity] [varchar] (35),
[MailingState] [varchar] (35) ,
[MailingZip] [varchar] (10),
[WorkOnHold] [bit] NULL ,
[ClientNotes] [varchar] (500),
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Contacts
CREATE TABLE [Contacts] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,
[JobTitle] [varchar] (50),
[BusinessStreet] [varchar] (50),
[BusinessCity] [varchar] (35),
[BusinessState] [varchar] (35),
[BusinessPhone] [varchar] (20),
[BusinessFax] [varchar] (20),
[E-mailAddress] [varchar] (255),
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ClientContacts
CREATE TABLE [ClientContacts] (
[ClientID] [int] NOT NULL ,
[ContactID] [int] NOT NULL ,
[Category] [varchar] (50),
CONSTRAINT [FK_ClientContacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ClientID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_ClientContacts_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ContactID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
The INNER JOIN I tryed but is not good. It returns just clients having contacts attached.
SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName,
Contacts.[E-mailAddress]
FROM ClientContacts INNER JOIN
Clients ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)
I've run into an interesting challenge, and haven't figured out how to solve it yet. I'm fairly novice at MS SQL, and I would assume that there is a relatively simple/elegant solution. Here's what's going on:
I'm tracking projects that have a one to many relationship with sub-project type. There can be many entries for any given project and sub-project type. Each entry has a status.
EX: "Datalist"
ID Proj Sub-Proj Status
1 Alpha Review 1
2 Alpha Test 3
3 Alpha Review 2
4 Alpha Review 2
5 Alpha Test 4
In addition to the Datalist above, I've got a table which contains all valid combinations of Projects and Sub-Projects.
EX: "ValidCombos"
ID Proj Sub-Proj
1 Alpha Review
2 Alpha Test
3 Beta Review
4 Beta Rewrite
5 Beta Test
I need to make a query/table which contains the total number of IDs that have a given Project, Sub-Project, and Status.
EX using data from the tables above: "DesiredTable"
Proj Sub-Proj Status1 Status 2 Status3 Status4
Alpha Review 1 2 0 0
Alpha Test 0 0 1 1
Beta Review 0 0 0 0
Beta Rewrite 0 0 0 0
Beta Test 0 0 0 0
How do I do this? BIG thanks in advance!
hi friends,
I m using pubs database. without using contains keyword i m getting result. if i use contains keyword i m not getting result.
select j.job_desc,e.fname,e.lname from jobs j,employee e where j.job_id=e.job_id and fname='Victoria'
This query displays as
desc fname lname
Managing EditorVictoriaAshworth
Below query displays as empty value
desc fname lname
select j.job_desc,e.fname,e.lname from jobs j,employee e where CONTAINS (e.fname, ' "Victoria" ')
I am trying to write a query which includes the following two tables.
Incidents
Column 1 = SourceContactNo
Contacts
Column 1 = ContactNo
Column 2 = Ref
The first columns in Incidents and Contacts both contain the same type of information (contact numbers, only the column names are different, some contact numbers appear in table 2 (contacts) which do not appear in incidents).What I really want to do is return the Contact Number & Ref from the table named contacts where the contact number is the same as SourceContactNo (in the first table incidents).Here is what I have so far:
SELECT Incidents.SourceContactNo, Contacts.ContactNo, Contacts.REF
FROM Contacts, Incidents
Hi,
I have a query that returns values from three seperate data tables into a dataset:
SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]
FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier
WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')
Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.
FileIdentifier FieldName FieldText
1 Field 1 Hello
1 Field 2 Goodbye
The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.
The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!
At the moment mt results gridlooks like this:
FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2
1 MyPC 1 Me 03/05/07 14:24 Hello Hello
1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye
I need it to look like this:
FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2
1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye
Thanks,
Guy
Hello,
I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL.
So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using....
SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @CartID ORDER BY ProductID
I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this?
I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing!
I'd really appreciate any help you can offer me.
Many thanks,
Luke
I'm trying to run a sql query using the core dotnetnuke database. I am tying to pull from two separate tables in the same database and return the results below:
------------------------------------
LastName | FirstName | City | PhoneNumber
Smith Joe New York (555)555-5555
------------------------------------
Last Name and FirstName are two separate columns in the DB_Users table, while City and PhoneNumber are both values of the same column DB_UserProfile.PropertyValue located in separate rows defined by an ID in the DB_UserProfile.PropertyDefinitionID column.
The UserProfile table looks something like this...
ID | PropertyDefinitionID | PropertyValue
1 27 New York
2 31 (555)555-5555
------------------------------------
This is what I have so far but it is not working out to well...
SELECT DB_Users.FirstName, DB_Users.LastName, DB_UserProfile.PropertyValue AS City, DB_UserProfile.PropertyValue AS PhoneNumber
FROM DB_Users, DB_UserProfile WHERE DB_Users.UserID = DB_UserProfile.UserID AND DB_UserProfile.PropertyDefinitionID = 27 AND DB_UserProfile.PropertyDefinitionID = 31
ORDER BY DB_Users.LastName
------------------------------------
THANKS!!
Hi:
I'm trying to create an ad-hoc query on a Asp.net page for user. Besides the usual Boolean operators, Field Names, Comparison operators & Field Values, the ad-hoc query also involves multiple tables, eg [Customers] , [Members] & [Orders].
Now I have difficulties on writing a TSQL sp on how to take the dynamic query with different tables under consideration. User might simply query each individual tables (eg, Customers with age > 25) or combination of tables (eg, Membered Customers with Orders Amt > 1000 between 1/1/2005 - 1/31/2005)
I have look up a lot dynamic query on the net but all are with only 1 single table to hit. Could anyone give me a direction on how to write a dynamic query script with multiple tables under consideration? Much appreciated.
ps: The ad-hoc query only contains these defined tables, no other table will be involved.
Sql2005??? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?
View 1 Replies View RelatedHello All,
I want to update multiple tables using single query and fields name are same of tables.
I am trying like:
update tablename1 t1,tablename2 t2 set t1.fieldname1 = t2.fieldname1 = 'value' where condition;
or
update tablename1 t1,tablename2 t2 set t1.fieldname1 = 'value' t2.fieldname1 = value where condition;
Plzzzzzz help me.Thanx in advance.
Thanx & Regards,
Smita.
Hi!
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1
Hi,
I am using SQl server 2005.
want to update rows in 2 tables,which have a relation on Id field.
Some thing like
Update tblA a , tblB b
Set a.UpdatedDt=getdate(),b.Updateddt=getdate()
where a.Id=b.Id and a.Name='xyz'
can anyone out there help me?
Thanks
Renu
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
i'm somewhat at a loss of where to begin.
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
I am trying to do the following:
Insert n rows into A Table called EAItems. For each row that is inserted into EAItems I need to take that ItemID(PK) and insert a row into EAPackageItems.
I'm inserting rows from a Table called EATemplateItems.
So far I have something like this: (I have the PackageID already at the start of the query).
INSERT INTO EAItems(Description, Recommendation, HeadingID)SELECT Description, Recommendation, HeadingIDFROM EATemplateItems WHERE EATemplateItems.TemplateID = @TemplateID INSERT INTO EAPackageItems(ItemID, PackageID) ....
I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.
Any Advice / help would rock! Thanks
Hello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
Greetings,
Iam new to SQLl2005. Iam using DTS to transfer data from my source to the warehouse. I have a couple of tables in my source whein I have to join these to tables fields and insert the same in teh warehouse fact table. I have used a Join query in my Oledb source component, What other component needs to be used to insert the data into the fact table.
I also need to extract same data with aggregation and insert the same into an another Fact table.
Kindly help.
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies View RelatedHi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?
select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id
There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.