SQL Server 2012 :: Conditional Join Between Two Tables
Oct 4, 2015
I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.
Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.
What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?
--- First Create two tables
Create table tabA
(cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100))
Insert into tabA values (NULL,'A1','A2')
Select * from tabA
create table tabB
[Code] .....
View 7 Replies
ADVERTISEMENT
Aug 16, 2015
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like -
JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date
FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK)
ON User_Count_Id = Analyst_Id_fk
WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana
ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk
)
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
View 4 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Oct 11, 2015
I have two tables, INCIDENT and PEOPLE
INCIDENT has two fields, ID and Date
PEOPLE has three fields, INC_ID [foreign key to INCIDENT.ID], PersonIteration, Complaint
There is always only one incident, but there can be one or more people involved in each incident.
I'd like to retrieve incidents, but have it include information from all involved person on one line.
No matter how I have tried to join these tables, I am ending up with one record each for each person involved in an incident, like so
ID PersonIteration Complaint
1 1 Head
1 2 Neck
1 3 Shoulder
I would like to join in this fashion:
ID PersonIteration1 Complaint1 PersonIteration2 Complaint2
1 1 Head 2 Neck
Is there any way to do this?
View 5 Replies
View Related
Dec 11, 2013
I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.
I can't set them both up as CTEs and query, the statement gets truncated.
I can't use a temp table because those get dropped when the query finishes.
I can't use a real table because the insert statement gets truncated.
Do I have any other good options, or am I in Spacklesville?
View 7 Replies
View Related
Feb 6, 2015
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
View 6 Replies
View Related
May 12, 2014
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],
[code]....
View 4 Replies
View Related
Sep 22, 2014
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT
b.[PBCat]
,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]
[Code] ....
View 2 Replies
View Related
Oct 22, 2014
I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue
--===== Create the test table with
CREATE TABLE #workorder
(
Id varchar(20),
wo varchar(10),
wodate datetime,
amount float
)
GO
CREATE TABLE #revenue
[code].....
View 2 Replies
View Related
Jan 7, 2015
I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
View 1 Replies
View Related
Jun 23, 2015
I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.
CREATE FUNCTION prici.[fn_pricipalamt]
(
-- Add the parameters for the function here
@Tcode char(10),
@SecTypeCode1 char(10),
@SecTypeCode2 char(10),
@TradeAmount float,
[Code] .....
View 2 Replies
View Related
Aug 9, 2015
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have attached my data model for reference.
View 9 Replies
View Related
May 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
View 6 Replies
View Related
Jan 30, 2007
Im faced with the following design issue..
on my site there are different profiles: a city profile, a restaurant profile and a user profile.
in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...)
Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...)
User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...)
as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile.
forumID in each of the above tables is a FK to the PK in tbForums:forumID intforumname nvarchar(50) (...)
Now imagine the following:
a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA?
a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that!
so if a user would click on forumID 2 (is there life in space?)
I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)
select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2
I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
View 6 Replies
View Related
Oct 4, 2006
I have three tables ...
tblWine tblSpecialOfferWine tblSpecialOffer
ID Name ID WineID SpecialOfferID ID Name IsLive
===================== ============================ ==========================
1 Mouton Rothschild 1 1 1 1 February Offer 0
2 Lafite Rothschild 2 1 2 2 March Offer 1
3 Chateau Teyssier 3 2 1
... and the current query I am using is the following along with it's result set ...
SELECT
tblWine.ID AS WineID,
tblWine.Name AS WineName,
tblSpecialOffer.ID AS SpecialOfferID,
tblSpecialOffer.Name AS SpecialOfferName
FROM
tblWine
LEFT OUTER JOIN tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
LEFT OUTER JOIN tblSpecialOffer ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild 1 February Offer
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild 1 February Offer
3 Chateau Teyssier NULL NULL
... but the result set I want is All wines and their associated specials offers but only show details of the offer if the offer is live like so ...
Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild NULL NULL
3 Chateau Teyssier NULL NULL
... I've tried putting where clauses like ...
WHERE tblSpecialOffer.IsLive = 1 OR tblSpecialOffer.IsLive IS NULL
... but then that hides the wines that were on a previously associated on a special offer but is no longer live (see Wine #2).
Any ideas on the query I should be using?
Note: The queries and data above were made off the top of my head so may contain mistakes.
View 4 Replies
View Related
Apr 27, 2008
Table A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/07
Table B
Registration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 235 07/31/07 1
1003 236 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
output
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 777 06/01/07 6/4/07 5
003 234 07/31/07 7/30/07 3
How can I do this conditional join ?
View 1 Replies
View Related
Jul 23, 2005
Hi,I am trying to change an SP from dynamic SQL to proper SQL but I can'tfigure a way to conditionally add extra parts to the statement. Howcan I do the equivalent of the following?DECLARE @arg NVARCHAR(10)SELECT a.i, a.xFROM aTable aIF LEN(@arg)BEGININNER JOIN bTable b ON a.[id] = b.[id]ENDConditionally adding the INNER JOIN is very easy when building up a SQLstring but I can't see how to do it in pure SQL?Thanks.
View 5 Replies
View Related
May 28, 2008
I have these two tables =>
Table a
Name CEN VID
1. AA 01 11
2. BB 01 11
3. CC 02 12
Table b
CEN VID CName
01 11 ZZZZ
NULL 12 YYY
02 PR XXX
I want to join either on CEN or VID.
1. Join with CEN but If CEN is NULL in table b than the inner join will be VID (automatically in single query)
2. Join with VID but if VID in table b = 'PR' than the inner join will switch to CEN (automatically)
How can I approch this in a single SQL query.
Help me...
View 4 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Oct 24, 2006
I want to filter some search results using an inner join.The criteria are passed as a parameter.If the parameter has value, I want to inner join with it. If there is a NULL value, I just want to ignore the inner join. I don't want to:1) Build a string and do an exec() - too slow and hard to maintain2) use a (@parm IS NULL) OR ('inner join') AGAIN, too slow Suggestions appreciated
View 4 Replies
View Related
May 6, 2014
Table 1
PropertyId
1
2
3
4
5
6
Table 2
PropertyID PropertyCode PropList ID
1 2 45
3 4 67
5 6 89
Table 3
PropertyID IType
1 3
2 3
3 11
4 11
5 3
6 3
Target Table
PropertyID PropertyCode
1 45
2 45
3 67
4 67
5 89
6 89
Step1: Join Table 1 and Table 3
Steo 2: If Table 1.PropertyId.Itype = 3 Then Join Table 1 and Table 3 on Table1.PropertyID = Table2.PropertyId
Else
Step 3: If Table 1.PropertyId.Itype = 11 Then Join Table 1 and Table 2 on Table1.PropertyID = Table2.PropListId
View 4 Replies
View Related
Oct 10, 2007
Hello,
I want to write a query that joins data in a different table based on a column value. The table is for a "Playlist" and holds play list items. The items can be video, audio, images, etc. The playlist table looks like this:
Table_Playlist
-----------------
ID (int)
MediaType (char)
MediaId (int)
Table_Audio
-----------------
MediaId (int)
Table_Video
--------------
MediaId (int)
If the Table_Playlist.[MediaType] column value = "Audio" then I want to join to the Table_Audio table. If the value = "Video" then I need the video table.
Hope that makes sense. Thanks
View 1 Replies
View Related
Apr 27, 2008
Table A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/071
Table B
REgistration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 234 07/31/07 1
1003 235 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
Table B
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 778 06/01/07 6/4/07 2
1003 234 07/31/07 8/1/07 7
How can I do this conditional join ?
View 6 Replies
View Related
Mar 5, 2014
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
View 7 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Jan 31, 2007
I have a merge join that does a full outer join. I then have a conditional split that will breakout by unchanged, insert and update. The update is what I am having a problem with. The conditional split for the update is a follows:
!ISNULL(HISTORICAL) && !ISNULL(TRANSFORM)
Now I believe the problem is related to spaces in the key field let me explain.
The join field is defined as [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
There was an original mismatch between the two tables of VARCHAR(14) & Char(14) but now there both Char(14).
What's interesting is that the few records with Alpha characters correctly does the update but the records with starting numeric data all go down the insert path.
The data of the join columns look like
'308811151 ' - 5 Spaces This Data incorrectly goes to insert path
'TSTRWR02 ' 6 Space This Data correctly goes to update
The data is grouped and sorted by the Key field and the historical and transform column contain a 1 for all records.
while the Doc_nbr which is the key
I tested the join in SQL server and it works with and without the spaces.
From SSIS I also tried RTRIM in the SQL command of the source in addition to substring the first 9 with the right trim and I always received the same results.
I am guessing there is something going on with the not isnull in the conditional split but I can't figure it out if I am missing something or if this is a BUG.
Three paths of data after the merge join and the conditional split
Insert
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
206352185 ,,1,
209900165 ,,1,
Update
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
NEE ,1,1,NEE
New ,1,1,New
Unchanged
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
,1,,0000521096
,1,,0000685216
Any suggestions, am I missing the obvious?
LL
View 3 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related
Jan 13, 2014
These two T-SQL statements return the same results.
If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN
-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER
USE AdventureWorks2008R2;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
USE AdventureWorks2008R2;
GO
SELECT DISTINCT Production.WorkOrder.ProductID
FROM Production.Product
INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
View 9 Replies
View Related
Oct 23, 2015
What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
[code]...
View 9 Replies
View Related
Mar 4, 2008
I have been reviewing the video series at this site and have found them very helpful but have a need...
I have two data tables one of which is quite large and another table that is small. They have at least one exact field in common, lets call it room number. How do I recognize both tables (in different databases) and join them so I can search a unique item in one table and select its associated data in the other table? I don't image there is a video on this topic for this VB.NET student?
View 1 Replies
View Related
Jun 11, 2014
We have a query that joins column A int which is an int onto column B with contains only int's but was created as a varchar and can't be changed to an int at the moment.
Casting column a as a varchar in the ON of the join to left join seems to void the index altogether and the query just runs for every.
We are talking a few hundred million rows of data in each table.
Temp solution is select into a #Hash table as correct data type and index then use the #Hash table in the join.
View 6 Replies
View Related
Jun 18, 2014
I came across this structure today and haven't seen it before:
SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2
I can follow along until the second "ON".
View 9 Replies
View Related
Oct 27, 2015
This query below is giving product join for me, is there a way to avoid this?
SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)
View 5 Replies
View Related