Solved

Database join help

Posted on 2013-12-20
9
377 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:Ess Kay
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
@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
 
LVL 15

Expert Comment

by:Ess Kay
Comment Utility
post a sample of what you want to see
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 15

Assisted Solution

by:Ess Kay
Ess Kay earned 100 total points
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now