(SQL 2000) Incorrect Results When Using An Outer Join And A View!
Mar 29, 2008
Hi,
I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.
However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.
Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.
Here's the query to reproduce this error.
Code Snippetcreate table t1 (pk1 int not null,primary key (pk1))
create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1))
go
insert into t1 values (1)
insert into t2 values (2, 'XXXXX')
go
create view V as
select pk1, 'ZZZZ' as label1 from t2
go
select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1
from t1 as A left outer join V as B on A.pk1 = B.pk1
go
This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".
I just want to confirm that this is an issue and no fix is available for this so far.
Regards,
Naresh Rohra.
View 2 Replies
ADVERTISEMENT
Oct 12, 2006
I am having no luck reporting this bug on the feedback link. I type in all information and click "Submit" and it just refreshes the page. So, here it is, I hope someone from MS will post this for me.
The problem appears to be a RIGHT OUTER JOIN on a VIEW causes the query to never return, or return very, very slowly. In 2000 SP4 the query returns in 2 seconds, in 2005 (2153) 64bit, it ran for 42 MINUTES before I killed it.
The is a duplication script to show a problem I have. This script uses the AdventureWorks database to demonstrate the problem. This is script is not the best, but shows the problem.
Please no comments on how to work around the problem. This query is generated by a user using and AdHoc reporting tool. I have NO control over how the user or the tool generates the SQL query.
The word "FAILURE" shows the original query which as far as I can tell never returns. The queries after, are different solutions, if I was able to change the user query, which I am not able to do.
-- Create Test Data Table CustomerListTom and Views
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO
CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)
INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,
ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
-- Create a big enough set of data for testing
DECLARE @i INT
SET @i = 1
WHILE (@i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,
ModifiedDate = cu.ModifiedDate + CASE WHEN @i > 3 THEN 10 ELSE -25 END + @i
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
SET @i = @i + 1
END
-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO
-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))
GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO
QUERY:
USE AdventureWorks
-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- Drop PK and run ORIGINAL query, returns in less than 4 seconds
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
View 5 Replies
View Related
Sep 21, 2006
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1
FROM employee LEFT OUTER JOIN
request AS request ON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))
AS dayspending
FROM employee AS employee_1 LEFT OUTER JOIN
request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,
employee_1.emp_lname
View 2 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
Jun 19, 2001
I have a partitioned view containing 4 tables (example follows at end)
The query plan generated on a select correctly accesses just one of the tables
The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area
Note that the behaviour is the same with SP1 on SQL2000
I would be very grateful for any advice
Thanks
Stefan Bennett
Example follows
--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO
INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO
--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO
--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'
-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;
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
May 9, 2005
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
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
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
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
Feb 9, 2007
I am running the following code in my stored procedure and I am not geting the results I need:
set @dbidstr = 'select @dbid = dbid from dbo.databaseoriginalsize'
execute sp_executesql @dbidstr, N'@dbid int output', @dbid output
print @dbid
'select dbid from dbo.databaseoriginalsize' returns values 1-26 but when I run it as a code shown above I only get one value which is 26, 26 times.
What am I doing wrong?
View 3 Replies
View Related
Jun 1, 2004
Oi! What follows is a hypothetical situation, but it is a totally analogous to a real problem Im having, but provides an easier model to understand.
Imagine that you have database-driven battleship game and its time to render the board. Also imagine that you have to render more than one board and that the ships are all the size of one point on the grid.
One sql result you need is a list of all the points on the grid, regardless of whether or not there is a ship on it. This will make rendering much easier for you, because you can simply look at the record index to determine if a ship is present. The data that is stored about the position of the ships consists of one record containing the grid index and ship name.
One possible way to retrieve this data is to build a table that you will not change which contains a record for each point on the grid. Is it possible to union or join on this table to retrieve a list of results that contain both unoccupied locations and occupied ones?
Here is what I've come up with, but it contains results that have a null location when there are no ship records:
SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
FULL OUTER JOIN Ships
ON Ships.GridID = Grids.GridID
WHERE
Grids.PlayerID = 1
UNION-- (**not** UNION ALL)
SELECT
Grids.GridID,
GridLocations.GridLocation,
(SELECT ShipName FROM Ships WHERE GridID = Grids.GridID AND GridLocation = GridLocations.GridLocation)
FROM
GridLocations, Grids
WHERE
Grids.PlayerID = 1
View 5 Replies
View Related
Jul 14, 2004
Hello there,
I have 2 tables:
Table: Leads
------------------
ID LDate ClientID
1 04/02/2004 101
2 04/03/2004 103
3 04/04/2004 104
....
Table: Tracking
------------------
ID TDate ClientID Shown Clicked
1 04/02/2004 101 3 2
2 04/03/2004 103 5 4
3 04/04/2004 101 3 9
....
I need a query to display results for any Client ID like this:
Date Leads Shown Clicked
=============================
04/02/2004 1 3 2
04/04/2004 0 3 9
.....
=============================
The following query doesn't work, it display 1 in leads column instead of 0:
select t.Tdate, count(l.id) as Leads, sum(t.shown) as Views
from tracking t left outer join Leads l on r.clientid = t.clientid
where l.clientid = 101
and l.Ldate >= 'April 2,2004'
and t.Tdate >= 'April 2,2004'
group by t.Tdate
Thanks a lot for your time and help in advance.
View 3 Replies
View Related
Aug 10, 2006
Here's the lookup table, tblLookup:
Task
SubTask
Subset
Superset
Description
And here's the more voluminous table, tblRecords, to which I need to join that:
Task
SubTask
Acct_cat
Actual_Amount
Budgeted_Amount
The problem is that the Task data in tblLookup consists only of the first 5 chars of the same kind of data in tblRecords (e.g., if a field on that record in tblRecords says "BILLYGOAT", that field in tblLookup is entered only as "BILLY").
How do I match them up?
Thanks.
View 2 Replies
View Related
Aug 31, 2006
Hello,
I am having problems with an outer join statement.
I have written a procedure that tests a table for missing and corrupt data and
to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did.
To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:
select o.*,'#',k.* from repaired o right outer join copy k on
(str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) )
where
o.D<>k.D or
o.E<>k.E or
o.F<>k.F or
...
I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy".
Before that it looked like:
... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...
In table "copy" is a record missing that is in table "repaired".
Why is my statement not printingout that missing record?
Shouldn't be an outer join exactly what I have to use for finding missing data?
I anybody can help me, I would be very happy.
Sven
View 2 Replies
View Related
Mar 15, 2007
Why do we need a Right outer join, when we get the same results by swapping the order in which tables are specified in a Left join?
View 9 Replies
View Related
Apr 21, 2004
Ok....I have 3 tables.
Entity
--------
name
entity_key
Address
----------
street
zip
mailing_flag
entity_key
Phone
--------
phone_number
phone_type_key
entity_key
I want to see all of the Entity records with their corresponding Address and Phone records. (select e.name, a.street, a.zip, p.phone_number)But only show the Address record for that Entity if the mailing_flag is 'Y' and I only want to see the Top 1 Phone record where the phone_type_key = 'Home'. If the above criteria isn't met I just want to see nulls for the Address and Phone records.
My problem is getting ALL the Entity records to return. It only wants to give me the Entity records that have Address or Phone associated with them. That and somehow showing the Top 1 phone record for the Entity are my issues.
Any help would be much appreciated......Thanks!
View 3 Replies
View Related
Apr 11, 2008
Hello,
I have the following script which is *sort of working* !!
The problem I have is that I need to add an outer join to one of the tables and I don't know where to add it or what the syntax is.
Basically, anyone who has an 'STRA' role in the contacts_roles table does not usually have an email address (shown as communications.notes). However, because I don't have any outer joins in place, the script is ignoring everyone who has an 'STRA' role and only pulling back those with an 'STRE' role.
Any help would be much appreciated as to how and where I put my outer join.
Thanks so much.
Jon
SELECT contacts.label_name, contact_positions.position, contact_roles.role, contact_roles.organisation_number, communications.notes, organisations.status, organisations.name, addresses.address, addresses.town, addresses.county, addresses.postcode
FROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_positions contact_positions, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts, bmf.dbo.organisations organisations
WHERE contact_roles.contact_number = contacts.contact_number AND communications.contact_number = contacts.contact_number AND organisations.organisation_number = contact_roles.organisation_number AND addresses.address_number = organisations.address_number AND contact_positions.contact_number = contacts.contact_number AND contact_positions.organisation_number = organisations.organisation_number AND ((contact_roles.role In ('STRE','STRA')) AND (organisations.status In ('BRAN','FULL','HOLD')))
ORDER BY organisations.name
View 10 Replies
View Related
Jun 20, 2008
Help!
I have a query that runs fine with 2 outer joins, but I am using "*=" syntax and this won't work for SQL 2005.
I am replacing with 'LEFT OUTER JOIN'... I can get it to work okay for first join, but not when I add the second
Any idea
Josephine
View 5 Replies
View Related
Jun 30, 2006
Hi i am having problem getting a resultset in a specific format which i wanted
i am suppose to get this:
team_id|Student|student_not_yet_submitted
Team 1|A,B,C|A
Team 2|D,E,F|NULL
Where (team_id, student) and student_not_yet_submitted are from different tables. Issue of concatenating aside (i am able to do this with java loop), I derived them like this:
1st select=select team_id, student_name from team, student where (....) to get the 1st 2 columns.
To get the 3rd column,
my second select is the same as 1st select but it has an additional condition based on results from 1st select stmt (using the team_id passed in)
2nd select=select team_id, student_name from team where (..... and student_name not in (a 3rd query stmt with result based on team_id from 1st select statement))
i am trying to use left outer join on student_name to join the 2 stmt together, but i am stuck because the 2nd select statement (or rather the 3rd inner query) requires input from the 1st. is there a more efficient way of doing this?
View 1 Replies
View Related
Mar 6, 2008
Below is my query. I am a relative novice to SQL. I'd like to rewrite this with joins. All should be inner joins except for the last one Aritem to shmast. That should be a left outer join because not all of our invoices (in the Aritem tables) have actually been shipped.
How would I do this? I have already read through 2 SQL books, but the examples they give are much simpler than what I need to do. Here's the Query:
SELECT DISTINCT Ardist.fcacctnum, Ardist.fcrefname, Ardist.fccashid,
Ardist.fcstatus, Armast.fcinvoice, Armast.fbcompany, Armast.fcustno,
Ardist.fddate, Ardist.fnamount * -1 as fnAmount,
glmast.fcdescr,
shmast.fcstate,
slcdpm.fcompany as CompanyName
FROM ardist, glmast, armast, slcdpm, shmast, aritem
WHERE Glmast.fcacctnum = Ardist.fcacctnum
AND Armast.fcinvoice = SUBSTRING(Ardist.fccashid,8,20)
AND Ardist.fnamount <> 0
AND ((Ardist.fcrefname = 'INV' OR Ardist.fcrefname = 'CRM' OR Ardist.fcrefname = 'VOID')
AND Glmast.fccode = 'R')
AND armast.fcustno = slcdpm.fcustno
AND armast.fcinvoice = aritem.fcinvoice
AND left(aritem.fshipkey,6) = shmast.fshipno
View 2 Replies
View Related
Mar 30, 2008
what is difference between inner join and outer join also right and left join can you explain with simple example(because i m fresher) and the query which are there in previous forum will work for the mainframe environment?
View 1 Replies
View Related
Jul 20, 2005
In my (admittedly brief) sojurn as an SQL programmer I've often admired"outer joins" in textbooks but never really understood their use. I'vefinally come across a problem that I think is served by an outer join.-- This table stores the answer to each test questionCREATE TABLE TestResults (studentIdvarchar (15)NOT NULL,testIdintNOT NULL REFERENCES Tests(testId),qIdintNOT NULL REFERENCES TestQuestions(qId),responseintNOT NULL REFERENCES TestDistractors(dId),CONSTRAINT PK_TestResultsPRIMARY KEY NONCLUSTERED (testId, studentId, qId),)-- This table defines which questions are on which testsCREATE TABLE TestQuestions_Tests (testIdintNOT NULL REFERENCESTests(testId),qIdintNOT NULL REFERENCESTestQuestions(qId),)(Table Tests contains housekeeping information about a particular test,TestQuestions defines individual questions, TestDistractors lists thepossible responses.)In schematic form, the simplest form of my problem is to find all thequestions that haven't been answered. That would be:SELECT tqt.qIdFROM TestResults AS trRIGHT OUTER JOIN TestQuestions_Tests AS tqtON tr.testId = tqt.testId AND tr.qId = tqt.qIdWHERE tr.qId is NULLSo far I think this is pretty straightforward and an efficient solution.Agreed?But my real problem is a little bit more complex. What I really want toknow is "for a given student, on a given test, which questions haven'tbeen answered?"So now I have:SELECT tqt.qIdFROM TestResults AS trRIGHT OUTER JOIN TestQuestions_Tests AS tqtON tr.testId = tqt.testId AND tr.qId = tqt.qIdWHERE tqt.testId = '1' AND tr.studentId = '7' AND tr.qId IS NULLIs this the canonical form of the solution to my problem? It seems tome like it is generating a whole slew of rows and then filtering them.Is there a more elegant or efficient way to do it?-- Rick
View 6 Replies
View Related
Feb 24, 2007
Hi
Wonder if any could help be putting together a SQl select statement. I have 2 tables of road-data, one having default data for the area, and one with actual data. They look like this:
Create table AreaDefaults {
AreaCode Int(3),
RoadCode Int(3)
}
Insert into table AreaDefaults values (34, 21);
Insert into table AreaDefaults values (35, 21);
Insert into table AreaDefaults values (36, 21);
Insert into table AreaDefaults values (37, 21);
Create table AreaRoadCode {
AreaCode Int(3),
RoadCode Int(3),
ZipCode Varchar(20)
}
Insert into table AreaRoadCode values (34, 12, '2800 L');
Insert into table AreaRoadCode values (34, 13, '2900 K');
Insert into table AreaRoadCode values (36, 18, '0900 O');
I would like to make an SQL select statement, where I join the two tables, producing the following result:
AreaCode RoadCode ZipCode
==============================
34 12 2800 L
34 13 2900 K
35 21
36 18 0900 O
37 21
Kan anybody tell me, how I do that?
/Michael
View 3 Replies
View Related
May 7, 2007
I have a table Financial_Values that has the following columns:
Year(pk),
Month (pk),
Account_No (pk),
Amount
The combination year, month & account no varies for each year & month.
I need to create sp or function that creates a result set that has the following columns:
Account_No (pk),
Current Amount,
Prior_Year_Amount
Current YTD_Amount,
Prior_Year_YTD
Because the rows in the Financial_Values (number and values of the Account No) can be
different for the current and prior years, I believe I have to do the following steps
1. Create table #Current_Amount
Year(pk),
Month (pk),
Account_No (pk),
Current_Amount
Insert #Current_Amount
Select Year, Month, Account_No, Amount as Current_Amount
From Financial_Values
Where Financial_Value.Year = @Current_Year
And Financial_Value.Month = @Current_Month
2. Create table #Current_YTD_Amount
Year(pk),
Month (pk),
Account_No (pk),
Current_YTD_Amount
Insert #Current_Amount
Select Year, Month, Account_No, Amount as Current_Amount
From Financial_Values
Where Financial_Value.Year = @Current_Year
And (Financial_Value.Month >= 1 and <= @Current_Month)
3. Create table #Current_Values
Year(pk),
Month (pk),
Account_No (pk),
Current_Amount,
Current_YTD_Amount
Insert #Current_Values
Select #Current_Amount.Year,
#Current_Amount.Month,
#Current_Amount.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
From #Current_Amount INNER JOIN #Current_YTD_Amount
On #Current_Amount.Year = #Current_YTD_Amount.Year
And #Current_Amount.Month = #Current_YTD_Amount.Month
And #Current_Amount.Account_No = #Current_YTD_Amount.Account_No
4. Create table #Prior_Year_Amount
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_Amount
Insert #Prior_Year_Amount
Select Year, Month, Account_No, Amount as Prior_Year_Amount
From Financial_Values
Where Financial_Value.Year = @Current_Year
And Financial_Value.Month = @Current_Month
5. Create table #Prior_Year_YTD_Amount
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_YTD_Amount
Insert #Prior_Year_YTD_Amount
Select Year, Month, Account_No, Amount as Prior_Year_YTD_Amount
From Financial_Values
Where Financial_Value.Year = @Current_Year
And (Financial_Value.Month >= 1 and <= @Current_Month)
6. Create table #Prior_Year_Values
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_Amount,
Prior_Year_YTD_Amount
Insert #Prior_Year_Values
Select #Prior_Year_Amount.Year,
#Prior_Year_Amount.Month,
#Prior_Year_Amount.Account_No,
#Prior_Year.Current_Amount,
#Prior_Year_YTD_Amount.Current_YTD_Amount
From #Prior_Year_Amount INNER JOIN #Prior_Year_YTD_Amount
On #Prior_Year_Amount.Year = #Prior_Year_YTD_Amount.Year
And #Prior_Year_Amount.Month = #Prior_Year_YTD_Amount.Month
And #Prior_Year_Amount.Account_No = #Prior_Year_YTD_Amount.Account_No
7. Create table #Current_and_Prior_Year_Values
Account_No (pk),
Current_Amount,
Current_YTD_Amount,
Prior_Year_Amount,
Prior_Year_YTD_Amount
Select @Current_Values_Count = Count(Account_No)
From dbo.tblPFW_Current_Values
Select @Prior_Year_Values_Count = Count(Account_No)
From dbo.tblPFW_Prior_Year_Values
If @Current_Values_Count > @Prior_Year_Values_Count
Insert #Current_and_Prior_Year_Values
Select #Current_Values.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
#Prior_Year_Values.Prior_Year_Amount,
#Prior_Year_YTD_Amount.Prior_Year_YTD_Amount
From #Current_Values RIGHT OUTER JOIN #Prior_Year_Values
On #Current_Values.Year = #Prior_Year_Values.Year
And #Current_Values.Month = #Prior_Year_Values.Month
And #Current_Values.Account_No = #Prior_Year_Values.Account_No
Else
Insert #Current_and_Prior_Year_Values
Select #Prior_Year_Values.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
#Prior_Year_Values.Prior_Year_Amount,
#Prior_Year_YTD_Amount.Prior_Year_YTD_Amount
From #Prior_Year_Values RIGHT OUTER JOIN #Current_Values
On #Prior_Year_Values.Year = #Current_Values.Year
And #Prior_Year_Values.Month = #Current_Values.Month
And #Prior_Year_Values.Account_No = #Current_Values.Account_No
Steps 1 thru 6 are working fine, however when I get to Step 7, my stored procedure fails with
trying to insert into #Current_and_Prior_Year_Values a null value the primary key Account_No.
If I create all the tables not as temporary tables it still fails the same way, however
if I don't run step seven and then run views like the select statements in Step 7
I get the correct results from the views.
Also if a perform an inner join in step seven vs an right outer join, the step does not fail with
the null insert, however I don't the right number of rows (account no)
I quess my question is why would the right outer joins in step 7, run as part of a sp, return
any null Account No values?
Or could anyone suggest a different way to get the result set I need?
View 1 Replies
View Related
Nov 19, 2007
I am trying to grab All Sales Reps (no matter if they have any valid records, i just want to show all names) and Display all question descriptions (even if the question wasnt answered). So it should look like this:
Travel Holiday Sick Ride With Office Day Vacation Seminar
Sally jones 5 1
Kim Smith 2
Tawny Rodes
Jim Tyler
Steven Jones
Calvin Moore
Tina Hood
Cristine Smart 3 20
Mark foley
Fred Rogers 2
Instead im getting this:
Travel Holiday Ride With Vacation
Sally jones 5 1
Kim Smith 2
Tawny Rodes
Cristine Smart 3 20
Fred Rogers 2
Here's my query:
Code Block
SELECT
Qry_Sales_Group.Name,
Qry_Sales_Group.SR_NAME,
Qry_Sales_Group.Salesperson_Purchaser_code as SR_Code,
CONVERT(datetime, DATEADD(day, q.cycle_day - 1, q.start_date), 6) AS Logged_Time,
y.question_code,
y.description,
q.response
FROM Qry_Sales_Group
LEFT OUTER JOIN(SELECT CONVERT(datetime, DATEADD(day, dbo.question_history.cycle_day - 1, dbo.period.start_date), 6) AS Logged_Time,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name,
questions.question_code,
questions.description,
question_history.response,
entity_code,
cycle_day,
start_date
FROM dbo.questions
INNER JOIN question_history
ON questions.question_code = question_history.question_code
INNER JOIN period
ON question_history.period_code = period.period_code
RIGHT OUTER JOIN Qry_Sales_Group
ON SUBSTRING(dbo.question_history.entity_code, 1, 5) = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
WHERE CONVERT(datetime, DATEADD(day, dbo.question_history.cycle_day - 1, dbo.period.start_date), 6) = '11/14/2007' AND
(dbo.questions.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01', 'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08',
'ACR07')) ) q
ON SUBSTRING(q.entity_code, 1, 5) = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
RIGHT OUTER JOIN(Select
description,
question_code
from Questions
Where questions.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01', 'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08',
'ACR07') )y
ON y.question_code=q.question_code
order by QRY_SALES_GROUP.SR_NAME
Please help. If i do a Right outer join, i get all the questions i want. But if i do a left outer join i get all the sales reps. How do i get both??
View 6 Replies
View Related
Feb 4, 2004
My SQL Statement as follows:
SELECT S.SessionID,S.SessionName,T.number
FROM Sessions S LEFT OUTER JOIN (SELECT SessionID,COUNT(*) AS number
FROM EventLog
WHERE MachineID = @machineID
GROUP BY SessionID) AS T
ON S.SessionID = T.SessionID
ORDER BY S.SessionID
The result sets T.number as NULL if there is no record related to SessionName. How to change NULL to ZERO?
Thanks a lot.
View 2 Replies
View Related
Jul 5, 2004
I have a MSDE query that includes a "left outer join..." clause. It runs fine in MSDE Query (a 3rd party GUI tool) and produces 12 rows. column 3 has some NULL values (because of the outer join).
But when I use the same query in an ASP.NET page, and display the result in a datagrid, it only displays 7 rows - the rows with the NULL value in column 3 do not display.
Is there a parameter somewhere in datagrid or dataset that I should be setting?
thank you someone!
View 2 Replies
View Related
