Parent/Child problem simplified version

[jay0316]

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?

 

 

 

 

 

 

[jay0316]

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.

[kickstart]

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