Self-Joins?
-
I have been asked to design a particular query for our database. Here is a brief description of the situation:
Every piece of hardware in our inventory is assigned an account number. When that system is retired, the account # is recycled and is given to a new system. A "yes" appears in the "associated?" column to indicate that that account number is associated to that particular piece of hardware, a "no" would mean that the system no longer exists and that account # is no longer associated with it. The table looks a little like this:
Account # Hardware ID Associated?
45 34 No
45 78 Yes
46 65 No
46 97 No
It is my job to create a query that will pull all of the account #'s that are not associated to any hardware so they can be recycled. I have tried a self-join, and everythign else i can think of, with no luck. Any ideas?
-
Hi Ramona!
Could you please write down the table structure of those tables that will be involved in the query? If I don't missunderstand your question, there should be at least two tables involved?
robbac
___the truth is out there___ -
Oh! yes of course! Sorry, there is a table with all account information (account #, hardware ID, Description) and the second table has all of the associations in it (hardware ID, Associated?).
-
Sorry for beeing a pain here, but where is the information stored about system that you mention?
Write down either the structure of the two tables, or those tables that are involved in the query, as well as some sample data from the tables.
robbac
___the truth is out there___ -
No Pain at all!!! Thank you so very much with your help on this!!
There are actually three tables I am looking pulling this information from.
There is a “Systems” table that contains information about the hardware. The primary Key for this table is the SystemID column
The table that ties this table to the “Account table” is called the “Systems_Account_Association” table, this table tells us whether or not the System is Associated to a particular account number. Here is some sample data from each table:
“Systems” Table:
SystemID HardwareID Description
==============================
1 123 MarketingPC123
2 124 MarketingPC124
3 126 <st1
ersonName>Accounting</st1ersonName>PC126“Account” Table:
System ID Account #
=================
1 5649
2 5650
3 5651
“Systems_Account_Association” Table:
ID SystemID Account # Associated?
=======================================
1 123 5649 YES
2 133 5649 NO
3 124 5650 NO
4 486 5650 NO
I am basically looking for all account #’s that have no systems associated to them.
-
Hi again!
I was a pain
From the last information I do realize (read understand) your question. You only need to use the last table "Ststems_Accou.." for your query. I've written two different selects for you. One with a left join and one with a sub-select. Depending on you indexes and amount of data one of the two might run faster then the other.SELECT DISTINCT
[Account#]
FROM [System_Account_Association]
WHERE [Associated?] like 'NO' and
[Account#] not in (
SELECT [Account#]
FROM [System_Account_Association]
WHERE [Associated?] like 'YES' )
SELECT DISTINCT
[SAA1].[Account#]
FROM [System_Account_Association] AS [SAA1]
LEFT JOIN [System_Account_Association] [SAA2] ON [SAA1].[Account#] = [SAA2].[Account#]
AND [SAA2].[Associated?] = 'YES'
WHERE [SAA2].[Account#] IS NULL AND
[SAA1].[Associated?] = 'NO'
I hope this will help you in your query.
robbac
___the truth is out there___ -
"I am basically looking for all account #’s that have no systems associated to them"
SELECT [Account#]
From [System_Account_Association]
where [Associated?] ='NO'
group by [Account#] -- in stead of distinct because it may be index-supported !
-- Order by [Account#]
As I understand you have doubts about the accuracy of your [System_Account_Association] table's content couvering _all_ data.
Robbac 's queries are the ones you need,
but Robbac why use the " like " conditions in query1 because you can use " = "
Be carefull if your [Account#]-column is nullable in the table [System_Account_Association] ! As long as there are no rows with null for [Account#] there is no problem, but when there are nulls in [Account#] you should only select the " is not null"-s in the " not in "-clause of query1.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi alzdba!
I do agree that the first querys where-statement is unnecessry and correct statement should be:
SELECT DISTINCT
[Account#]
FROM [System_Account_Association]
WHERE [Account#] NOT IN (
SELECT [Account#]
FROM [System_Account_Association]
WHERE [Associated?] LIKE 'YES' )
But your query with gruop won't work, since you will only get the records that are not associated with any system. The records you retrieve might still have a record with an association.
There are many different ways to write this kind of queries, and I would suggest that you try all of them and check the queryplan to see wich is the best. Or simply run them one by one and see how long they run.
robbac
___the truth is out there___ - aaarch Wednesday morning syndorme
affected my query !-- if all [account#] have rows in
-- [System_Account_Association]
-- if there is only 'Yes' and 'No' in Associated? then this should do fine.
-- correlated subquery to accomplish self-join
SELECT [Account#]
From [System_Account_Association] SAA_No
where not exists (select *
from [System_Account_Association] SAA_Yes
where SAA_Yes.[Associated?] ='YES'
and SAA_Yes.[Account#] = SAA_No.[Account#] )
group by [Account#]
-- order by [Account#]
-- left self-joined example
SELECT SAA_No.[Account#]
From [System_Account_Association] SAA_No
left join [System_Account_Association] SAA_Yes
on SAA_No.[Associated?] ='NO'
and SAA_Yes.[Associated?] ='YES'
and SAA_No.[Account#] = SAA_Yes.[Account#]
where SAA_Yes.[Account#] is NULL
group by SAA_No.[Account#]
-- order by SAA_No.[Account#]
This way it _is_ a self-join of object [System_Account_Association]
Robbac, what I meanth to say is : why use " like " if you can use " = " with a predicate. (you did not provide a wildcard - and it was not needed to put a wildcard so like could be avoided).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Looks like we can go on forever 
Your first query is correct, but the second one will return all rows in the table.
Why I use LIKE instead of =?
Checking queryplan, once with LIKE and once with =, LIKE use 13% for the nested loop and = use 50%.
robbac
___the truth is out there___ -
".. but the second one will return all rows in the table .."
No. Test it.
Left part contains only those with Associated?="No"
Right part contains only those with Associated?="yes"
joined by Account#
and I ony keep those that have no right part.
This should leave me only those that have "No" and don't have "Yes"
Query plan :
these % are relative ! the relative cost for " = " is higher because it may be used for direct filtering.
This all depends on statistics and should be tested on the actual data.
Ramona Roman : test all proposals and choose that one that suits you best.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sorry, but that's what I've done, tested the query that is. And it returns all the rows in the test-table that I have.
About the LIKE or = operators, of course this should be compared on real data...
I agree with you, test and pick the one(s) that you find gives you the answer you need.
robbac
___the truth is out there___
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply