Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: How to join multiple results into one result

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jpcaissy View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jpcaissy View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    Quote Originally Posted by r937 View Post
    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évis'),
    (2, 'Montré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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jpcaissy View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •