SQL Query - Joining table to itself and getting desired data?

[mattboy_slim]

I have a table of parents and children, and I need to pull the information for a parent from the child.

I have tried various combinations of JOINS with no luck:

"SELECT * FROM dbo.tb_locationsmachines l JOIN dbo.tb_locationsmachines p ON l.f_itemparentID=p.f_itemID WHERE l.f_itemID = '" & var_groupID & "'"

var_groupID is pulled from the querystring, so I know that it is correct, as I can response.write and receive the proper result.

Say the table looks like this:
f_ID f_itemname f_itemparent
114 Child 115
115 Parent 0

I want to feed the query the f_ID of 114, and get information from the child, and the parent. So I would want to be able to show the following data:

Child ID: 114
Child Name: Child
Parent's ID: 115
Parent's Name: Parent

Any advice is appreciated.

Thanks,
Matt

[Old Pedant]

First of all, let's make the code readable.

SQL = "SELECT * " _
& " FROM dbo.tb_locationsmachines l " _
& " JOIN dbo.tb_locationsmachines p ON l.f_itemparentID=p.f_itemID " _
& " WHERE l.f_itemID = '" & var_groupID & "'"
Response.Write "DEBUG SQL: " & SQL & "<HR>"

So that code is basically saying that f_itemID is a TEXT field of some kind.

Since you say you want to pass in the value of 114, that's obviously a goof.

Try it WITHOUT the '...' around the 114.

SQL = "SELECT * " _
& " FROM dbo.tb_locationsmachines l " _
& " JOIN dbo.tb_locationsmachines p ON l.f_itemparentID=p.f_itemID " _
& " WHERE l.f_itemID = " & var_groupID
Response.Write "DEBUG SQL: " & SQL & "<HR>"


It's also a really bad idea to code SELECT * unless you really will use all (or nearly all) the fields from both tables. But that wouldn't cause an error from the query. It *COULD* cause a query when accessing the recordset.

Since you neglected to tell us what the problem you are having actually *IS*, that's about all I can see to comment on.

[mattboy_slim]

Sorry O.P., I left out the key point of information:

The problem I'm having is writing the information back out into the code. For example, I have this:

<table border="0" cellspacing="0" cellpadding="4">
<tr>
<td>Child ID</td>
<td>Child Name</td>
<td>Parent ID</td>
<td>Parent Name</td>
</tr>
<tr>
<td><%=rs_groupinformation("l.f_itemID")%></td>
<td><%=rs_groupinformation("l.f_itemname")%></td>
<td><%=rs_groupinformation("p.f_itemID")%></td>
<td><%=rs_groupinformation("p.f_itemname")%></td>
</tr>
</table>


...which generates the following on the 9th line above.

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal


If I remove the "l." and "p." in front of those values, then I only get the parent information, not the child information.

What am I missing?

Thanks,
Matt

[TheShaner]

As Old Pedant stated, using SELECT * can get you in trouble, as it has now. Since you have identical field names in the parent and child tables, you will need to use an ALIAS for identical field names. ASP is unable to use dots in index names (as far as I know).

Try:
SQL = "SELECT l.f_itemID AS lItemID, l.f_itemname AS lItemName, p.f_itemID AS pItemID, p.f_itemID AS pItemName " _
& " FROM dbo.tb_locationsmachines l " _
& " JOIN dbo.tb_locationsmachines p ON l.f_itemparentID = p.f_itemID " _
& " WHERE l.f_itemID = " & var_groupID
...
<tr>
<td><%=rs_groupinformation("lItemID")%></td>
<td><%=rs_groupinformation("lItemName")%></td>
<td><%=rs_groupinformation("pItemID")%></td>
<td><%=rs_groupinformation("pItemName")%></td>
</tr>
Also, if a situation ever arises where a parent item could have multiple children (or vice versa), your table setup won't allow for it. You would need two tables: one table would have your item information and another one would hold item IDs and parent item IDs to show links between items.

-Shane

[mattboy_slim]

I always post my code as SELECT * because when you are selecting 30 out of 60 fields, it makes the question unclear as there isn't really any benefit in posting that much code when it is irrelevant to the question.

However, your suggestion of aliases appears to have resolved my issue of the lack of support of dots in index names.

Thanks,
Matt

[Old Pedant]

Funny thing is, if you had done this with an Access DB, and used the JET OLEDB driver, it probably would have worked. With that combination, you *can* do

value = RS("table.field")

and get the right value, even if the SELECT also included "table2.field". But not with any other database, including SQL Server. With other DBs, only the field name is significant. And if you have two the same you will get the error you saw. Note that if you really feel you NEED to use SELECT *, you can. You just have to *also* provide the aliased names:

SELECT a.ID as a_ID, b.ID as b_ID, * ....

Hacky, but it works.

[shakir]

what about the query
select * from table1 where F _ID = 114 or f_itemparent in ( select f_itemparent from table1 where F _ID = 114)

[Old Pedant]

What does that answer have to do with ANYTHING???

He *NEEDS* fields in his SELECT from *BOTH* occurrences of the table. Your answer would only get fields from ONE table. No record would give BOTH the parent AND the child.