Join Query Broken Between MySQL Versions

[reinere]

I have two tables that I'm using in this query. We'll call the first one "userGroups" and the second "links". I've included their structure below. I have one system that is running an older version of MySQL, and I also have a system running a newer version.

Older Version: 5.0.91-community-log
Newer Version: 5.5.19

Running on the older version of mysql my query executes as I need it to.

SELECT * FROM usergroups u JOIN links l ON u.group_id = l.group_id WHERE `user_id` = '1'

When running this it returns every row in the links table that has a matching group_id.

user_id - group_id - linkID - group_id
1 - 1 - 1 - 1
1 - 4 - 3 - 4
1 - 4 - 4 - 4
1 - 4 - 5 - 4

The problem I've having on the new version is that the group_id doesn't match and throws off the results of what I need to be seen/hidden.

user_id - group_id - linkID - group_id
1 - 1 - 1 - 1
1 - 4 - 2 - 3

I'm really at a loss on this. I'm not that familiar with joins at all. I've tried performing a few UNIONs with right/left and outer joins but haven't seem to have found the desired results.

___________________________________________
userGroups:
___________________________________________

CREATE TABLE IF NOT EXISTS `usergroups` (
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `usergroups`
--

INSERT INTO `usergroups` (`user_id`, `group_id`) VALUES
(1, 1),
(1, 4);

___________________________________________
Links:
___________________________________________

CREATE TABLE IF NOT EXISTS `links` (
  `linkID` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` set('1','2','3','4','5','6','7','8','9','10','11') NOT NULL,
  `linkName` varchar(32) NOT NULL,
  `linkURL` varchar(255) NOT NULL,
  `linkTarget` enum('_self','_blank','_parent','_top') NOT NULL,
  `linkSub` int(11) NOT NULL DEFAULT '0',
  `linkState` enum('Active','Disabled') NOT NULL,
  `linkOrder` tinyint(2) NOT NULL,
  PRIMARY KEY (`linkID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `links`
--

INSERT INTO `links` (`linkID`, `group_id`, `linkName`, `linkURL`, `linkTarget`, `linkSub`, `linkState`, `linkOrder`) VALUES
(1, '1', 'Home', 'ucp.php', '_self', 0, 'Active', 1),
(2, '3', 'Networks', 'networks.php', '_self', 0, 'Active', 2),
(3, '4', 'Public', 'publicnetwork.php', '_self', 2, 'Active', 1),
(4, '2,4', 'Private', 'privatenetwork.php', '_self', 2, 'Active', 2),
(5, '4', 'IPv6', 'ipv6network.php', '_self', 2, 'Active', 3);

[simplypixie]

Tray changing your select query to indicate which table to reference for the user id

SELECT * FROM usergroups u INNER JOIN links l ON u.group_id = l.group_id WHERE u.user_id=1

[reinere]

The query results are still the same with or without the table reference. The query doesn't have a problem running. It has a problem returning desired results.

[simplypixie]

I didn't think it would have a problem running but how you format your query and join obviously affects what results you get which is what I am trying to help you with.

I actually for got something in the query, but before I change it I need to know exactly what ecords you want from each table.

[smantscheff]

Your query tries to compare the equality of a single value with a whole set. Try instead:

SELECT * FROM usergroups u, links l where u.`user_id` = '1' and find_in_set(u.group_id,l.group_id);

[reinere]

Your query tries to compare the equality of a single value with a whole set. Try instead:

SELECT * FROM usergroups u, links l where u.`user_id` = '1' and find_in_set(u.group_id,l.group_id);

Excellent! This is exactly what I needed. Thank you so much. I'm still a bit curious as to why it was broken between versions, but as long as it works. =)