Solved

explain T-SQL query to me

Posted on 2010-08-18
6
576 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 167 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 167 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 166 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 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