Join On One Column

Sep 8, 2014

I have two tables.

One called Boats with fields

IDSerialNo AreaCompany

There is another table called Equipment with fields

ID is the same in Boats as the ID in Equipment (Foreign Key).There can be multiple Parts linked to ID.I want to be able to return all the rows from Boats but only the Parts for each ID from Equipment. I dont want the other fields appearing more than once in the query.

View 4 Replies


ADVERTISEMENT

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

SQL Query To Join A Column On A Row

Apr 3, 2007

I have 2 tables, my vehicle data table and my config table. I need a
query to join them by a datarow and a data column. Heres my tables...config table--------------------id   name   type--------------------1    make   varchar2    model  varchar3    color  varcharveh table--------------------------id   make   model   color--------------------------1    chevy  s10     white2    ford   ranger  silver2    chevy  blazer  brownrecordset needed for veh.id=1---------------------------id   name   type     value---------------------------1    make   varchar  chevy2    model  varchar  s103    color  varchar  white  Thanks for any helpRyan 

View 1 Replies View Related

Multiple Column Join

Aug 16, 1999

I have two tables, one of which is a key table with a subaccount number and a set of attributes that define that subaccount. I am trying to join this key table with the table with all the attributes and come up with one table of subaccounts. The Subaccounts should only lookup the attributes associated with them, not all of the attributes, so I put OR [attribute] IS NULL in the WHERE clause so it only matched on the appropriate columns. This worked great in an initial test with two attributes but when I put all 9 attributes in it crashed with this message "Msg 415, Level 16, State 1
The current query would require an index on a work table to be built with 15 keys. The maximum allowable number of keys is 16"
like this
CREATE VIEW subaccounts
(Subacct_no, balance)
AS SELECT
(s.Account_No + "." + r.Subacct_Ext, S.Balance)
FROM Acct_Rcd r, Subacct_Key s
WHERE
(s.attr1 = r.attr1 OR s.attr1 IS NULL)
AND (s.attr2 = r.attr2 OR s.attr2 IS NULL)

View 3 Replies View Related

ORDER BY - Join Column

Apr 6, 2007

Hi, I am writing a small search engine.
There are two tables. The first one holds the search engine main index, the second one is link table.
I have the following query that retrieves results. I would like to sort the results by:
dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query?
I'm using MSSQL 2005. Thanks.
PS. Function OCCURS2 returns number of occurrences of one string in other.

Code:

select
id as Id,
uri as ElementUri,
size as Size,
modified_date as ModifiedDate,
title as Title,
text as Text,
dbo.OCCURS2(LOWER(:query),Title) as TitleOcc,
dbo.OCCURS2(LOWER(:query),Text) as BodyOcc
FROM se_index
WHERE (title LIKE :query) OR
(text LIKE :query) OR
(id IN
(SELECT se_links.target_index_id
FROM se_links INNER JOIN
se_index AS se_index_1 ON
se_links.target_index_id = se_index_1.id AND
se_links.anchor LIKE :query))

View 1 Replies View Related

How To Join In As A Partial Column

Oct 2, 2013

We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.

SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC

View 5 Replies View Related

Join With Parsing From Another Column

Nov 26, 2013

How to accomplish a join when the join condition has to be parsed from another column? I need a list of all the exchange part numbers with exchange part pricing, but need the description from the original part number. Example:

Part Number Description Cost
123-456-1 A ROUND THINGIE 5
150-387-1 EXCH - 123-456-1 2

Needed record is
Part number ExchangePart Description Cost
123-456-1 150-387-1 A ROUND THINGIE 2

Something like

SELECT PART# as exchangepart,
case
when substring(descr, 1,4) = 'EXC-' then trim(substring(DESCR, 5, 25))
when substring(descr, 1,3) = 'EXC-' then trim(substring(DESCR, 4, 26))
when substring(descr, 1,6) = 'EXC - ' then trim(substring(DESCR, 7, 23))
when substring(descr, 1,5) = 'EXCH-' then trim(substring(DESCR,6,24))

[Code] .....

View 2 Replies View Related

How Pass Column To Udf In Join

Jul 23, 2005

Group,Passing inline values to a udf is straightforward. However, how or is itpossible to pass a column from the select of one table into a udf thatreturns a table variable in a join to the original table. The goal is toexplode the number of rows in the result set out based on the result of theudf. Although the example I am providing here is simplified, we are tryingto parse out multiple values out of a text column and using a select into sothe result will be one row for each of row in the original table compoundedby the number of occurrences in the text field.(I know bad table design but that's out of my control)Thanks,Raycreate table TableOne(Col1 int,Col2 char(5))goinsert TableOne values (1, 'One')insert TableOne values (2, 'Two')insert TableOne values (3, 'Three')goCreate Function dbo.udfTableTwo(@Id int)RETURNS @tbl TABLE (TID int, TChar char(1))ASBEGINDeclare @test intset @test = @IdWhile @test >= 0BeginINSERT @tbl VALUES (@Id, Char(65+@test))set @test = @test - 1EndRETURNEND--worksselect a.*, b.* from TableOne a join dbo.udfTableTwo(2) bon a.col1=b.TID--Fails with Line 1: Incorrect syntax near '.'.select a.*, b.* from TableOne a join dbo.udfTableTwo(a.col1) bon a.col1=b.TIDdrop table TableOnegodrop function dbo.udfTableTwogo

View 6 Replies View Related

Outer Join Column

Mar 17, 2006

Thanks in advance for any help.I'm trying to attempt the following with one table/query.I have a table called TABLE_1It has the following fields:SSNTITLELOCATION322EXDALLAS568REPFT_WORTH536EXDALLAS485SLSYOUNGSTOWN854BROFEEDER258EXEVANSVILLE478TEMPTROY861SLSDALLASI want toselect SSN, TITLE, LOCATIONfrom table_1where ssn = ('322', '536', '258', '478', '861')and title = 'EX'and location = 'DALLAS'The problem, however, it that I want all of the SSN records on my list.My table should look like this:SSNTITLELOCATION322EXDALLAS536EXDALLAS258nullnull478nullnull861nullnullI can easily do this on two tables with a left outer join. I do notknow how to accomplish same (theory) with one table.Any help on how to do this would be appreciated. Thanks

View 8 Replies View Related

To Return Distinct Column Using INNER JOIN

Apr 23, 2008

Hello all,

I am using INNER JOIN to connect 2 tables together but I wish it to return distinct columns instead of repeating itself !

eg.
Current output would be:
UserID Name UserID OrderID
1 John 1 5
2 Bob 2 6

I want it to be:
UserID Name OrderID
1 John 5
2 Bob 6


I need to use SELECT * as there are many many columns and wish to save time :)


Cheers,

James

View 3 Replies View Related

Join Two Columns Together To Form A New Column

Feb 1, 2014

I'm trying to join two columns together to form a new column

My code is basically in the form of can't post the actual since it would be cheating--school assignment

SELECT Column1Name,Column2Name, Column3Name,Column4Name,
Column1Name+Column2Name AS NewColumn1
Column3Name+Column4Name AS NewColumn1
FROM OriginalTable

View 1 Replies View Related

Inner Join Based On Conditional Column

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

Newb - Mutiple-column Join

Mar 23, 2007

Hi,very new to SQL queries, and strugling with join conceptI had to do a join based on a single field:select*fromtableA, tableBwheretableA.value = tableB.value(+);this works finebut how can i do the same thing while comparing multiple columns ... iwas thinking something like this: (obviously doesn't work)select*fromtableA, tableBwhere[tableA.value1, tableA.value2] = [tableB.value1,tableB.value2](+);Is there some sort of "tuple" comparison I can do?Thank you.

View 2 Replies View Related

Join To Select A 'weighted' Column

Mar 28, 2006

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @inv table (item varchar(50), supplierid int)

declare @supplier table (supplierid int, weight int)

set nocount on

insert into @inv values ('item1', 1)

insert into @inv values ('item1', 2)

insert into @inv values ('item2', 2)

insert into @inv values ('item2', 3)

insert into @supplier values(1, 30)

insert into @supplier values(2, 20)

insert into @supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @supplier ps2 join

(select item, min(ps.weight)'weight'

from @inv inv join @supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

View 3 Replies View Related

Dynamic Join Based On Column Value??

May 4, 2007

Hi,

I have three tables.

The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.



I need to do a join to a second table based on the MasterID...



However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.



Make sense?



Anyone got any pointers?



Thanks in advance

James

View 8 Replies View Related

Join Two Datasets From A Similar Column

Apr 23, 2008

Hi, I was wondering wich is teh easiest way to solve this:


I have one data set that comes from a sql query with many columns.

I have another data set that comes from a txt file that I am reading.

Both data set have one column in common (for example ID).

In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.

So, which is the easiest way to solve this?
Thanks!

View 6 Replies View Related

SP With Inner Join - Update Table Value (Single Column)

Oct 16, 2015

I'm trying to do the following:

update a tables value(a single column), based on a query of another database, using a common column.

Here's what I've cooked up so far:

Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no

[Code] ...

print @trackingNoThe error it's returning is:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table), then update P21 oe_hdr table with "trackingNo from Shipping_data

View 8 Replies View Related

Move Column Of Data To Another Table With A Join

Oct 12, 2013

I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.

I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.

This is what I have tried so far:

Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum

View 1 Replies View Related

Join Tables With Almost Identical Column Data

Oct 29, 2014

Selecting the data from these two tables? The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.

table is 'adrmst'

Host External IDAddress NameAddress Line 1Address Line 2City StatePostal Code
A000042401 T-3803VC 1530401 00 WENGER STTOPEKA KS66609
A000042402 CO INC 960 PP TOMLIN MILL RDSTATESVILLENC286258332
A000042403 CO INC 1420 PP BLVD GARYSBURGNC278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEEFL347463415
A000042405 CO INC 962 PP COMMERCE DRVALDOSTAGA316011206

table is 'shipment'

Shipment IDHost External IDcar_move_idP_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862TAGSDQD
42403 42403 SDQD_00863TAGSDQD
42404 42404 SDQD_00863TAGSDQD
42405 42405 SDQD_00863TAGSDQD

View 4 Replies View Related

Dynamic Column Sorting After Merge Join

Feb 25, 2008



Hello,
I have data coming in from two sources, one being SQL and the other being Oracle. The end result needs to be a CSV file with the columns in a specific order. I have a Data Flow task setup that takes both sources and does a Merge Join on them. I can add a Sort Transformation and manually set the sorting of all 156 columns that end up going to a CSV file destination. However, I have a table setup that holds the names of the 156 columns and the order that the CSV file expects them to be in. I would much rather do this step dynamically as the column names and order may change in the future. Anyone who has used the Sort Transformation for a large number of columns knows how tedious it can be and how adding a column in the middle will cause you to change the sort # for each of the columns that come after it.

So I added a Script Component between the Merge Join and the Flat File Destination hoping that I could alter the order of the columns there. However I added the following code and found that the SortKeyPosition is ReadOnly.


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)


Dim column As IDTSInputColumn90

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection



column.SortKeyPosition = 1

Next

End Sub

I was hoping to add some code to get the sort index from my table for each of the columns and set it to the SortKeyPosition. Has anyone out there done this before or seen an example that might point me in the right direction? I've searched for 2 days without coming up with much.

Thanks!


View 5 Replies View Related

Lookup Transformation Join Column Types

Sep 1, 2006

Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...

View 3 Replies View Related

Join-Problem (select Two Columns Out Of One Column)

Oct 27, 2007

Hi I'd like to select two columns out of one column from another table. Example from source table:





Code Block

ID ArtNr EAN
1 4711 51323135
1 4712 84651213
1 4713 84351322
2 4711 86431313
2 4714 23546853
1 4714 54646545
2 4715 64684353
2 4716 65435132


I want to join this table to a base table with one select, and generate the following output. The ID identifies If the ArtNr has an EAN1 or EAN2:







Code Block

ArtNr EAN1 EAN2
4711 51323135 86431313
4712 84651213
4713 84351322
4714 54646545 23546853
4715 64684353
4716 65435132

View 20 Replies View Related

Transact SQL :: Create Join On Aliased Column

Jun 20, 2015

This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort

Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result]
From foobar As fb
Inner Join chezter As cz
On fb.username = cz.username
and [fb].[Last Result] = [cz].[Last Result]

View 3 Replies View Related

Transact SQL :: Modifying A JOIN When New Column Is Introduced

Jun 25, 2015

At present I have a logic implemented like below code example where I can get a combination of columns cid and ani in 'config' table to which I have to map columns cid and ani present in my 'current' table.

Now, a new column pid is added to both 'config' and 'current' tables, like

alter table config add pid integer 
alter table current add pid integer

Now I can have data in a combination of cid and ani (where pid can be NULL) or cid and pid (where ani can be NULL) or a row where all cid, ani and pid can have values. In this scenario, how should I make the changes in the current implementation given below?

create table current (cid integer, ani integer, resetdate datetime, threshold integer)
create table config (cid integer, ani integer, resetdate datetime, threshold integer )
SELECT cid, ani from
(
SELECT A.cid,
A.ani,
CASE WHEN C.cid is null then B.resetdate ELSE C.resetdate END AS resetdate,

[Code] ....

View 2 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

SQL Server 2008 :: How To Return 1 Particular Column From Another Table In Join

Apr 25, 2015

I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?

The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join

My photos table is constructed as follows:

CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,

[Code] ....

Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.

View 3 Replies View Related

T-SQL (SS2K8) :: Dynamic Join On EXECUTE Table Column

Jun 23, 2015

Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e

select * from (select table1.theSql,table1.userid from table1 ) as a

inner join (execute a.thesql) as b

on a.userId=b.userid

View 9 Replies View Related

Ambiguous Column Names In Multi-Table Join

Jun 21, 2006

Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S

View 6 Replies View Related

How To Do A Join Using Foreign Keys In Stored In A Database Column?

Apr 17, 2007

I have a database column that stores a comma delimited list of foreignkeys. Would someone show me how to do a join using the values from alist stored within a record?For example, a record in tbl_cds.genre_id might have a value of "2,5, 6" corresponding to genre_ids 2 , 5 and 6. I want to jointbl_cds.genre_id to tbl_genre.genre_id using the values in that datafield.It seems I need a loop like this:SELECT * FROM tbl_cdsWHEREBegin Looptbl_cds.genre_id[i] = tbl_genre.genre_idEnd Loop.Would someone give me the correct syntax?Is there an alternative method that would create less overhead?Sorry for such a novice post.

View 2 Replies View Related

Duplicates Again! UNION Join - Remove Records With Column Diff.

Sep 9, 2004

Hello All,

We all were new at one point.... any help is appreciated.

Objective:

Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)

Reason:

I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.

My progress/where I'm stuck:

Here is my first query/union:

SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];

As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....

an example of duplicates that I must remove are as follows:

142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)

I am trying to run another query on my first query results so I don't mess my first query up. Here it is:

SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";


Please Help! Thank you in advance.

--------------------

5 minutes away from pulling my last one!

BaldNAskewed

View 7 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 Replies View Related

Query Performance Problems With Join On UDF-based Computed Column

Aug 25, 2007

We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:

select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn

but this one sends the CPU usage of SQL Server to 99% for a very long time:

select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn

The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).

Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?

This is SQL Server 2005 SP2, btw.

View 15 Replies View Related

SQL Server 2014 :: Column Store Query Reverting To Row Mode With CROSS JOIN

May 20, 2015

I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.

-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.

-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;

[Code] .....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved