SSIS: JOINING TABLES FROM TWO SERVERS - BETTER WAY THAN SORT -&&> MERGE JOIN?
Jan 20, 2006
Setup:
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?
When select this data with an order by like: select test from table_2 order by test The result will be:
Code Snippet
test 1-1.00.00.00 1000 2000 If you sort the data by the SORT block of the SSIS the result will be:
Code Snippet
test 1000 1-1.00.00.00 2000This is annoying and dangerous, because it causes the next bug.
2/ Two datasources sorted by ORDER BY clause can give problems in a Merge Join.
If you have 2 data sources both correctly sorted by an order by in the query. When you join these 2 datasources with a Merge Join, you can lose some records in the dataflow. This happens with larger datasets than examples above.
In the first image as can be seens i have 2 different data sources and then they are being joined using "Merge Inner Join". The "sort" is on BusinessEntityID column of Person table and "Sort1" is on "PersonID" of Customer table. The merge join of these 2 result in 19,119 rows.
On the other hand, if i use single data source and use a query with inner join on  tables  used in the first image (ie. 2 tables being used in 2 different data sources) as depicted in second image. Also,  since merge cannot operate without SortKey i have defined TerritoryID as sort key in the advanced editor. The number of rows i get after this is "10,274". My select query was :
SELECT P.BusinessEntityID, P.PersonType, P.Title, P.FirstName, P.MiddleName, P.LastName, P.Suffix, C.TerritoryID FROM stg.Person AS P INNER JOIN stg.Customer AS C ON C.CustomerID = P.BusinessEntityID ORDER BY C.TerritoryID;
According to me, it should have been the same as in first case i am using merge inner join and in second case i am using SELECT query with inner join. Upon drilling down i found that in the first case , my sort keys are BusinessEntityID  and PersonID, if i modify this to CustomerID  and BusinessEntityID as this is my join condition (in ithe inner join query shown above), i get the desired output. What i was wondering was, how  the sort order change the Join Condition?
Hello,I am a beginner with SSIS. I have a scenario ...
I have source DB 'MySource' and a destination DB 'MyDestination' I have created a SSIS to push data from my source data base to detination DB.
I have a scenario where in i have to push data from the source DB to the Destination DB , depending on a join condition for which the data is in Destination.
I would not be able to acces both the DBs while writing query to pick data from .. source DB. How do iproceed ..
let me give an exmple
Source DB:
Table1
---col1 --col2 --col3 -- -- --coln
Destination DB;
Table2: --col1 --col2 --col3 -- -- -- col n
Table 3:
--col 1 --col 2 -- col 3
Now i Have to move data to table 3 into destination DB getting all the records which pass a join on table1 and table2 on cloumns 1 ,2 and 3 ..
I have only read access on the the Source DB.. pls let mw know how i can solve this scenario in SSIS.Thanks and Regards,Raja Tirumala Rao
Hi guys! I'm trying to figure out how to join 3 tables, but I can't seem to find a solution. What I want to do is to put table 1, table 2 and table 3 into table_merged. table_merged = table 1 + table 2 + table 3 Is it possible to merge tables even if they have different fields?
I need to take certain items of data from four different tables and out them into one table.
Unfortunately my source data's version of SQL does not support the LEFT JOIN keyword which has left me with a bit of a problem.
I saw the merge join in SSIS and used it to get data from two of the tables and stick them in the destination and it all worked fine.
That got me thinking, is it possible to create a second merge join transformation within the same data flow task for the remaining two tables and then join the output of both the merge joins to give me the data I need from all four tables in one output?
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 I would simply do the following:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
What do I need to do to perform the same operation in SSIS world?
I am working on an ssis package and i find an problem while using the merge join for merging 2 OLEDB Data sources .
data source 1 is : - The table formed my an sal server comand , that out put is given to a multicast since i want to sare that output amoung 2o other tables.
So the the left input for the merge join is OLEDB source , which contains direct data from source table
I am usong Inner join on one column
The problem is i am not getting the expected rows as out put of merge .
I tried to join the two tables in sqlserver query window and i am getting expected result
What could be the problem
The first table is
Reservations.ReservationManual
second table is Out put of the following query
Select Distinct B.ReservationID as R
from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C
Where
A.propertyID = B.PropertyId And
C.ReservationID = B.ReservationID And
getdate() >=C.Until +A.ReservationOffLineDays
i am not getting the expected result here in SSIS package merge join
But if i try to execute the following in query editer in management studio i am getting the expected result !!
declare @temp as table
(ResID Varchar(50)
)
Insert into @temp
(ResID)
Select Distinct B.ReservationID as R
from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C
Where
A.propertyID = B.PropertyId And
C.ReservationID = B.ReservationID And
getdate() >=C.Until +A.ReservationOffLineDays
select * from Reservations.ReservationManual A , @temp b
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:
Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows: 1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND 2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND 3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey
Hi, I am pretty new to SSIS. I am transferring some rows from 2 source tables to 1 destination table. The 2 source tables have 1000 rows.They act as the 2 inputs to a merge join transformation where i perform the join between the 2 tables based on a couple of fields. But for some reason the output of the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows? How do i solve tis problem?
Can the collation used by SSIS be changed or influenced during install or run time? We have found that our databases, that use a mandatory "LATIN1_GENERAL_BIN", have incorrect SSIS Merge Join output. Changing our database collation in testing didn't make a difference. What matters is the data. Which Windows collation is SSIS using?
put in a Sort task before the Merge Join task as setting advanced properties isn't enough (as described by Eric Johnson here --> [URL] ......
We are using 64-bit SQL Server 2008 R2 w/ SP1 in Windows Server 2008 R2 ENT w/ SP1.
UPDATE from ETL team: Explicitly ordering the source with "COLLATE Latin1_General_CS_AS" seems to have the same effect as using a separate sort task. We don't feel that we can rely on our findings, however, unless we have documentation that this collation is what is behind SSIS.
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
Does this look correct for MERGE SYNTAX?I want to INSERT INTO the target table,new service contracts sold with the start date,from historical table by month, only if they don't exist in the target table, doing nothing if matched. . . but the source table is indexed Fiscal Period descending and I have multiple renewal versions of the same contract number in following years. . .
MERGE dbo.SERVICE_CONTRACTS_NEW AS Target USING (SELECT r.Customer_Acct, r.[Contract], r.Start_Period FROM dbo.ACTIVE CONTRACTS r ORDER BY r.FiscalPeriod ASC) AS Source ON Target.Account_Nbr = Source.Customer_Acct AND Target.[Contract] = Source.[Contract] WHEN NOT MATCHED BY TARGET THEN INSERT (Account_Nbr, [Contract], FiscalPeriod_Start) VALUES ( Source.Customer_Acct ,Source.[Contract] ,Source.Start_YYYYMM );
Just wondering if I am an isolated case or just doing stuff other people don't. Ive created around a dozen packages for importing various types of data into a datawarehouse and some of the packages have random crashes. Over time I have been trying to tie together what the crashing ones have in common and now believe I have completely isolated it: Sort + Merge inside a dataflow in a for each loop. The more sorts and merges in the flow the more likely the problem is to occur.
This week I created 2 identical packages to import some XML... one used some gnarly complicated substrings and such to extract some data and add it as derived columns while the other used the XML correctly that produced 2 streams (one being meta data - other being detail data) and sorted + merged them. The gnarly substring package could run all night (processes about a file every 2 or 3 seconds so you can do the math) while the merged data flows crashes somewhere between 1 and 4 hrs of running - usually right around 3 hrs (also processes about 1 file every 2 seconds). The file it crashes on wil be completly random and not related to file size - I've had crashes on files with as little as 1 row of data and 1 row of metadata and as big as 1 row of metadata and 100,000+ rows of data. Another more complicated package I created has 4 merges (and thus 8 sorts) and it will crash anywhere between 5 minutes and 20 minutes of running.
The crash is usually one where the whole process just shuts down with a memory dump but occasionally I have gotten errors about out of memory or warnings about threads leaking buffers.
I am trying to do some "research" to see if others have this problem. I've spent 1.5 months writing all ETL jobs in SSIS and its gotten to the point management has asked me to explore other platforms. I do have a case open with Microsoft on the package with 4 merges - but we've spent weeks now just trying to get debugging tools to get them some useful info when the process crashes. I am running SP1 and kb91822.
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _SubCategories.SubCategoryName, _SubCategories.SubCategoryName, LinkName FROM _Links INNER JOIN _SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId INNER JOIN _SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _Links_Categories.CategoryName, _Links_StatusCategories.StatusName, LinkName FROM _Links INNER JOIN _Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId INNER JOIN _Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail from tblcodes 1, tblelements 2
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.) THANKS
The problem:I'm updating a report to be "multi-language" capable. Previously,any items that had text associated with them were unconditionallypulling in the English text. The database has always been capable ofstoring multiple languages for an item, however.Desired output:Given the test data below, I'd like to get the following resultsselect * from mytestfunc(1)Item_Id, Condition, QuestionText1876, NOfKids <= 10, This many children is unlikely.select * from mytestfunc(2)CheckID, Condition, QuestionText1876, NOfKids <= 10, NULLThe current SQL for my UDF:CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,tblQuestionText.QuestionTextFROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =tblQuestionText.Item_ID)WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR(tblQuestionText.LanguageReference IS NULL)))Test data:CREATE TABLE [dbo].[tblCheck] ([Item_ID] [int] NOT NULL ,[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblCheck] ADDCONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED([Item_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblLanguage] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Priority] [int] NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Spoken] [bit] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADDCONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] ADDCONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR[CreationDate],UNIQUE NONCLUSTERED([Priority]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblQuestionText] ([Item_ID] [int] NOT NULL ,[LanguageReference] [int] NOT NULL ,[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SameAs] [int] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblQuestionText] ADDCONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED([Item_ID],[LanguageReference]) WITH FILLFACTOR = 90 ON [PRIMARY]GOINSERT INTO tblCheck (Item_ID, CheckDescr)VALUES(1876, 'NOfKids <= 10')INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(1,1,'English', 1)INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(2,2,'Espanol', 1)INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)VALUES (1876, 1, 'This many children is unlikely.')Any tips or pointers will be appreciated. Thanks.
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.
My scripts are as follows: Main View SELECT CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, CUSTOMER_POLICY_DETAILS.POLICYNUMBER, --TotalPayable IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalPayable, --NetPremium IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS NetPremium, --IPT IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS IPT, --Fee IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS Fee, --TotalCommission IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalCommission
FROM ACCOUNTS_CLIENT_TRAN_LINK INNER JOIN ACCOUNTS_TRANSACTION ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID INNER JOIN ACCOUNTS_TRAN_BREAKDOWN ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID INNER JOIN LIST_TRAN_BREAKDOWN_TYPE ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID INNER JOIN CUSTOMER_POLICY_DETAILS ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID INNER JOIN SYSTEM_INSURER ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID INNER JOIN SYSTEM_SCHEME_NAME ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID INNER JOIN CUSTOMER_INSURED_PARTY ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID WHERE CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'
GROUP BY CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, ACCOUNTS_TRANSACTION.Transaction_ID, CUSTOMER_POLICY_DETAILS.POLICYNUMBER
Add on View 1 CREATE VIEW TOPCARDPA AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'
Add on View 2 CREATE VIEW TOPCARDRESC AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'
Join Result Script SELECT TOPCARD.AUTHORISATIONUSER, TOPCARD.AUTHORISATIONDATE, TOPCARD.TRANSACTION_CODE_ID, TOPCARD.SURNAME, TOPCARD.INSURER_DEBUG, TOPCARD.SCHEMENAME, TOPCARD.POLICYNUMBER, TOPCARD.TotalPayable, TOPCARD.NetPremium, TOPCARD.IPT, TOPCARD.Fee, TOPCARD.TotalCommission, TOPCARDPA.SELECTED, TOPCARDRESC.SELECTED FROM dbo.TOPCARD TOPCARD INNER JOIN dbo.TOPCARDPA TOPCARDPA ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID INNER JOIN dbo.TOPCARDRESC TOPCARDRESC ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID
I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
Hi. I'm new to SQL, and need to join 2 tables... any hints??? table1:id (int)title(varchar(50))body(text) table2:id (int)title(varchar(50))body(text) somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information: table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" I would like to get... id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body" Does anyone know how to get this? I am fairly flexible if i need to change things... cheers, eh!
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
Hello all, I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do??????? Mahmoudona
I've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:
-A users Table -A Posts Table -A Replies Table.
Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.
1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.
I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.
I am using MS SQL Server 2005 on Windows XP with SQL Server Management Studio Express CTP. I am having issues with my query on joining 2 tables I created using BETWEEN to restrict the Salary. Table 1 is called Employee and Table 2 is called Job_title. The column Job_title_code is the only column that is in both tables which is how I am joining both tables. Here is my SQL query:
Code:
SELECT Employee.*, Job_title.*
From Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code
WHERE Salary
BETWEEN 50000 AND 500000;
The results I am getting back are:
Msg 207, Level 16, State 1, Line 7 Invalid column name 'Job_title_code'.
I can't figure out how to fix this error. I feel like I have tried everything, so any help will be much appreciated. Thank you.
Hi, I have a table with fields as partnerid, contractno. The partnerid field has the Id number which can be a supplier or a customer. I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.
Data in table PId ContractNo 20045 1567 435 1567 123 1567 345 1678 1004 1678
I need to display the data in the following format.
jack 20 Melbourne AAA Nick 30 Bendigo BBB Russ 28 Sydney AAA Marty 31 Perth AAA
Table 3
name age city Position
jack 20 Melbourne Manager Nick 30 Bendigo Manager Russ 28 Sydney Clerk Marty 31 Perth Manager
Table 4
name age city datejoined
jack 20 Melbourne 09-09-2001 Nick 30 Bendigo 08-05-2001 Russ 28 Sydney 10-12-2000 Marty 31 Perth 11-11-1999
I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.
so the result should be
name age city position datejoined
jack 20 Melbourne Manager 09-09-2001 Nick 30 Bendigo Manager 08-05-2001 Russ 28 Sydney null null
my query
SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined FROM Table1 a, Table2 b, Table3 c, Table4 d WHERE a.age=b.age and a.name=b.name and a.city=b.city and b.age*=c.age and b.name*=c.name and b.city*=c.city and b.position='Manager' and b.age*=d.age and b.name*=d.name and b.city*=d.city
THE RESULT IS
jack 20 Melbourne Manager 09-09-2001 Nick 30 Bendigo Manager 08-05-2001 Russ 28 Sydney null 10-12-2000
When I try to join table4 with table i am getting a exception
Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN
I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code: select groups.group.id, groups.group.name from groups, users where groups.disabled='1' and users.user_id = $user_id and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
