jay0316 Posted February 10, 2010 Share Posted February 10, 2010 Below I have the SQL for 2 tables that are simplified versions of what I'm working with. I need to print a list of all stores where the completed by field is by "Todd". However, I need the list to always print the parent store first with the children underneath. Can I do that with the way things are setup with some combination of SQL and php? Table 1 -- -- Table structure for table `table1` -- CREATE TABLE `table1` ( `id` int(2) NOT NULL auto_increment, `parent` int(2) NOT NULL, `child` int(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ; -- -- Dumping data for table `table1` -- INSERT INTO `table1` (`id`, `parent`, `child`) VALUES (1, 2, 3), (2, 2, 3), (3, 2, 1), (4, 4, 5); Table 2 -- -- Table structure for table `table2` -- CREATE TABLE `table2` ( `id` int(2) NOT NULL auto_increment, `store_name` varchar(255) collate latin1_general_ci NOT NULL, `completed_by` varchar(255) collate latin1_general_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ; -- -- Dumping data for table `table2` -- INSERT INTO `table2` (`id`, `store_name`, `completed_by`) VALUES (1, 'Walmart Supermarket', 'Todd'), (2, 'JC Penny', 'Todd'), (3, 'Wal-mart', 'Todd'), (4, 'Dollar General''s Store', 'Jeff'), (5, 'Dollar General', 'Todd'), (6, 'Giant Eagle', 'Mark'); The way I was trying to do it is by selecting all the parent stores and stores that don't have a parent child relationship: SELECT * FROM table1 WHERE NOT EXISTS ( SELECT child FROM table1 WHERE id = child ) AND completed_by = 'Todd' Then I with php i checked to see if the id was a parent, and if it was I printed the parent and then did another mysql select statement to select all the child id's and printed them out below. The problem I have is that sometimes the parent store isn't completed by "Todd", so I'm missing some of the child stores. Any ideas? Quote Link to comment Share on other sites More sharing options...
jay0316 Posted February 15, 2010 Author Share Posted February 15, 2010 anyone? If you need more info, just let me know what you need and I'll give it to you. I could really use some help. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 15, 2010 Share Posted February 15, 2010 Hi Not 100% sure what you want. I think you want a list of all the parent stores where completed by is 'Todd' and all the child stores for that parent. In which case something like this should do it. SELECT * FROM table1 a INNER JOIN table2 b ON a.parent = b.id INNER JOIN table2 c ON a.child = c.id WHERE b.completed_by = 'Todd' All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.