Query Using Multiple Tables, SUM()
May 25, 2007
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!
View 3 Replies
ADVERTISEMENT
Sep 8, 2006
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.
View 3 Replies
View Related
Feb 11, 2005
Hi, 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)
View 3 Replies
View Related
Apr 17, 2008
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" ')
View 4 Replies
View Related
Apr 21, 2015
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
View 2 Replies
View Related
May 3, 2007
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
View 2 Replies
View Related
Jun 17, 2006
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
View 1 Replies
View Related
Feb 27, 2007
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!!
View 5 Replies
View Related
Feb 8, 2005
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.
View 3 Replies
View Related
Aug 29, 2013
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]....
View 2 Replies
View Related
May 3, 2006
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 Related
Apr 3, 2008
Hello 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.
View 17 Replies
View Related
Oct 25, 2007
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
View 4 Replies
View Related
Nov 6, 2007
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
View 2 Replies
View Related
Apr 1, 2006
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.
View 6 Replies
View Related
Feb 27, 2007
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
View 3 Replies
View Related
Jan 29, 2004
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?
View 6 Replies
View Related
Mar 8, 2012
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?
View 4 Replies
View Related
Jan 19, 2006
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.
View 21 Replies
View Related
Apr 27, 2015
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 Related
Mar 21, 2008
Hi! 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
View 1 Replies
View Related
May 17, 2013
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.
View 9 Replies
View Related
Apr 10, 2007
Hi there,
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
View 2 Replies
View Related
Mar 6, 2008
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
PID
MISSING FROM
1
TBL3
2
TBL3
3
TBL1
4
TBL2
4
TBL3
5
TBL1
6
TBL1
6
TBL2
7
TBL2
10
TBL1
10
TBL2
Thanks all.
View 5 Replies
View Related
Mar 18, 2008
This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
View 7 Replies
View Related
Aug 31, 2015
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
View 9 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Jun 27, 2006
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
View 9 Replies
View Related
Mar 1, 2007
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
View 9 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 15, 2008
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
View 1 Replies
View Related
