• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Database join help

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
bdhtechnology
Asked:
bdhtechnology
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Ess KayEntrapenuerCommented:
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
 
awking00Commented:
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
 
bdhtechnologyAuthor Commented:
@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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Ess KayEntrapenuerCommented:
post a sample of what you want to see
0
 
Ess KayEntrapenuerCommented:
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
 
bdhtechnologyAuthor Commented:
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
 
awking00Commented:
>>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
 
snoyes_jwCommented:
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
 
bdhtechnologyAuthor Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now