Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

explain T-SQL query to me

Posted on 2010-08-18
6
Medium Priority
?
624 Views
Last Modified: 2012-05-10
Can somebody explain this SQL query to me? what is it doing?

DECLARE @CollectionID int      

SELECT @CollectionID= C2.CollectionID
FROM Collections C1
      INNER JOIN Collections C2 on C1.CollectionID = C2.ParentID
WHERE C1.Title= '09-0274'
      --AND C1.CompanyID= 1 and C2.Title LIKE 'Vendor-'  + '240635'
      AND C1.CompanyID= 1 and C2.Title LIKE '%240634%'

0
Comment
Question by:troyvw
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33466143
This query will return the CollectionID from Collections table satisfying the following criteria
1. 'Title' column data should contain '240634'
2. Its parent Collection's CompanyID should be 1
3. Its parent Collection's Title should be '09-0274'

Clear ?
Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 668 total points
ID: 33466170
This type of JOIN is called 'Self Join' - Joining a table with itself.
http://msdn.microsoft.com/en-us/library/ms177490.aspx

In your case, 'ParentID' column is JOINed to 'CollectionID' column of the same table.

Raj
0
 
LVL 2

Assisted Solution

by:rajeshprasath
rajeshprasath earned 668 total points
ID: 33466189
DECLARE @CollectionID int

This statement declares the variable CollectionID.

SELECT @CollectionID= C2.CollectionID
FROM Collections C1
      INNER JOIN Collections C2 on C1.CollectionID = C2.ParentID
WHERE C1.Title= '09-0274'
      --AND C1.CompanyID= 1 and C2.Title LIKE 'Vendor-'  + '240635'
      AND C1.CompanyID= 1 and C2.Title LIKE '%240634%'

Then from the above query @CollectionID varible is used to store the CollectionID column of the Collection table.

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records.

This query is used to fetch the collectionids of each partner.

http://databases.about.com/od/sql/a/selfjoins.htm
http://sqlservercodebook.blogspot.com/2008/03/how-to-use-self-join-in-sql-server-2000.html
http://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-self-join/
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Expert Comment

by:Maverick543
ID: 33466191
Here it goes.
The inner join connects collection C1 and C2 on the field ParentID of C2 and the field CollectionID of C1.
It then selects all records from the joined collection where CompanyID = 1, Title (from C2) contains '2406234' somewhere in the field and the Title of C1 = '09-0274'
The line starting with the -- is just a comment.
The way the query is written it is assumed there will be only 1 return row. And of that row the content of the field CollectionID is stored in a local variable @CollectionID
It looks like this query is part of a large SQL script (or Stored Procedure), I am correct?
Regards
Marco
0
 
LVL 13

Assisted Solution

by:khairil
khairil earned 664 total points
ID: 33467082

Hi,

let me break it up in line number. For more clear version, we will replace line 2 with select all columns, instead of select into variables.

1. DECLARE @CollectionID int      

2. SELECT C2.*
3. FROM Collections C1
4. INNER JOIN Collections C2 on C1.CollectionID = C2.ParentID
5. WHERE C1.Title= '09-0274'
6. --AND C1.CompanyID = 1 and C2.Title LIKE 'Vendor-'  + '240635'
7. AND C1.CompanyID = 1
8. and C2.Title LIKE '%240634%'
===================================================
Here is some sample data

Collections
=============================================
CollectionID  ParentID   Title      CompanyID
1                 0               AB2406341  1
2                  1          09-0274    1
3             1          09-0274    3
4             2          AD2406341  4
5             2          XF856332   5
6             3          QW85632    6


> Line 1. Declare a variable type integer

> Line 2. The real state here is "SELECT @CollectionID = C2.CollectionID", which mean put a value of CollectionID return by statements 3 - 7 into variable @CollectionID (value return must be in integer). Note; @CollectionID is not just 'a' value, but an array of value which each element represent by @CollectionID. Simply said @CollectionID have collection of values.

Line 3 & 4. Self joining a "Collections" table with another instance of "Collections" table with condition where first instance (C1) of "Collections" table's ColleciontID must be same with the second instance (C2) of "Collections" table's ParentID. Running sentance 1 - 4 on sample data will return result from instance of "Collections" tables C2 as below:

C2
============================================
CollectionID  ParentID  Title      CompanyID
2             1         09-0274    1
3             1         09-0274    3
4             2         AD2406341  4
5             2         XF856332   5
6             3         QW85632    6

First and second row return because there is CollectionID with value 1 in Collections tables.

Third and forth row return because there is CollectionID with value 2 in Collections table

Fith row return because there is CollectionID with value 3 in Collections table.


> Line 5. We add one more condition, we want instance of Collections table C1 to be more specific, which is only get data that have Title EQUAL to '09-0274'.

Running this statement alone:

SELECT C1.* FROM Collections C1 WHERE C1.Title = '09-0274'

Will return two rows, notice that CompanyID is different - means that it is not the same row:

C1
=============================================
CollectionID  ParentID   Title      CompanyID
2                  1          09-0274    1
3                  1          09-0274    3

Now combine with the first condition, we want ParentID in C2 to be equal to CollectionID in C1. Three rows from C2 fit this condition which are:

C2
============================================
CollectionID  ParentID  Title      CompanyID
4             2         AD2406341  4
5             2         XF856332   5
6             3         QW85632    6

Both have ParentID of 2 & 3, which exist in C1 CollectionID.

> Line 6. Is commented by --. Anything commented will not effect the query.

> Line 7. We add one more condition, third condition. Beside of having C1's Title equal to '09-0274', we also want that it have CompanyID value 1.

Having condition, we can first simplify the statement as follow:

SELECT C1.* FROM Collections C1 WHERE C1.Title = '09-0274' AND C1.CompanyID = 1

Which will return only a row, which satisfy the company must have Title EQUAL to '09-0274' AND CompanyID EQUAL 1:
C1
=============================================
CollectionID  ParentID   Title      CompanyID
2                  1          09-0274    1

Now combine with the C2 result in Line 5 above. Compare the ParentID of C2 with the CollectionID of C1 return above. Only two rows return for C2, which have ParentID equal to 2, which equal to C1 CollectionID of 2. There is no C1 CollectionID equal to 3, so last row of C2 get rejected. Result of C2 now are:

C2
============================================
CollectionID  ParentID  Title      CompanyID
4             2         AD2406341  4
5             2         XF856332   5


I hope you have get the clear picture now.

> Line 8. Forth condition. We want to filter C2 further more. We want only data that have '240634' inside it's Title. From result return by C2 above, only 1 row fulfill the condition:

C2
============================================
CollectionID  ParentID  Title      CompanyID
4             2         AD2406341  4

Only this line have '240634' in it's Title.

OK. Now return back to real statement - that want to us to return only CollectionID into @CollectionID variable. The result in this sample is an array of one:

@CollectionID
=============
4


*Hope this can make you understand better. Enjoy.


0
 
LVL 13

Expert Comment

by:khairil
ID: 33467163
Here is an MS Access sample for you.

I have add one more column for ID (you can ignore it if you want to).
I use CollectionID column instead of variable here.

I also remove the commented statement and replace like state from '%240634%' to '*240634*', MS Access using * as %.


Collections.mdb
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question