mysql simple query problem

Philip123
Philip123 used Ask the Experts™
on
Oh man, have I been up every night this week to sort this simple query out. Please someone help.

I have list table called " pol_doc_list " . It has 9 names within it. I have another table called " pol_doc_det " which has a foreign key to the " pol_doc_list " called " id_pol_doc_list " . I have another "id_policies" in this "pol_doc_det" table.

Now I have a page with the list iterating its names with an add or edit column. When add is clicked from one of the items within the list it posts an url to the detai page with the id_policy and id_pol_doc_list to the detail page. These are hidden variables that the detail page needs for a query. When the detail page is submitted it returns to the listed page of items, the add now of the list item previously clicked on to add now shows edit.

Due to the query I have created the list now shows an item less. What I need is to have the whole list present irrespective of adding items on the details page. At the same time I need this list query to have some intelligent of the detail page on the list page so that the add can change to edit. Respectfully the id_policies and the id_pol_doc_list variable needs to be present for the detail page.

The query has to select the id_pol_doc_list in the pol_doc_list as well as the associated foreign key of the pol_doc_det.id_pol_doc_list where the id_policies is lets say = 14. From the list view I would like to see all that my list comprises of, 9 items, as well as the id_policies of the detailed page without my list shifting more and less in total items listed.

Here is the tables and the query, please find code snipped.

I have tried AND and OR within my SELECT JOIN WHERE query with very random results.

Your expert solution would be greatly appreciated. I am a super beginner so please supply the detailed query if you could.



CREATE TABLE IF NOT EXISTS `pol_doc_list` (
  `id_pol_doc_list` int(11) NOT NULL auto_increment,
  `pol_doc_name_short` varchar(100) default NULL,
  `pol_doc_list_name` text,
  PRIMARY KEY  (`id_pol_doc_list`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `pol_doc_list`
--

INSERT INTO `pol_doc_list` (`id_pol_doc_list`, `pol_doc_name_short`, `pol_doc_list_name`) VALUES
(1, 'Receipt of a fully completed Policy Acceptance...', 'Receipt of a fully completed Policy Acceptance Form Participation Quota Sheet as per Sectional Plan\r\n'),
(2, 'Special Management Rules...', 'Special Management Rules (if applicable)'),
(3, 'Trustees'' resolution authorizing...', 'Trustees'' resolution authorizing (a) STILUS insurance; (b) authorizing signatories to this Proposal; and (c) fixing interest at the required rate (attached) Schedule of current levy arrears'),
(4, 'Last adopted budget', 'Last adopted budget'),
(5, 'Minutes of members'' resolution...', 'Minutes of members'' resolution adopting budget'),
(6, 'Minutes of trustees'' resolution...', 'Minutes of trustees'' resolution apportioning the levies, including apportionment schedule'),
(7, 'Schedule of current levy arrears', 'Schedule of current levy arrears'),
(8, 'Latest audited financial statements', 'Latest audited financial statements'),
(9, 'Documents relating to any current levies...', 'Documents relating to any current levies dispute, if any');

---------------------------------------------

CREATE TABLE IF NOT EXISTS `pol_doc_det` (
  `id_pol_doc_det` int(11) NOT NULL auto_increment,
  `id_pol_doc_list` int(11) default NULL,
  `id_policies` int(11) NOT NULL,
  `pol_doc_det_remarks` text,
  `pol_doc_det_email_notice1` date default NULL,
  `pol_doc_det_email_notice2` date default NULL,
  `pol_doc_det_email_notice3` date default NULL,
  `pol_doc_det_link_display` varchar(120) default NULL,
  `pol_doc_det_file` varchar(120) default NULL,
  `pol_doc_det_created` date default NULL,
  `pol_doc_det_updated` date default NULL,
  PRIMARY KEY  (`id_pol_doc_det`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=58 ;

INSERT INTO `pol_doc_det` (`id_pol_doc_det`, `id_pol_doc_list`, `id_policies`, `pol_doc_det_remarks`, `pol_doc_det_email_notice1`, `pol_doc_det_email_notice2`, `pol_doc_det_email_notice3`, `pol_doc_det_link_display`, `pol_doc_det_file`, `pol_doc_det_created`, `pol_doc_det_updated`) VALUES
(57, NULL, 14, NULL, NULL, NULL, NULL, NULL, '', '2011-08-05', NULL);

------------------------------------------

Here is the query

SELECT DISTINCT pol_doc_list.pol_doc_list_name, pol_doc_list.id_pol_doc_list, pol_doc_det.id_pol_doc_det, pol_doc_det.id_policies FROM (pol_doc_list LEFT JOIN pol_doc_det ON pol_doc_det.id_pol_doc_list=pol_doc_list.id_pol_doc_list) WHERE pol_doc_det.id_policies=14 OR pol_doc_det.id_policies IS NULL ORDER BY pol_doc_list.pol_doc_list_name ASC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
First of all, both your tables are MyISAM so it can not have the foreign key constraint. You have to use InnoDB to create foreign constraints.  And in your pol_doc_det there is no value for the id_pol_doc_list. Its null. It has to point it to some row of pol_doc_list.
theGhost_k8Database Consultant
Commented:
1. Description is not easy to understand :) may be u can also provide with output you get vs output you want.
2. MyISAM will just list the foreign key but wont force the constraint; anyways that's not the problem here - you can handle it with your queries.
and as already said - your join condition is not being satisfied here and hence you're not getting data.

Author

Commented:
Thank you so much for your quick responses! I know about the NULL, my mistake. After trying queries and deleting them from the details in mysql I should of rechecked the data. Sorry about that.

Here is a refreshed one inserts for the detail page, see attached.

I have also included the list page screenshot. As you can see the top list has to be displayed in its entirety. The add changes to edit when the id_policies value returns true with its id. This list display has to have the id_policies variable collected within its query so that I can retrieve a policies related detail attachments.

I have another query at the bottom in a column display which adds those that were added form the list. The bottom email notices, etc all work fine and adds to this list as they are added from the total list of items that could possibly be displayed.

This is an admin section of a small company and they need this flexibility because on each policy requires all the necessary items that can be displayed.

the id_pol_doc_list is the common identifier here, but I also need a id_policies in it and on top of it the total list of items that can be added need to be displayed in its entirety. When I use a WHERE to select the id_policies it lists only those in the list that have an id_policy of a selected interger. Hence my dilemma. The idea is to return this and display the full list of items that can be attached.

Thank you once again for your patience.

Kind regards



INSERT INTO `pol_doc_det` (`id_pol_doc_det`, `id_pol_doc_list`, `id_policies`, `pol_doc_det_remarks`, `pol_doc_det_email_notice1`, `pol_doc_det_email_notice2`, `pol_doc_det_email_notice3`, `pol_doc_det_link_display`, `pol_doc_det_file`, `pol_doc_det_created`, `pol_doc_det_updated`) VALUES
(58, 2, 13, NULL, NULL, NULL, NULL, NULL, '', '2011-08-06', NULL),
(59, 6, 13, NULL, NULL, NULL, NULL, NULL, '', '2011-08-06', NULL);

Open in new window

List-page-of-policy-documents.jpg
I am still not sure if I completely understood your problem. The attached query will give u pol_doc_list_name associated with the id_policies=14 and for the common id_pol_doc_list from both the tables.
SELECT pol_doc_list.pol_doc_list_name, pol_doc_list.id_pol_doc_list, pol_doc_det.id_pol_doc_det, pol_doc_det.id_policies FROM pol_doc_list, pol_doc_det WHERE pol_doc_list.id_pol_doc_list = pol_doc_det.id_pol_doc_list AND (pol_doc_det.id_policies=14 OR ISNULL(pol_doc_det.id_policies)) GROUP BY pol_doc_list.pol_doc_list_name ORDER BY pol_doc_list.pol_doc_list_name ASC

Open in new window

Author

Commented:
Thanks for all your help. Jointly the info I received helped me resolve my poroblem.

Thanks to all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial