September 9, 2009 at 10:30 am
I have two tables one which may or may not have data related to the primary table contest. When I do an old school non ansi tsql I can get the results I am looking for. Now I convert it to ansi style and doesn't return the same results.
Setup:
TABLE: contest
Fields: contest_id, contest_attempts_allowed
Row 1: 2, 5
Row 2: 3, 5
Row 3: 4, 3
TABLE: contest_activity
Fields: contest_id, p_id
No records in table
--NON ANSI SQL THAT WORKS
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c, contest_activity ca
WHERE c.contest_id *= ca.contest_id
AND ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
--RESULT: 5, 0
--ANSI SQL THAT DOESN'T PRODUCT THE RESULTS I WANT
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c RIGHT JOIN contest_activity ca
ON c.contest_id = ca.contest_id
WHERE ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
--NO RESULTS RETURNED
Any advice on how to get to match?
September 9, 2009 at 10:47 am
lleemon13 (9/9/2009)
I have two tables one which may or may not have data related to the primary table contest. When I do an old school non ansi tsql I can get the results I am looking for. Now I convert it to ansi style and doesn't return the same results.Setup:
TABLE: contest
Fields: contest_id, contest_attempts_allowed
Row 1: 2, 5
Row 2: 3, 5
Row 3: 4, 3
TABLE: contest_activity
Fields: contest_id, p_id
No records in table
--NON ANSI SQL THAT WORKS
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c, contest_activity ca
WHERE c.contest_id *= ca.contest_id
AND ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
--RESULT: 5, 0
--ANSI SQL THAT DOESN'T PRODUCT THE RESULTS I WANT
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c RIGHT JOIN contest_activity ca
ON c.contest_id = ca.contest_id
WHERE ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
--NO RESULTS RETURNED
Any advice on how to get to match?
Change your RIGHT OUTER JOIN to a LEFT OUTER JOIN.
September 9, 2009 at 10:52 am
Lynn, thanks for the quick reply. However the following didn't return any results:
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c LEFT OUTER JOIN contest_activity ca
ON c.contest_id = ca.contest_id
WHERE ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
It appears it only shows up once one record is in both tables.
September 9, 2009 at 2:56 pm
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c LEFT OUTER JOIN contest_activity ca
ON c.contest_id = ca.contest_id AND ca.p_id = 1
WHERE c.contest_id = 2
GROUP BY contest_attempts_allowed
_____________
Code for TallyGenerator
September 9, 2009 at 3:01 pm
Lynn Pettis (9/9/2009)
lleemon13 (9/9/2009)
I have two tables one which may or may not have data related to the primary table contest. When I do an old school non ansi tsql I can get the results I am looking for. Now I convert it to ansi style and doesn't return the same results.Setup:
TABLE: contest
Fields: contest_id, contest_attempts_allowed
Row 1: 2, 5
Row 2: 3, 5
Row 3: 4, 3
TABLE: contest_activity
Fields: contest_id, p_id
No records in table
--NON ANSI SQL THAT WORKS
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c, contest_activity ca
WHERE c.contest_id *= ca.contest_id
AND ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
--RESULT: 5, 0
--ANSI SQL THAT DOESN'T PRODUCT THE RESULTS I WANT
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c RIGHT JOIN contest_activity ca
ON c.contest_id = ca.contest_id
WHERE ca.p_id = 1
AND c.contest_id = 2
GROUP BY contest_attempts_allowed
--NO RESULTS RETURNED
Any advice on how to get to match?
Change your RIGHT OUTER JOIN to a LEFT OUTER JOIN.
Move your where criteria in to the ON clause as well.
September 9, 2009 at 3:37 pm
That was it thanks.
SELECT c.contest_attempts_allowed,
count(ca.ca_status_cd)
FROM contest c LEFT OUTER JOIN contest_activity ca
ON c.contest_id = ca.contest_id
AND ca.p_id = 1 AND c.contest_id = 2
GROUP BY contest_attempts_allowed
September 9, 2009 at 4:36 pm
No, you should leave criteria on "FROM" table in WHERE:
WHERE c.contest_id = 2
Only criteria on LEFT JOIN table go into ON.
_____________
Code for TallyGenerator
September 9, 2009 at 7:22 pm
Sergiy (9/9/2009)
No, you should leave criteria on "FROM" table in WHERE:WHERE c.contest_id = 2
Only criteria on LEFT JOIN table go into ON.
I think it is a wash either way on the main table. Either way it will filter the c.contest_id = 2 records.
Leaving it in the WHERE clause does make it clearer that that particular piece is a filter not a join criteria.
September 9, 2009 at 7:53 pm
Lynn Pettis (9/9/2009)
Sergiy (9/9/2009)
No, you should leave criteria on "FROM" table in WHERE:WHERE c.contest_id = 2
Only criteria on LEFT JOIN table go into ON.
I think it is a wash either way on the main table. Either way it will filter the c.contest_id = 2 records.
Leaving it in the WHERE clause does make it clearer that that particular piece is a filter not a join criteria.
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'test_maintable' AND type = 'U')
DROP TABLE test_maintable
GO
CREATE TABLE test_maintable (
ID int NULL,
Name nvarchar(50) NOT NULL
)
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'test_RefTable' AND type = 'U')
DROP TABLE test_RefTable
GO
CREATE TABLE test_RefTable (
MainID int NULL,
Name nvarchar(50) NOT NULL
)
GO
INSERT INTO test_maintable
(ID, Name)
SELECT 1, 'Record 1'
UNION
SELECT 2, 'Record 2'
INSERT INTO test_RefTable
(MainID, Name)
SELECT 1, 'Ref Record 1'
SELECT M.ID, M.Name, R.MainID, R.Name
FROM test_maintable M
LEFT JOIN test_RefTable R ON R.MainID = M.ID AND R.Name = 'Ref Record 1'
WHERE M.ID = 2
SELECT M.ID, M.Name, R.MainID, R.Name
FROM test_maintable M
LEFT JOIN test_RefTable R ON R.MainID = M.ID AND R.Name = 'Ref Record 1'
AND M.ID = 2
Feel the difference.
😉
_____________
Code for TallyGenerator
September 9, 2009 at 8:59 pm
I'm going to go with "it depends." Which result set is the correct one? Only the user knows for sure:
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'test_maintable' AND type = 'U')
DROP TABLE dbo.test_maintable
GO
CREATE TABLE dbo.test_maintable (
ID int NULL,
Name nvarchar(50) NOT NULL
)
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'test_RefTable' AND type = 'U')
DROP TABLE dbo.test_RefTable
GO
CREATE TABLE dbo.test_RefTable (
MainID int NULL,
Name nvarchar(50) NOT NULL
)
GO
INSERT INTO dbo.test_maintable
(ID, Name)
SELECT 1, 'Record 1' UNION
SELECT 2, 'Record 2';
INSERT INTO dbo.test_RefTable
(MainID, Name)
SELECT 1, 'Ref Record 1';
SELECT
M.ID,
M.Name,
R.MainID,
R.Name
FROM
dbo.test_maintable M
LEFT JOIN dbo.test_RefTable R
ON R.MainID = M.ID AND R.Name = 'Ref Record 2'
WHERE
M.ID = 2
SELECT
M.ID,
M.Name,
R.MainID,
R.Name
FROM
dbo.test_maintable M
LEFT JOIN dbo.test_RefTable R
ON (R.MainID = M.ID
AND R.Name = 'Ref Record 2'
AND M.ID = 2)
INSERT INTO dbo.test_RefTable
(MainID, Name)
SELECT 2, 'Ref Record 2';
SELECT
M.ID,
M.Name,
R.MainID,
R.Name
FROM
dbo.test_maintable M
LEFT JOIN dbo.test_RefTable R
ON R.MainID = M.ID AND R.Name = 'Ref Record 2'
WHERE
M.ID = 2
SELECT
M.ID,
M.Name,
R.MainID,
R.Name
FROM
dbo.test_maintable M
LEFT JOIN dbo.test_RefTable R
ON (R.MainID = M.ID
AND R.Name = 'Ref Record 2'
AND M.ID = 2)
DROP TABLE dbo.test_maintable
DROP TABLE dbo.test_RefTable
Do they want only the records for M.ID = 2 regardless of a match in the reference table or do they want all records from the master table plus only the ones that match in the reference table where M.ID = 2?
That is the question.
September 9, 2009 at 9:04 pm
Yes, how you build any query depends on desired outcome.
But that obviously was not true:
Either way it will filter the c.contest_id = 2 records.
_____________
Code for TallyGenerator
September 9, 2009 at 9:30 pm
All I'm saying is that how you build the query is based on what the question asked. The OP indicated (right or wrong) that what he posted as was what he were looking for from the query.
After creating a sql server 2000 test environment, yes the c.contest_id = 2 needs to be in the WHERE clause based on the data returned from the original query.
I'll be honest, I have never written a query using the old style joins. I have always written them in the FROM clause. Just seemed right, even when using SQL Server 6.5 and SQL Server 7.0.
Trying to convert complex queries to ANSI-92 can be a pain in the arse.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply