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
Solved

Database join help

Posted on 2013-12-20
9
389 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​

861 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