Solved

Database join help

Posted on 2013-12-20
9
394 Views
Last Modified: 2013-12-20
So I have a database with several tables that I want to combine into one table so I can export it easily into Excel or similar format.

Here are the table definitions:
CREATE TABLE IF NOT EXISTS `jos_directory_entry` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `ordering` int(11) NOT NULL,
  `published` tinyint(1) NOT NULL,
  `create_date` datetime NOT NULL,
  `hits` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=113 ;

CREATE TABLE IF NOT EXISTS `jos_directory_enf` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `entry_id` bigint(20) NOT NULL,
  `field_id` bigint(20) NOT NULL,
  `field_value` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1403 ;

CREATE TABLE IF NOT EXISTS `jos_directory_field` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `descr` text NOT NULL,
  `type` varchar(55) NOT NULL,
  `options` text NOT NULL,
  `css_class` varchar(55) NOT NULL,
  `search_include` tinyint(1) NOT NULL,
  `included` varchar(3) NOT NULL,
  `required` tinyint(1) NOT NULL,
  `ordering` int(11) NOT NULL,
  `published` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

Open in new window


The query I have tried to run is:
SELECT * FROM `jos_directory_entry` AS d 
LEFT JOIN `jos_directory_enf` e ON d.`id`=e.`entry_id`
LEFT JOIN `jos_directory_field` AS f ON e.`field_id`=f.`id`

Open in new window


This works, but it puts data into a separate row for each field for jos_directory_enf, which is what I would expect.  What I would like is to have one row for each entry in jos_directory_entry, and have the column name be the corresponding `name` field in the jos_directory_field table.  Is this possible?
0
Comment
Question by:bdhtechnology
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39732132
SELECT DISTINCT d.*, jos_directory_field.name  FROM `jos_directory_entry` AS d
LEFT JOIN `jos_directory_enf` e ON d.`id`=e.`entry_id`
LEFT JOIN `jos_directory_field` AS f ON e.`field_id`=f.`id`


hope that helps
0
 
LVL 32

Expert Comment

by:awking00
ID: 39732136
Can you provide some sample relevant data (i.e not nedessarily every field and a limited number of rows) and what you want to see as the results?
0
 
LVL 1

Author Comment

by:bdhtechnology
ID: 39732153
@esskayb2d, same results

here is sample data
INSERT INTO `jos_directory_entry` (`id`, `title`, `ordering`, `published`, `create_date`, `hits`) VALUES
(6, 'Business 1', 3, 1, '2010-09-13 22:22:19', 565),
(5, 'Business 2', 2, 1, '2010-09-13 22:21:06', 1341),
(4, 'Business 3', 1, 0, '2010-09-13 22:20:30', 172);

INSERT INTO `jos_directory_field` (`id`, `title`, `name`, `descr`, `type`, `options`, `css_class`, `search_include`, `included`, `required`, `ordering`, `published`) VALUES
(1, 'Address', 'address', 'Enter here your Address', 'textfield', '', 'field_address', 1, '2', 0, 1, 1),
(2, 'City', 'city', 'Enter here your city', 'textfield', '', 'field_city', 0, '2', 0, 2, 1),
(3, 'State', 'state', 'Enter here your State', 'textfield', '', 'state_field', 1, '2', 0, 3, 1),
(13, 'Phone', 'Phone', '', 'textfield', '', '', 0, '2', 0, 7, 1),
(5, 'Zip', 'zip', 'Enter your zip code here', 'textfield', '', 'field_zip', 1, '2', 0, 4, 1),
(14, 'Categories', 'Categories', '', 'textfield', '', '', 0, '1,2', 0, 13, 1),
(7, 'Website', 'Website', '', 'url', '', '', 0, '2', 0, 9, 1),
(8, 'Facebook Fan Page', 'Facebook_Fan_Page', '', 'url', '', '', 0, '2', 0, 8, 1),
(10, 'Twitter Page', 'Twitter_Page', '', 'url', '', '', 0, '2', 0, 10, 1),
(11, 'Description', 'Description', '', 'textarea', '', '', 0, '2', 0, 11, 1),
(12, 'Image', 'Image', '', 'image', '', '', 0, '2', 0, 12, 1),
(16, 'Fax', 'Fax', '', 'textfield', '', '', 0, '2', 0, 5, 1),
(17, 'Toll Free Phone', 'Toll_Free_Phone', '', 'textfield', '', '', 0, '2', 0, 6, 1);


INSERT INTO `jos_directory_enf` (`id`, `entry_id`, `field_id`, `field_value`) VALUES
(37, 4, 1, 'Address'),
(38, 4, 2, 'City'),
(39, 4, 3, 'State'),
(40, 4, 13, '123-456-7890'),
(41, 4, 5, 'Zip'),
(42, 4, 14, 'Hotels & Motels'),
(43, 4, 7, ''),
(44, 4, 8, ''),
(46, 4, 10, ''),
(47, 4, 11, ''),
(48, 5, 1, 'Address'),
(49, 5, 2, 'City'),
(50, 5, 3, 'IA'),
(51, 5, 13, '123-456-7890'),
(52, 5, 5, 'Zip'),
(53, 5, 14, 'Photographers'),
(54, 5, 7, ''),
(55, 5, 8, ''),
(57, 5, 10, ''),
(58, 5, 11, ''),
(59, 6, 1, 'Address'),
(60, 6, 2, 'City'),
(61, 6, 3, 'State'),
(62, 6, 13, '123-456-7890'),
(63, 6, 5, 'Zip'),
(64, 6, 14, 'Printers'),
(65, 6, 7, 'http://www.website.com/'),
(66, 6, 8, ''),
(68, 6, 10, ''),
(69, 6, 11, ''),
(70, 5, 12, 'someimage.jpg'),
(1075, 4, 16, ''),
(1076, 4, 17, ''),
(1177, 5, 16, ''),
(1178, 5, 17, '');

Open in new window

0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 15

Expert Comment

by:Ess Kay
ID: 39732160
post a sample of what you want to see
0
 
LVL 15

Assisted Solution

by:Ess Kay
Ess Kay earned 100 total points
ID: 39732182
the way it works now is a One-to-Many relation. So business 1 will have address, city, website..etc
same with business 2 and 3


you want just one field from jos_directory_field
then you add a WHERE statement

like WHERE jos_directory_enf.field_id = '10'

that willl give you the twitter page for all businesses

In the Select, add jos_directory_enf.field_value
0
 
LVL 1

Author Comment

by:bdhtechnology
ID: 39732270
I see what you mean as to get all values for a particular field like the twitter page.

What I am hoping to accomplish is to have one row with all values for a particular business, i.e. see the business name, city, state, zip, etc all on one row so I can export is easily.  Is that possible?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39732383
>>What I am hoping to accomplish is to have one row with all values for a particular business, i.e. see the business name, city, state, zip, etc all on one row so I can export is easily<<
So, given your sample data, what do you want those rows to look like?
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 400 total points
ID: 39732470
You would have to know the list of possible fields, and then join the table multiple times, once for each column:

SELECT 
  jde.id,
  address.field_value, 
  city.field_value,
  state.field_value
FROM
  jos_directory_entry AS jde
  LEFT JOIN jos_directory_enf AS address ON jde.id = address.entry_id AND address.field_id = 1
  LEFT JOIN jos_directory_enf AS city ON jde.id = city.entry_id AND city.field_id = 2
  LEFT JOIN jos_directory_enf AS state ON jde.id = state.entry_id AND state.field_id = 3
etc.
GROUP BY jde.id

Open in new window

You can join in the jos_directory_field table for each of those left joins too in order to avoid having to know the field_id numbers.

It might be simpler to just use the GROUP_CONCAT function to put them all together into a string, and then extract the pieces in your application.
0
 
LVL 1

Author Comment

by:bdhtechnology
ID: 39732963
SELECT 
  jde.id,
  jde.title as Business_Name, 
  address.field_value as address, 
  city.field_value as city,
  state.field_value as state,
  zip.field_value as zip,
  Fax.field_value as Fax,
  Toll_Free_Phone.field_value as Toll_Free_Phone,
  Phone.field_value as Phone,
  Facebook_Fan_Page.field_value as Facebook_Fan_Page,
  Website.field_value as Website,
  Twitter_Page.field_value as Twitter_Page,
  Description.field_value as Description,
  Image.field_value as Image,
  Categories.field_value as Categories
FROM
  jos_directory_entry AS jde
  LEFT JOIN jos_directory_enf AS address ON jde.id = address.entry_id AND 

address.field_id = 1
  LEFT JOIN jos_directory_enf AS city ON jde.id = city.entry_id AND city.field_id = 2
  LEFT JOIN jos_directory_enf AS state ON jde.id = state.entry_id AND state.field_id = 

3
  LEFT JOIN jos_directory_enf AS zip ON jde.id = zip.entry_id AND zip.field_id = 5
  LEFT JOIN jos_directory_enf AS Fax ON jde.id = Fax.entry_id AND Fax.field_id = 16
  LEFT JOIN jos_directory_enf AS Toll_Free_Phone ON jde.id = Toll_Free_Phone.entry_id 

AND Toll_Free_Phone.field_id = 17
  LEFT JOIN jos_directory_enf AS Phone ON jde.id = Phone.entry_id AND Phone.field_id = 

13
  LEFT JOIN jos_directory_enf AS Facebook_Fan_Page ON jde.id = 

Facebook_Fan_Page.entry_id AND Facebook_Fan_Page.field_id = 8
  LEFT JOIN jos_directory_enf AS Website ON jde.id = Website.entry_id AND 

Website.field_id = 7
  LEFT JOIN jos_directory_enf AS Twitter_Page ON jde.id = Twitter_Page.entry_id AND 

Twitter_Page.field_id = 10
  LEFT JOIN jos_directory_enf AS Description ON jde.id = Description.entry_id AND 

Description.field_id = 11
  LEFT JOIN jos_directory_enf AS Image ON jde.id = Image.entry_id AND Image.field_id = 

12
  LEFT JOIN jos_directory_enf AS Categories ON jde.id = Categories.entry_id AND 

Categories.field_id = 14

GROUP BY jde.id

Open in new window


That got me what I wanted, eventually.  Thank you!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question