INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Self-Join returning odd results

Self-Join returning odd results

(OP)
Hi,
I have a quick question, I hope. smile The issue is quite simple. I'm trying to create a 5-level (purposely limited to 5) hierarchical query, that's returning odd results. For simplicity, I'll just include the relevant stuff.

I have a table, called, "CONTENT_Items". This table has an ItemID PK and a ParentItemID column, both of type int. This table currently has exactly one row in it:
---
ItemID, ParentItemID
1, 0
---

When I run the following query:

CODE --> SQL

SELECT c1.`ItemID` c1ItemID,
		c2.`ItemID` c2ItemID,
		c3.`ItemID` c3ItemID,
		c4.`ItemID` c4ItemID,
		c5.`ItemID` c5ItemID
	FROM `CONTENT_Items` c1
		LEFT JOIN `CONTENT_Items` c2 ON c2.ParentItemID = c1.ItemID
		LEFT JOIN `CONTENT_Items` c3 ON c3.ParentItemID = c2.ItemID
		LEFT JOIN `CONTENT_Items` c4 ON c4.ParentItemID = c3.ItemID
		LEFT JOIN `CONTENT_Items` c5 ON c5.ParentItemID = c4.ItemID
	WHERE c1.ParentItemID = 0 

...I should get the following results:
c1ItemID, c2ItemID, c3ItemID, c4ItemID, c5ItemID
1, NULL, NULL, NULL, NULL

...but instead, I get:
c1ItemID, c2ItemID, c3ItemID, c4ItemID, c5ItemID
1, NULL, 1, NULL, 1

Why is this? And how do I get the desired (first) set of results instead?

Many thanks! smile

Katie

RE: Self-Join returning odd results

Hi

Interesting. The MySQL 5.7.12 I use returns what you expect :

CODE --> MySQL command-line tool

test> select * from `CONTENT_Items`;
+--------+--------------+
| ItemID | ParentItemID |
+--------+--------------+
|      1 |            0 |
+--------+--------------+
1 row in set (0.00 sec)

test> \. Katerine.sql
+----------+----------+----------+----------+----------+
| c1ItemID | c2ItemID | c3ItemID | c4ItemID | c5ItemID |
+----------+----------+----------+----------+----------+
|        1 |     NULL |     NULL |     NULL |     NULL |
+----------+----------+----------+----------+----------+
1 row in set (0.00 sec) 

Feherke.
feherke.ga

RE: Self-Join returning odd results

(OP)
Hm. According to "SHOW VARIABLES LIKE "%version%";", the SQL Server version on the server (which I'm unable to change, because it's a shared host) is 5.0.83-community. Is there some sort of bug with that version? Or maybe some setting that could be explaining it that another client requested that the hosting provider set?

[Edit]: Oh, could it possibly have to do with the fact that the default value of ParentItemID is set to 0? Checking... no, removing the default doesn't seem to make a difference. There's no way the other columns in the table could make a difference, is there? Or the fact that ItemID is auto-increment? I'm just brainstorming for random ideas now... I really need this query to work, and quickly.

[Update]: I just created a fresh table, called testtable, with just the ItemID and the ParentItemID column, inserted the one row, and ran the query on it, and it returned the same results. So it doesn't seem to be a table-level problem, but rather a server-level problem? What do you think?

Thank you, btw, for checking into this. smile

Katie

RE: Self-Join returning odd results

Hi

Sorry, I'm quite new to MySQL, so no idea about such version incompatibilities. And I have no older database available right now. sad

"community" ? Also interesting. I knew that MySQL's community fork is the MariaDB... I made another test with MariaDB 10.0.23 ( released 18 Dec 2015 ) and same successful result.

Feherke.
feherke.ga

RE: Self-Join returning odd results

(OP)
I think the "community" part is referring to the fact that it's a shared MySQL server, shared between many different clients? That's just what I assumed. smile

Update: I changed ParentItemID to -1, and changed the query's WHERE condition to look for -1, and it worked. It's a workaround, and it sucks, but it'll get the job done. smile Many thanks again for looking into this!

Katie

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close