Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timark
Starting Member
11 Posts |
 Posted - 2008-02-14 : 14:55:21
|
| How do I get a table query to exclude data based on the contents of another table??I import raw data into a table where it is then deconstructed to a relational format. I keep the raw data with each submittal as a record of data received, so I need to sort out the rowsets that have already been deconstructed so that I don't add them to the relational data again. I'm having trouble defining a query that does this. First, I developed a view that groups the relational data to list the submittals contained, as follows.--- begin contents query ---SELECT ProgramID, ContractID, AsOfDateFROM dbo.tblBaseDataGROUP BY ProgramID, ContractID, AsOfDate--- end contents query ---Then I attempted to draw the rowsets from the raw data that were not included in the relational data using the following query. I'm only including the column set necessary to identify the new data.--- begin new rowset query ---SELECT dbo.tblRawData.ProgramID, dbo.tblRawData.ContractID, dbo.tblRawData.AsOfDate, dbo.tblRawData.CAID, dbo.tblRawData.SPAE, dbo.tblRawData.ValueTypeFROM dbo.tblRawData INNER JOIN dbo.vwBaseDataContents ON dbo.tblRawData.ProgramID <> dbo.vwBaseDataContents.ProgramID ORdbo.tblRawData.ContractID <> dbo.vwBaseDataContents.ContractID OR dbo.tblRawData.AsOfDate <> dbo.vwBaseDataContents.AsOfDate--- end new rowset query ---This query draws all rowsets from the raw data table, where I was expecting it to draw only those elements that weren't represented in the relational data.How do get a table query to exclude data based on the contents of another table??I sure would appreciate any help! Thanks! -tim |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-02-14 : 16:07:27
|
| The key question here is do you have a key field(s) between these two tables that will identify rows. That is how you want to seperate the new ones with the existing ones. Your way of using OR to join not equal columns in the where clause let me to think that you are looking for any changed/unchanged rows. |
 |
|
|
timark
Starting Member
11 Posts |
Posted - 2008-02-15 : 12:37:22
|
| H*You just about nailed it - each submittal repeats the rowsets already submitted, but the detailed data for 144 periods changes with each submittal (the data is analyzed across submittals to assess trends). The fields that I selected in the 'new rowset' query do in fact provide the data required to make the rows unique. The three fields selected in the 'contents' query are required to identify the distinct submittals... so I need to be able to select the data from 'rawdata' that includes the submittals that are *not* contained in the 'basedata', so that I can deconstruct this "new" data into a relational format - other tables are affected as well, but the same techniques would be used to insert new data into those as well. SO - I *think* I understand that what you are saying is that I should utilize ALL of the key fields rather than just the ones that are required to identify the new rowsets as belonging to a new submittal... yes?X*I don't understand either statement - what is DDL?? And what does the 'is null' statement do? None of the selected fields hold nulls...Thanks for your responses!!-tim |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-02-15 : 15:22:52
|
| DDL (Data Definithion Language).If you could provide Create table statement and sample data, then it will be easier for us to help.Table2.K2 IS NULL is a way to find rows in one table but not in the other. i.e. this is the most common way to find the new arrival lines. Look it up in sql book on line.When you get your new set of raw data, are they incremental (meaning only new added ones and changed ones are in there), or historical (meaning including those have been submitted)? If it is the former, the most challenge part is to identify the "deleted" ones. There are a lot of ways to find out the changed ones. A datetime field indicating last_change_date is one. Yours <>or <> is another way. But there are a lot of details need to be considered. For instance, does .530 = 0.53? or does mcCain = Mccain = Mc Cain? |
|
|
|
|
|
|
|
|
excluding data from query result