SQL Select Statement Help Needed

[stanlyn]

Hi,

I need to some help figuring out a select statement, let me explain... This is a parent/child setup each having a field named KEY that will relate them.

One(parent) to Many(child) - and assuming that each child only has 1 parent... used only as an analogy, (the parent/child references)

Table #1 - Table name: PARENT
Fields: key, name
Parents Sample Data:
111, John
222, Mary
333, Jane

Table #2 - Table name: Child
Fields: key, name
Child Sample Data:
222, Jill - Mary's child
222, Steve - Mary's child
111, Bob - John's child
333, Robert - Jane's child
111, Kay - John's child
111, Lyn - John's child
333, Crystal - Jane's child

Objective is to find and list all the children that belongs to the parent when only a child's name is known.

In this case we want to find out who LYN's parent and siblings are, so we
1. Search the child's table on name for "LYN" and get the key,
2. use that key and do a lookup also to the same/child table to get a list of all the siblings/children that has the same key,
3. then do a lookup using the same key to the parents table to get the parents data,

The result set will contain the parent data of "John" and siblings Bob, Kay, and Lyn...

Thanks, Stanley

 

[cantor001]

Here is one way - all depends how you want the output formatted:

DECLARE @child_name VARCHAR(100); SET @child_name = 'Lyn'
SELECT p.[name] AS [name],'parent' AS relationship
FROM [Child] child
JOIN [Parent] p
ON p.[key] = child.[key]
WHERE child.[name] = @child_name
UNION ALL
SELECT sib.[name] AS [name],'sibling' AS relationship
FROM [Child] child
JOIN [Child] sib
ON sib.[key] = child.[key]
WHERE child.[name] = @child_name

 

[stanlyn]

Hi cantor001 ,

Thanks for your quick reply, however I'm getting nowhere with this. I need details on whats going on with this complex query.

Google isn't returning much of anything for: sql "AS relationship", neither is w3schools.com. There is more here than anywhere, and I haven't seen any used with t-sql...

Even books online has nothing to say about it, so details would be good...

I get the line 1 setting of a variable, and lt looks like you have a 3rd results table names sib...

Thanks, Stanley

 

[r937]

the AS that you are asking about assigns an alias

[red]p.[name] AS [name][/red] assigns a column alias but it's really not very useful to do this, because the column alias is the same as the column name

[red]'parent' AS relationship[/red] assigns a column alias to the string value -- "relationship" is the alias name of the column, and the column will contain either 'parent' or 'sibling'

[red][Parent] p[/red] assigns a table alias, and here you can see that the AS keyword is optional


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cantor001]

Yes, many apologies Stanley, this query could have been a lot simpler but I thought you wanted the whole thing in just one result set which made it a bit more complex.

DECLARE @child_name VARCHAR(100); SET @child_name = 'Lyn'
-- as you say we declare a local var and set its value to the string 'Lyn' - I am just using this as a test to return an example of Lyn's parents and siblings

SELECT
p.[name] AS [name],
'parent' AS relationship
FROM [Child] child
JOIN [Parent] p
ON p.[key] = child.[key]
WHERE child.[name] = @child_name
-- we query the child table, get the row for 'Lyn' (child.[name] = @child_name) and then use its key get the row for Lyn's parent (p.[key] = child.[key]) from the [Parent] table

p.[name] AS [name], -- this bit just returns the name of Lyn's parent as a column named [name]

'parent' AS relationship -- this bit returns the type of relationship as a column named [relationship], in this case we indicate that the relationship is child to parent

SELECT sib.[name] AS [name],'sibling' AS relationship
FROM [Child] child
JOIN [Child] sib
ON sib.[key] = child.[key]
WHERE child.[name] = @child_name
-- this code just returns all children with the same parent as that of the child whose name is held in @child_name, i.e. first we follow a similar pattern as we did for the parent, we query the child table, get the row for 'Lyn' (child.[name] = @child_name) except this time we join again to the [Child] table using the key to find all other children with the same key - i.e. siblings
"JOIN [Child] sib ON sib.[key] = child.[key]"

sib.[name] AS [name], -- this bit just returns the name of Lyn's siblings as a column named [name] (note that we also return Lyn herself)

'sibling' AS relationship -- this bit returns the type of relationship as a column named [relationship], in this case we indicate that the relationship is child to sibling

UNION ALL
-- this union all just returns the two result sets 'child to parent' and 'child to sibling' from the above two select statements as one set

A less complex example would be:

SELECT p.[name] [parents_name], sib.[name] [childs_name]
FROM [Child] child
JOIN [Child] sib
ON sib.[key] = child.[key]
join [Parent] p
on p.[key] = child.[key]
WHERE child.[name] = @child_name

 

[cantor001]

p.[name] AS [name] assigns a column alias but it's really not very useful to do this, because the column alias is the same as the column name"

Yes, well spotted, I'm looking back at the code and thinking 'why the heck did I do this?'. I suspect that its because initially I wanted to rename the [name] column in the result - and then promptly forgot to do so...this happens when one misses one's nanna nap

 

[stanlyn]

Thanks,

I'll try this a bit later today and see if I can make it work. I've got another query that I also need help with, but will post a separate thread for it.

Again thanks... Stanley

 

[stanlyn]

Hi cantor001 and r937,

Still not working... after about 50 versions... or 2 hours... Below is the latest version which needs some help...

use [library]
DECLARE @author_name VARCHAR(100); SET @author_name = 'Limeback, Rudy'

SELECT
b.[book_type] as [BookType],
b.[book_name] as [BookName],
sib.[author_name] as [AuthorName]
FROM
[assets].[AuthorName] as child
JOIN [Child] as sib
ON sib.[key] = child.[key]
join [assets].[Book] b
on b.[key] = child.[key]
WHERE
child.[author_name] = @author_name

I've changed the metaphors to more closely align to what I'm doing. The idea here is to get all the books that Rudy Limeback has written along with his co-authors that has helped him co-write the books. When searching only on Rudy's name, and we will get a list of all his books along with his co-authors...

Thanks, Stanley

 

[cantor001]

Posting an answer is a little tricky I'm a little unclear how you are modelling this.

One possible structure for this would be:

Author
======
author_id
author_name

Book
====
book_id
book_name
book_type

BookAuthor (assoc entity)
==========
author_id
book_id

If you can post the schema that would be great.

 

[stanlyn]

Hi cantor,

Parent Table: Book
Fields: book_key, book_name, book_type, book_id

Child Table: Author
Fields: book_key, author_name, author_id

Hope this helps,
Stanley

 

[cantor001]

Sure, what are the pks for Book and Author tables ?

 

[cantor001]

From what I understand of the schema this should do the trick - but, respectfully, I think the model needs to separate the relationship between author and book into a separate entity.

DECLARE @author_name VARCHAR(100); SET @author_name = 'Limeback, Rudy'

SELECT
book.[book_type] [BookType],
book.[book_name] [BookName],
coauthor.[author_name] [AuthorName]
FROM
[Author] author
join [Book]
on book.[book_key] = author.[book_key]
join [Author] coauthor
on coauthor.[book_key] = book.[book_key]
WHERE
author.[author_name] = @author_name

 

[stanlyn]

The pks are the book_key fields in both tables...

Lets break this down...

1. SELECT
2. book.[book_type] [BookType],
3. book.[book_name] [BookName],
4. coauthor.[author_name] [AuthorName]
5. FROM
6. [Author] author
7. join [Book]
8. on book.[book_key] = author.[book_key]
9. join [Author] coauthor
10. on coauthor.[book_key] = book.[book_key]
11. WHERE
12. author.[author_name] = @author_name


Q1. Is the keyword "as" implied on lines 2,3,4?

Q2. What are the 2 authors representing on line 6?

Q3. Some items have [] around them and some do not, whats the difference? I did some searching and really didn't find a distinction, as I've seen it done in a lot of different ways, with no reasoning why...

Q4. Line 7 says "join the parent table book" Correct?

Q5. Is there an implied "as" in line 9, which is making a copy of table "Author" and naming it "coAuthor"?

Thanks, Stanley

 

[r937]

Q1 yes
Q2 more needless aliasing
Q3 brackets delimit names containing special characters (but all of the ones seen here are needless)
Q4 no, just "join book"
Q5 yes, the AS is optional, but the aliasing isn't, because without it, you'd have two tables named the same

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cantor001]

Yes, once again I have no idea why I am using all these brackets! My own code never uses reserved words for object naming and I never alias a table with its own name. Weird.

More importantly, in addition to the above comments note that
while the brackets, and the alias used in line 6 are merely pointless, the code in lines 2,3 8 and 10 will potentially break.

 

[cantor001]

The pks are the book_key fields in both tables..."
Yes, I think I'm going to have to pass on this one and leave it with r937. If book_key is the pk for both tables then I'm not sure how you would relate a book to its author using just these two entities.

good luck

 

[stanlyn]

Cantor001,

Both tables have a pk field that is named: unique_id. It was added when the vfp data was imported into it. The vfp data had a key field in both tables that contains the key used to create the relation, and has nothing to do with the pk "unique_id" which was added to the sql tables, long after 3 million records came to it. So, the parent's key field's value must be the same for the children if the children is the parent's child. However, in sql or vfp terms, is is not a primary key, you can think of it as a relationship key...

The code that you used to build the relation is correct, as the key's value must be the same for the parent and children... (remember when I said we are to assume that there can only be one parent, early up this thread, as in real life, there must be 2 parents, not so here, only 1)

Hope that helps...
Stanley