CODEWITHSUNDEEP
phpmysqlsql
Walter White
Walter White

Reputation: 369

How to select from three tables in one query

I have following database structure:

db_config_list id | id_config | type

db_config id | id_list | name | value

db_config_lang id | id_list | id_lang | name |value

In one mySQL query I would like to select things from db_config and db_config_lang where db_config.id_list = db_config_list.id and db_config_lang.id_list = db_config_list.id

I've tried:

$sql = 'SELECT * FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config.'` c
        ON (cl.id = c.id_list)
        LEFT JOIN `'._DB_PREFIX_.$this->db_config_lang.'` cll
        ON (cl.id = cll.id_list)
        WHERE cl.id_config = '.$this->c_id.'
        AND cl.type = "'.$this->c_type.'"
        AND cll.id_lang = "'.$this->default_lang.'"';

But It doesn't work proper. It's actually works when I do this In two different queries:

$sql = 'SELECT * FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config.'` c
        ON (cl.id = c.id_list)
        WHERE cl.id_config = '.$this->c_id.'
        AND cl.type = "'.$this->c_type.'"';

$sql = 'SELECT * FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config_lang.'` cll
        ON (cl.id = cll.id_list)
        WHERE cll.id_lang = "'.$this->default_lang.'"';

But I would like to do this in one query. Is this possible ?

// EDIT

I think I was tried to achieve something impossible. So I chose different solution

$sql = 'SELECT name,value FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config.'` c
        ON (cl.id = c.id_list)
        WHERE cl.id_config = '.$this->c_id.'
        AND cl.type = "'.$this->c_type.'"';

$sql2 = 'SELECT name,value FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config_lang.'` cll
        ON (cl.id = cll.id_list)
        WHERE cll.id_lang = "'.$this->default_lang.'"';

$query1 =  Db::getInstance()->executeS($sql);
$query2 =  Db::getInstance()->executeS($sql2);

$query = array_merge($query1, $query2);

// EDIT 2

Here is the code for sqlfiddle.com

Create code:

CREATE TABLE IF NOT EXISTS `db_config_list` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_config` int(10) unsigned NOT NULL,
    `type` varchar(10) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `db_config` (
    `id_list` int(10) unsigned NOT NULL,
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `value` text NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8;


CREATE TABLE IF NOT EXISTS `db_config_lang` (
    `id_list` int(10) unsigned NOT NULL,
    `id_lang` int(10) unsigned NOT NULL,
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `value` text NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8;

ALTER TABLE `db_config` ADD INDEX(`id_list`);

ALTER TABLE `db_config_lang` ADD INDEX(`id_list`);

ALTER TABLE `db_config` ADD FOREIGN KEY (`id_list`) REFERENCES `db_config_list`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `db_config_lang` ADD FOREIGN KEY (`id_list`) REFERENCES `db_config_list`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

INSERT INTO `db_config_list` (`id`, `id_config`, `type`) VALUES
(1, 1, 'global');

INSERT INTO `db_config` (`id_list`, `id`, `name`, `value`) VALUES
(1, 1, 'font_family', ''),
(1, 2, 'first_main_color', '#19BCE7'),
(1, 3, 'use_background_image', '1'),
(1, 4, 'background_color', '#F60'),
(1, 5, 'animated_tabs_carousel', '1'),
(1, 6, 'boxed_layout', '1'),
(1, 7, 'loading_animation', '1'),
(1, 8, 'smooth_scroll', '1'),
(1, 9, 'responsiveness', '1'),
(1, 10, 'sticky_header', '1');

INSERT INTO `db_config_lang` (`id_list`, `id_lang`, `id`, `name`, `value`) VALUES
(1, 1, 1, 'center_column_content', 'boo foo'),
(1, 1, 2, 'bottom_column_content', 'boo foo'),
(1, 2, 3, 'center_column_content', 'boo foo'),
(1, 2, 4, 'bottom_column_content', 'boo foo'),
(1, 3, 5, 'center_column_content', 'boo foo'),
(1, 3, 6, 'bottom_column_content', 'boo foo'),
(1, 4, 7, 'center_column_content', 'boo foo'),
(1, 4, 8, 'bottom_column_content', 'boo foo');

Select code:

SELECT name,value FROM `db_config_list` cl
LEFT JOIN `db_config` c
ON (cl.id = c.id_list)
WHERE cl.id_config = 1
AND cl.type = "global";

SELECT name,value FROM `db_config_list` cl
LEFT JOIN `db_config_lang` cll
ON (cl.id = cll.id_list)
WHERE cll.id_lang = 1
AND cl.id_config = 1
AND cl.type = "global";

Upvotes: 2

Views: 102

Answers (1)

Mikel Bitson
Mikel Bitson

Reputation: 3627

I'm thinking that what is happening is more likely just that the select is a * attribute- which will only select one value for each column name, regardless of how many tables are joined. If you can post a SQL Fiddle for this setup I can help you modify the query. Essentially, I believe you're looking to do something like :

$sql = 'SELECT *, c.name AS config_name, cll.name as config_lang_name
        FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config.'` c
        ON (cl.id = c.id_list)
        LEFT JOIN `'._DB_PREFIX_.$this->db_config_lang.'` cll
        ON (cl.id = cll.id_list)
        WHERE cl.id_config = '.$this->c_id.'
        AND cl.type = "'.$this->c_type.'"
        AND cll.id_lang = "'.$this->default_lang.'"';

EDIT: Doing two separate queries and then combining the result sets will be far slower than adjusting the query to return the desired result set. Is this query closer to what you wanted? (The above was just an example, now that you've shown your end goal I believe this should work.)

$sql = 'SELECT cl.name AS config_list_name, cl.value AS config_list_value,
        cll.name AS config_lang_name, cll.value AS config_lang_value
        FROM `'._DB_PREFIX_.$this->db_config_list.'` cl
        LEFT JOIN `'._DB_PREFIX_.$this->db_config.'` c
        ON (cl.id = c.id_list)
        LEFT JOIN `'._DB_PREFIX_.$this->db_config_lang.'` cll
        ON (cl.id = cll.id_list)
        WHERE cl.id_config = '.$this->c_id.'
        AND cl.type = "'.$this->c_type.'"
        AND cll.id_lang = "'.$this->default_lang.'"';

EDIT 2: It looks like you're using a framework for this query. I would really recommend utilizing some parameter binding instead of putting the values directly into the query.

Upvotes: 3

Related Questions