T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?
Mar 30, 2015
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table
[Code]....
View 2 Replies
ADVERTISEMENT
Dec 12, 2007
Hi,
In my database there are following two tables with sample data:
Customer_Product
=================
Branch Customer ProductID
B1 C1 P1
B1 C1 P2
B1 C2 P1
B2 C3 P2
B2 C3 P3
Product
=================
ID Name
P1 Prod 1
P2 Prod 2
P3 Prod 3
I need to write a query which can display the sum of each product for each branch. The required output is as follow:
Required Output
================
Branch Product Total
B1 P1 2
B1 P2 1
B1 P3 0
B2 P1 0
B2 P2 1
B2 P3 1
The Query I tried is:
SELECT Branch, ProductID, COUNT(*) as Total
FROM Customer_Product cp
RIGHT OUTER JOIN Product pd ON cp.ProductID=pd.id
GROUP BY Branch, ProductID
ORDER BY Branch, ProductID
But Right outer join act on Table level instead of Group. How can i generate desired output by keeping in mind that both Branch and Product are dynamic?
Thanks.
View 3 Replies
View Related
Apr 2, 2007
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
View 3 Replies
View Related
Oct 10, 2014
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
View 4 Replies
View Related
Aug 17, 2015
I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.
SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName,
dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code,
RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email,
[Code]...
The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.
View 9 Replies
View Related
Oct 18, 2013
I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.
My query
select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town
How to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB
I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...
View 2 Replies
View Related
Jul 29, 2007
Hi, I would appreciate help on the following query I got stuck with;
I've got 2 tables "AccountList" and "PeriodBalance";
I need to return all the accounts form "AccountList" and their balance form "PeriodBalance";
The user will select a period for which the balances should be returned, if there is no balance for the period... 0 should be returned. "PeriodBalance" would not have an entry if there is no balance for the period.
There is only 3 possible periods (1, 2 or 3)
And there is only one balance per period per account;
TABEL 1: AccountList:
AccNo
100
200
300
TABLE 2: PeriodBalance:
AccNo PerID PerBal
100 1 1000
100 2 1750
100 3 1800
300 1 3200
300 3 3500
This is what is what I need returned by the query, assuming we are selecting PerID 2: (WHERE PerID=2)
AccNo PerID PerBal
100 2 1750
200 2 0
300 2 0
I've included ISNULL(PerID,4) = 4 in my WHERE clause...this worked for returning AccNo 200.
My problem is AccNo 300 in not NULL....it has values, just not for the selected PerID...so how do I get AccNo 300 to be included in my Query result?
This is my SQL Query:
SELECT AccountList.AccNo, PeriodBalance.PerID, PeriodBalance.PerBal
FROM AccountList LEFT OUTER JOIN
PeriodBalance ON AccountList.AccNo = PeriodBalance.AccNo
WHERE PeriodBalance.PerID = 2 OR ISNULL(PerID,4) = 4
Thanks in advance;
View 4 Replies
View Related
Nov 19, 2006
I've met a curiouse thing when using full outer join, that I got some rows belong to neither tables @_@
The SQL statement is like this:
SELECT PreviousMonth.InvoiceID, PreviousMonth.CategoryName, PreviousMonth.ProdName, PreviousMonth.Qty, ISNULL(CurrentMonth.SellTotal, 0)
AS SellTotal, ISNULL(PreviousMonth.SellTotal, 0) AS PriorSellTotal
FROM (SELECT SellTotal, InvoiceID, CategoryName, ProdName, Qty
FROM viewProdSales AS viewProdSales_1
WHERE (DateCreated >= @pStartDate) AND (DateCreated <= @pEndDate)) AS CurrentMonth
FULL OUTER JOIN
(SELECT SellTotal, InvoiceID, CategoryName, ProdName, Qty
FROM viewProdSales
WHERE (DateCreated >= @pPriorStartDate) AND (DateCreated < @pStartDate)) AS PreviousMonth
ON
CurrentMonth.InvoiceID = PreviousMonth.InvoiceID
The result include some added rows, if I query the 2 sub table seperately, neither contains such rows.
For example, if I pass 01/09/2006, 01/10/2006 and 31/10/2006 as pPriorStartDate, pStartDate and pEndDate, to query the sales statistics in Sept and Oct, I would get some reords in June or even earlier... Anyone know about this? Thanks in advance.
Summer
View 3 Replies
View Related
Jan 20, 2005
This is what I want my results to look like
invoice wbs1 wbs2 amount
8060 000-333 500 0
8060 000-333 100 0
8060 000-333 140 0
8060 000-333 150 4335
8060 000-333 160 0
8267 000-333 500 0
8267 000-333 100 20500
8267 000-333 140 547.50
8267 000-333 150 2000
8267 000-333 160 5000
This is what I have so far:
SELECT PR.WBS1, PR.WBS2, ledgerar.invoice, SUM(CASE WHEN ledgerar.transtype = 'in' AND ledgerar.period = '200405' THEN ledgerar.amount * - 1 END) AS amount
FROM PR LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND LedgerAR.WBS3 = PR.WBS3
WHERE (LedgerAR.wbs1 = '001-333') AND ledgerar.period = '200405'
GROUP BY pr.wbs1, pr.wbs2, ledgerar.invoice
the above query gives me the following results:
invoice wbs1 wbs2 amount
8060 000-333 100 0
8060 000-333 140 0
8060 000-333 160 4335
8267 000-333 100 1320
8267 000-333 140 20912.5
8267 000-333 150 8363
8267 000-333 160 2650
But I don't know how to get the query to display all of wbs2 whether it is null or not. So, for each invoice number there should be five records according to the wbs2 codes (500, 100, 140, 150, 160)
Does anyone know how to do this?
Thanks,
Laura
View 1 Replies
View Related
Dec 31, 2007
Need some join help...
Table 1 - Modules:
ID | Name
1 | A
2 | B
3 | C
Table 2 - CompanyModules
ModuleID | CompanyID
1 | 1
2 | 1
3 | 1
1 | 2
I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False
What would be the query for this? Thanks.
Edit: This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1
View 1 Replies
View Related
Jul 7, 2014
I am having below schema.
CREATE TABLE #Turnover (
location varchar(50),
Total int
)
insert into #Turnover (location,Total) values('A', 500)
insert into #Turnover (location,Total) values('AB', 200)
insert into #Turnover (location,Total) values('ABC', 100)
insert into #Turnover (location,Total) values('BA', 100)
insert into #Turnover (location,Total) values('BAC', 500)
insert into #Turnover (location,Total) values('BAM', 100)
Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.
View 5 Replies
View Related
Apr 26, 2002
Take the following scenario:
We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.
Thanks in advance for any help.
-David Edelman
Test script below, followed by results
===========================================
create table parent (p_id int NOT NULL)
go
create table child (p_id int NOT NULL, c_type varchar(6) NULL)
go
insert parent values (1)
insert parent values (2)
insert parent values (3)
insert parent values (4)
insert parent values (5)
insert parent values (6)
insert parent values (7)
insert parent values (8)
insert parent values (9)
insert parent values (10)
go
insert child values (1, 'AAA')
insert child values (1, 'BBB')
insert child values (1, 'CCC')
insert child values (2, 'AAA')
insert child values (4, 'AAA')
insert child values (4, 'DEF')
insert child values (4, 'AAA')
insert child values (4, 'BBB')
insert child values (5, 'AAA')
insert child values (5, 'AAA')
insert child values (6, 'AAA')
insert child values (7, 'AAA')
insert child values (7, 'BBB')
insert child values (7, 'CCC')
insert child values (7, 'DDD')
insert child values (10, 'AAA')
insert child values (10, 'CCC')
go
select p.p_id, count(c.p_id) as num_rows
from parent p, child c
where p.p_id *= c.p_id
and c.c_type in ('AAA', 'BBB')
group by p.p_id
select p.p_id, count(c.p_id) as num_rows
from parent p left outer join child c on p.p_id = c.p_id
where c.c_type in ('AAA', 'BBB')
group by p.p_id
=========================================
Results:
(T-SQL *= outer join)
p_id num_rows
----------- -----------
1 2
2 1
3 0
4 3
5 2
6 1
7 2
8 0
9 0
10 1
(SQL-92 outer join)
Warning: Null value eliminated from aggregate.
p_id num_rows
----------- -----------
1 2
2 1
4 3
5 2
6 1
7 2
10 1
View 1 Replies
View Related
May 10, 2010
what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?
View 3 Replies
View Related
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
Nov 4, 2003
Hello
I've a table with these values:
Cod_Lingua - Des_Lingua
------------------------------
ITA Italian
GER German
ENG English
FRA French
and another table with product/description
ProductID - Cod_Lingua - Description
-------------------------------------------
1 ITA Mia Descrizione
1 ENG My Description
I've this SELECT:
SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description
FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua
WHERE Descrizioni_Lingua.ProductID=1
I get these results:
ITA - Mia Descrizione
ENG - My Description
I don't want this. I'd like to have this:
ITA - Mia Descrizione
ENG - My Description
GER - (null)
FRA - (null)
How can I get the second result set?
Thanks for your support.
View 3 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Aug 10, 2007
Scenario:
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?
View 1 Replies
View Related
Jun 5, 2006
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?
View 3 Replies
View Related
Dec 21, 2006
Hi ,
i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,
However it is same for smaller number of rows.
Please help ...i want the order to be same.
View 4 Replies
View Related
Aug 22, 2014
This is my code, table and result,
declare @t_JobNoticeID table (cvid int, JobNoticeID int)
insert into @t_JobNoticeID values(2456, 24);
insert into @t_JobNoticeID values(4000, 124);
insert into @t_JobNoticeID values(245, 9);
insert into @t_JobNoticeID values(2456, 19);
insert into @t_JobNoticeID values(4000, 904);
insert into @t_JobNoticeID values(4000, 11);
insert into @t_JobNoticeID values(24, 19);
[Code] ....
My question is -
How update statement looks like compare RESULT A1 with x_JobMatching?
So, my FINAL RESULT shown as follow,
CVIDJobNoticeIDisMatch
1925450
6590690
459130
2456191
4569110
40009041
24560
900240
24191
View 5 Replies
View Related
Apr 28, 2015
I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000
Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900
create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)
[Code] ....
View 5 Replies
View Related
May 20, 2014
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
View 4 Replies
View Related
Apr 9, 2015
I'm working on a join between two tables where I only want one row returned... I'm matching on two columns between two tables. One of those columns in the target table could be null. I only want one record returned.
create table #vehicle(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1))
create table #class(id int, classtype varchar(1), value int)
insert into #vehicle values(1, 'AUDI', 'R8', 'A')
insert into #vehicle values(2, 'AUDI', null, 'B')
insert into #class values(1, 'A', 100)
insert into #class values(2, 'B', 1)
[Code] ....
Using the above example, if VehicleModel is anything other than 'R8' is specified then I want it to return the other class type record.
This is going to be used as a join within a bigger statement, so I'm not sure ordering and returning top 1 is going to work.
View 9 Replies
View Related
Jul 16, 2015
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact];
CREATE TABLE [dbo].[SourceContact]
(
[SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED,
[ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''),
[ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
[code]....
View 2 Replies
View Related
Mar 1, 2008
hello, i am running mysql server 5 and i have sql syntax like this:
select
sales.customerid as cid,
name,
count(saleid)
from
sales
inner join
customers
on
customers.customerid=sales.customerid
group by
sales.customerid
order by
sales.customerid;
it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales.
what can be the problem?
thanks,
View 10 Replies
View Related
Oct 10, 2007
Given a table of building components e.g. floors, walls, etc, etc:
create table component_multiplier_table
(
system_code char(4),
system_component_code char(3),
function_code char(4),
component_multiplier dec(7,6)
)
Where function_code is the function of the area e.g. Auditorium, Classrom, etc, etc. And not all components are available for all functions e.g. Carpeting is available for Classrooms but not Power Plants or Warehouses.
I need to self join the above table to itself on system_code and system_component_code and find out which rows are missing from each side.
A query that I've been banging away at with no success is:
SELECT c1.*, c2.*
FROM [dbo].[component_multiplier_table] c1 FULL OUTER JOIN [dbo].[component_multiplier_table] c2
ON (c1.system_component_code = c2.system_component_code) AND (c1.[system_code] = c2.[system_code])
WHERE c1.function_code = '2120' AND c2.[function_code] = '2750' AND (c1.[system_code] IS NULL OR c2.system_code IS NULL);
I added the is null conditions, no joy. I've tried every flavor of outer join w/o success.
Could any T-SQL gurus out there help me figure out how to do this in a set before I start coding
DECLARE crsr CURSOR
Thanks.
View 7 Replies
View Related
Mar 10, 2014
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
View 2 Replies
View Related
Apr 16, 2008
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
View 5 Replies
View Related
Sep 3, 2014
I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
View 5 Replies
View Related
Mar 31, 2007
And by correctly, I mean the way it *should* match, of course.I've got 2 data sources, using a left outer join, matching 2 columns. Whatever is the right side of my table is never matching and returning data. Here's my basic setup:OLE DB Source 1 (table1)
Sel Name Order JoinKeyno UniqueID 1 yesyes Column1 0 no
...
OLE DB Source 2 (table2)
Sel Name Order JoinKeyyes ID 1 yesyes Columns 0 no
...
There is a link (arrow) between UniqueID and ID, and the join type is "Left Outer Join". When I execute the statement "SELECT * FROM Table1 LEFT JOIN TABLE2 ON TABLE1.UniqueID = Table2.ID", the data returns correctly to me. What am I missing with the properties I've set above with the merge join?
View 6 Replies
View Related
Feb 28, 2008
I have been assigned to write a report that queries 3 tables. Table Store, Store_ID unique index, Cust table Cust_ID non unique index, CUST_Store_ID relates to Store Table, Table Emp, Emp_ID non unique index, Emp_Store_ID relates to Store Table
The problem is that the Employes and the customer don't relate to each other in any way. There can be stores with no Employees and there can be stores with no Customers. There are employees and customers listed multiple times in thier respective tables, and the multiple listings need to show on the report. A result set may look like this.
Store | Emp | Cust
1|1|
2|2|1
2|2|2
2| |3
2| |4
3| |5
3| |5
4|3|6
4|3|7
4|3|7
4|3|8
4|3|
4|3|
I tried a full join, but ended up with this:
Store ID ~~~~~Emp~~~~~Cust
OHCOL-811C ~~~~~10498500|OHCOL-811C|00151.00|448097821|SL0005 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146921092
OHCOL-811C ~~~~~10498500|OHCOL-811C|00152.00|448097821|SL0006 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146921092
OHCOL-811C ~~~~~10498500|OHCOL-811C|00153.00|448097821|SL0007 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146921092
OHCOL-811C ~~~~~10498500|OHCOL-811C|00154.00|448097821|SL9827 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146921092
OHCOL-811C ~~~~~10498500|OHCOL-811C|00155.00|448097821|SL0150 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146921092
OHCOL-811C ~~~~~10498500|OHCOL-811C|00151.00|448097821|SL0005 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146876651
OHCOL-811C ~~~~~10498500|OHCOL-811C|00152.00|448097821|SL0006 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146876651
OHCOL-811C ~~~~~10498500|OHCOL-811C|00153.00|448097821|SL0007 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146876651
OHCOL-811C ~~~~~10498500|OHCOL-811C|00154.00|448097821|SL9827 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146876651
OHCOL-811C ~~~~~10498500|OHCOL-811C|00155.00|448097821|SL0150 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146876651
As you can see Cust is being duplicated for each Emp. The actual result set should look like this.
Store ID ~~~~~Emp~~~~~Cust
OHCOL-811C ~~~~~10498500|OHCOL-811C|00151.00|448097821|SL0005 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146921092
OHCOL-811C ~~~~~10498500|OHCOL-811C|00152.00|448097821|SL0006 ~~~~~448097821| | | |N|AD-TSUACE |SPKG |146876651
OHCOL-811C ~~~~~10498500|OHCOL-811C|00153.00|448097821|SL0007 ~~~~~NULL
OHCOL-811C ~~~~~10498500|OHCOL-811C|00154.00|448097821|SL9827 ~~~~~NULL
OHCOL-811C ~~~~~10498500|OHCOL-811C|00155.00|448097821|SL0150 ~~~~~NULL
View 5 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related
Dec 23, 2014
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
View 9 Replies
View Related
