June 19, 2015 at 9:53 am
Hi.
I have a query that works fine in T-SQL but I need to implement it on a system using an old ODBC driver that doesn't support subqueries. Can anyone assist? The background is I have a table of artefacts (databases, instances, servers, etc.) and a table of relationships between them. I'm writing a query to find databases without a relationship to an instance using:
declare @artefacts table (id int, name char(10), type char(10))
declare @relationships table (parent int, child int)
insert into @artefacts values
(1, 'myInstance', 'Instance'),
(2, 'mySite', 'Site'),
(3, 'myDatabase', 'Database')
insert into @relationships values
(1, 3),-- comment out this line to get a database without a parent instance
(2, 1),
(2, 3)
SELECT
artefacts.name AS DB
FROM
@artefacts artefacts
LEFT OUTER JOIN
(SELECT * FROM @relationships relationships INNER JOIN @artefacts artefacts ON relationships.parent = artefacts.id WHERE artefacts.type = 'Instance') relationships
ON relationships.child = artefacts.id
LEFT OUTER JOIN
@artefacts AS a2
ON a2.id = relationships.parent
WHERE
artefacts.type = 'Database'
AND
a2.name IS NULL
I had to use the subquery because my original code:
LEFT OUTER JOIN
@relationships relationships
ON relationships.child = artefacts.id
LEFT OUTER JOIN
@artefacts a2
ON a2.id = relationships.parent
AND a2.type = 'Instance'
was giving me a false result if the database had a parent relationship to something else (which it does).
Can anyone advise how I can do this without the subquery? I'm sure there's a way but I haven't found it.
Thanks!
June 19, 2015 at 10:35 am
Maybe something like this?
declare @artefacts table (id int, name char(10), type char(10))
declare @relationships table (parent int, child int)
insert into @artefacts values
(1, 'myInstance', 'Instance'),
(2, 'mySite', 'Site'),
(3, 'myDatabase', 'Database')
insert into @relationships values
(1, 3),-- comment out this line to get a database without a parent instance
(2, 1),
(2, 3),
(3,2)--I added this so there would be a row where the database was a parent (the problematic condition you noted)
SELECT
Child.Name as DB
FROM
@artefacts Child
LEFT JOIN
@relationships relationships
ON relationships.child = Child.id
LEFT JOIN
@artefacts Parent
ON Parent.id = relationships.parent
AND Parent.type='Instance'
WHERE Child.type='Database'
GROUP BY (Child.Name)
HAVING COUNT(Parent.Name)=0
Cheers!
June 19, 2015 at 11:33 am
By default, SQL Server joins tables in left-to-right order, but you can override that behavior by using parentheses in your JOIN clause.
SELECT
artefacts.name AS DB, *
FROM
@artefacts artefacts
LEFT OUTER JOIN (
@relationships relationships
INNER JOIN @artefacts AS a2
ON a2.id = relationships.parent
AND a2.type = 'Instance'
)
ON relationships.child = artefacts.id
WHERE
artefacts.type = 'Database'
AND
a2.name IS NULL
Since you didn't give your expected results, I'm not sure if this does what you want.
Drew
PS: If your ODBC driver doesn't support subqueries, it's likely that there are other features that it doesn't support. You might need to find a support forum for that specific version of the ODBC driver.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 19, 2015 at 2:47 pm
Are you able you simply put the query in a stored proc and then simply execute the sp over the ODBC connection?
June 19, 2015 at 2:54 pm
drew.allen (6/19/2015)
By default, SQL Server joins tables in left-to-right order,
I'm not sure it's safe to make that blanket statement. I think SQL will join in whatever order it deems best unless you explicitly indicate otherwise. An OUTER join adds additional restrictions on how SQL must process the join, but I don't believe it necessarily forces a specific join order.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 19, 2015 at 3:23 pm
ScottPletcher (6/19/2015)
drew.allen (6/19/2015)
By default, SQL Server joins tables in left-to-right order,I'm not sure it's safe to make that blanket statement. I think SQL will join in whatever order it deems best unless you explicitly indicate otherwise. An OUTER join adds additional restrictions on how SQL must process the join, but I don't believe it necessarily forces a specific join order.
Yes, I was aware of that, but, to the best of my knowledge, the results will always be the same as if it had done the joins left-to-right, so I felt that it was an unnecessary complication to bring that up.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 22, 2015 at 2:16 am
Many thanks; this worked perfectly!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply