Solved

Database join help

Posted on 2013-12-20
9
382 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
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
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39732160
post a sample of what you want to see
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

19 Experts available now in Live!

Get 1:1 Help Now