Jump to content

Parent/Child problem simplified version


jay0316

Recommended Posts

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?

 

 

 

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.