How to join multiple results into one result

jpcaissy · #1 (**permalink**) 02-09-10, 11:41

Hi,

I've got this weird sql request i'm trying to make.
Let's say I have the following tables and rows:
Table a: (it actually have over 10 000 rows)

Code:

A.ID | A.Name
1    | JP
2    | Simon

Table b: (only have 2 rows)

Code:

B.ID | B.Code
1    | NYC
2    | SF

Table c: (Will always have table_a number of rows * table_b number of rows)

Code:

C.ID | C.TABLE_A_ID | C.TABLE_B_ID | C.QUANTITY
1    | 1            | 1            | 12
2    | 1            | 2            | 10
3    | 2            | 1            | 6
4    | 2            | 2            | 3

Is there any way to get a result set like this :

Code:

TABLE_A.name | TABLE_C.Quantity | TABLE_C.Quantity
JP           | 12               | 10
Simon        | 6                | 3

Here the first quantity would be for NYC and the second quantity would be for SF.

To make it simple, is there a way to implement a two-row result related to a single row INTO a single row resultset?
It is the first time I have to implement such a method. I am opened to review my model if necessary.

Thanks,
JP

r937 · #2 (**permalink**) 02-09-10, 12:14

Quote:

Originally Posted by jpcaissy

To make it simple, is there a way to implement a two-row result related to a single row INTO a single row resultset?

yes, there is

but i would rather not work with "dumbed down" table and column names

it's too confusing!!!

please, state your real table and column names

jpcaissy · #3 (**permalink**) 02-09-10, 12:20

Code:

--
-- Table structure for table `inventaire`
--

CREATE TABLE IF NOT EXISTS `inventaire` (
  `id` int(11) NOT NULL auto_increment,
  `product_option_id` int(11) default NULL,
  `entrepot` int(11) default NULL,
  `limite` int(11) default NULL,
  `qte_commande` int(11) default NULL,
  `qte_deja_commande` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `inventaire_FI_1` (`product_option_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3224 ;

-- --------------------------------------------------------

--
-- Table structure for table `inventaire_boutique`
--

CREATE TABLE IF NOT EXISTS `inventaire_boutique` (
  `id` int(11) NOT NULL auto_increment,
  `boutique_id` int(11) default NULL,
  `inventaire_id` int(11) default NULL,
  `quantite` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `inventaire_boutique_FI_1` (`boutique_id`),
  KEY `inventaire_boutique_FI_2` (`inventaire_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6447 ;

-- --------------------------------------------------------

--
-- Table structure for table `product_option`
--

CREATE TABLE IF NOT EXISTS `product_option` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` int(11) default NULL,
  `tvq` tinyint(4) default NULL,
  `name` varchar(64) collate utf8_unicode_ci NOT NULL,
  `code` varchar(16) collate utf8_unicode_ci NOT NULL,
  `description` text collate utf8_unicode_ci NOT NULL,
  `price` decimal(5,2) default NULL,
  `cout` decimal(5,2) default NULL,
  `isbn` varchar(15) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `product_option_I_1` (`code`),
  KEY `product_option_FI_1` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3224 ;

--
-- Table structure for table `inventaire`
--

CREATE TABLE IF NOT EXISTS `inventaire` (
  `id` int(11) NOT NULL auto_increment,
  `product_option_id` int(11) default NULL,
  `entrepot` int(11) default NULL,
  `limite` int(11) default NULL,
  `qte_commande` int(11) default NULL,
  `qte_deja_commande` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `inventaire_FI_1` (`product_option_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3224 ;


--
-- Constraints for dumped tables
--

--
-- Constraints for table `inventaire`
--
ALTER TABLE `inventaire`
  ADD CONSTRAINT `inventaire_FK_1` FOREIGN KEY (`product_option_id`) REFERENCES `product_option` (`id`);

--
-- Constraints for table `inventaire_boutique`
--
ALTER TABLE `inventaire_boutique`
  ADD CONSTRAINT `inventaire_boutique_FK_1` FOREIGN KEY (`boutique_id`) REFERENCES `boutique` (`id`),
  ADD CONSTRAINT `inventaire_boutique_FK_2` FOREIGN KEY (`inventaire_id`) REFERENCES `inventaire` (`id`);

--
-- Constraints for table `product_option`
--
ALTER TABLE `product_option`
  ADD CONSTRAINT `product_option_FK_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);

--
-- Constraints for table `inventaire`
--
ALTER TABLE `inventaire`
  ADD CONSTRAINT `inventaire_FK_1` FOREIGN KEY (`product_option_id`) REFERENCES `product_option` (`id`);

Table A represents PRODUCT_OPTION,
Table B is BOUTIQUE
Table C is INVENTAIRE_BOUTIQUE
I have added another table (INVENTAIRE) which is linked to PRODUCT_OPTION

The tricky part I can't figure out is how to get a result from INVENTAIRE that would show the quantity for each BOUTIQUE on the same line.

r937 · #4 (**permalink**) 02-09-10, 12:43

Quote:

Originally Posted by jpcaissy

The tricky part I can't figure out is how to get a result from INVENTAIRE that would show the quantity for each BOUTIQUE on the same line.

please note you gave inventaire twice, and forgot to include boutique

so which of these tables is involved in your query?

does your query actually return the right data?

can we see your query?

jpcaissy · #5 (**permalink**) 02-09-10, 12:52

Quote:

Originally Posted by r937

please note you gave inventaire twice, and forgot to include boutique

Oops right. Here it is :

Code:

--
-- Table structure for table `boutique`
--

CREATE TABLE IF NOT EXISTS `boutique` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `boutique`
--

INSERT INTO `boutique` (`id`, `name`) VALUES
(1, 'L&#233;vis'),
(2, 'Montr&#233;al');

The query I have so far :

Code:

SELECT concat(product.code, product_option.code) as code, concat_ws(\" - \", product.name, product_option.name) as article,
product_option.price as prix, product_option.cout as cout, inventaire.entrepot as entrepot, inventaire.limite as limite,
inventaire.qte_commande as qte_a_commande, inventaire.qte_deja_commande as qte_deja_commande, inventaire.entrepot * product_option.cout as cout_total
FROM product_option, product, inventaire
WHERE
product_option.product_id = product.id
AND inventaire.product_option_id = product_option.id

What I can't get done is to get the quantities for both boutiques (Lévis & Montréal) from inventaire_boutique from within this query. I just have no clue on how to accomplish that.

r937 · #6 (**permalink**) 02-09-10, 13:06

Quote:

Originally Posted by jpcaissy

What I can't get done is to get the quantities for both boutiques (Lévis & Montréal) from inventaire_boutique from within this query.

first thing you have to do is add the inventaire_boutique table to the query --

Code:

SELECT CONCAT(product.code, product_option.code) AS code
     , CONCAT_WS(\" - \", product.name, product_option.name) AS article
     , product_option.price AS prix
     , product_option.cout
     , inventaire.entrepot
     , inventaire.limite
     , inventaire.qte_commande AS qte_a_commande
     , inventaire.qte_deja_commande
     , inventaire.entrepot * product_option.cout AS cout_total
  FROM product_option
INNER
  JOIN product
    ON product.id = product_option.product_id 
INNER
  JOIN inventaire
    ON inventaire.product_option_id = product_option.id

what application language are you using for this? php?

Internet Services

Web Development

Digital Marketing

Consumer Tech