Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Question Unanswered: How can you display SQL results inline?

    Hi,

    I have the following tables:

    Code:
    CREATE TABLE `tbl_packages` (
    	`pkg_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`pkg_name` VARCHAR(100) NOT NULL,
    	`pkg_desc` VARCHAR(1000) NOT NULL,
    	`pkg_price` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT '0.00',
    	`pkg_type` ENUM('Standalone','Combo','Subscription','Custom'),
    	PRIMARY KEY (`pkg_id`)
    )
    ENGINE=InnoDB
    
    CREATE TABLE `tbl_pkg_products` (
    	`pkg_id` INT(10) UNSIGNED NOT NULL,
    	`pd_id` INT(10) UNSIGNED NOT NULL,
    	`qty` INT(10) UNSIGNED NOT NULL DEFAULT '1', # number of credits
    	`duration` INT(10) UNSIGNED NOT NULL, # number of days the credits last
    	PRIMARY KEY (`pkg_id`, `pd_id`),
    	INDEX `pd_id` (`pd_id`),
    	FOREIGN KEY (`pd_id`) REFERENCES `tbl_products` (`pd_id`),
    	FOREIGN KEY (`pkg_id`) REFERENCES `tbl_packages` (`pkg_id`) ON DELETE CASCADE
    )
    ENGINE=InnoDB
    Code:
    some values in tbl_pkg_products
    
    pkg_id | pd_id | qty | duration
    -------------------------------
    5      |  1    | 5   | 365
    5      |  2    | 5   | 365
    
    
    some values in tbl_products
    
    pd_id | pd_name 
    ---------------
    1     |  std Jobs    
    2     |  prem Job
    3     |  featured Jobs
    A package can consists of several products lets say for example My pkg consists of 5 std jobs and 5 prem jobs. The linking tbl_pkg_products will also hold some attribute of a product such as credit qty and duration.

    How do you write an SQL statement to show a package and lets say 3 package products in an inline fashion as follows:

    Code:
    So it would appear as:
    
    pkg id | pkg name | std jobs | prem jobs | featured jobs
    --------------------------------------------------------
    5      |  My Pkg  |    5     |      5    |      0
    --------------------------------------------------------
    the following SQL will just display it as a list and omit featured job since their is no value for it in the linking table. How can you display it so it appears inline as above?

    Code:
    SELECT pkg.pkg_id, pkg.pkg_name, pd.pd_name, pkg_pd.qty 
    FROM tbl_packages pkg
    INNER JOIN tbl_pkg_products pkg_pd ON pkg.pkg_id = pkg_pd.pkg_id
    INNER JOIN tbl_products pd ON pkg_pd.pd_id = pd.pd_id
    WHERE pkg.pkg_id = 5
    Any help appreciated.

  2. #2
    Join Date
    Mar 2006
    Posts
    56
    Hello,

    You don't need tbl_products to get what you want; all necessary information is already in the first table.

    This is untested....
    Code:
    SELECT pkg_pd.pkg_id
         , SUM(CASE WHEN pkg_pd.pd_id = '1' THEN pkg_pd.qty ELSE 0 END) as "std jobs"
         , SUM(CASE WHEN pkg_pd.pd_id = '2' THEN pkg_pd.qty ELSE 0 END) as "prem jobs"
         , SUM(CASE WHEN pkg_pd.pd_id = '3' THEN pkg_pd.qty ELSE 0 END) as "featured jobs"
      FROM tbl_pkg_products pkg_pd INNER JOIN tbl_packages pkg
        ON pkg_pd.pkg_id  = pkg.pkg_id
     WHERE pkg.pkg_name = 'My Pkg'
     GROUP BY pkg_pd.pkg_id;
    Alas, I cannot get around to including pkg_name because it is not the argument of the GROUP BY clause.
    Last edited by Ikviens; 04-03-10 at 11:18. Reason: edit, edit, edit.

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    OK i've managed to do this using subqueries within the select statement:

    Code:
    SELECT DISTINCT pkg.pkg_id
                   ,pkg.pkg_name
    	       ,(SELECT pkg_pd.qty 
                       FROM tbl_packages pkg
    	     INNER JOIN tbl_pkg_products pkg_pd ON pkg.pkg_id = pkg_pd.pkg_id
    	     INNER JOIN tbl_products pd ON pkg_pd.pd_id = pd.pd_id
    		  WHERE pkg.pkg_id = 5 AND pd.pd_id = 1) as std_job
    	       ,(SELECT pkg_pd.qty 
    		   FROM tbl_packages pkg
    	     INNER JOIN tbl_pkg_products pkg_pd ON pkg.pkg_id = pkg_pd.pkg_id
    	     INNER JOIN tbl_products pd ON pkg_pd.pd_id = pd.pd_id
    	          WHERE pkg.pkg_id = 5 AND pd.pd_id = 2) as prem_job
    	       ,(SELECT pkg_pd.qty 
    		   FROM tbl_packages pkg
    	     INNER JOIN tbl_pkg_products pkg_pd ON pkg.pkg_id = pkg_pd.pkg_id
    	     INNER JOIN tbl_products pd ON pkg_pd.pd_id = pd.pd_id
    	          WHERE pkg.pkg_id = 5 AND pd.pd_id = 3) as featured_job 
          FROM tbl_packages pkg
    INNER JOIN tbl_pkg_products pkg_pd ON pkg.pkg_id = pkg_pd.pkg_id
    INNER JOIN tbl_products pd ON pkg_pd.pd_id = pd.pd_id
         WHERE pkg.pkg_id = 5
    But is this possible using an inline view? If so any help would be appreciated

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Ikviens View Post
    Hello,

    You don't need tbl_products to get what you want; all necessary information is already in the first table.

    This is untested....
    Code:
    SELECT pkg_pd.pkg_id
         , SUM(CASE WHEN pkg_pd.pd_id = '1' THEN pkg_pd.qty ELSE 0 END) as "std jobs"
         , SUM(CASE WHEN pkg_pd.pd_id = '2' THEN pkg_pd.qty ELSE 0 END) as "prem jobs"
         , SUM(CASE WHEN pkg_pd.pd_id = '3' THEN pkg_pd.qty ELSE 0 END) as "featured jobs"
      FROM tbl_pkg_products pkg_pd INNER JOIN tbl_packages pkg
        ON pkg_pd.pkg_id  = pkg.pkg_id
     WHERE pkg.pkg_name = 'My Pkg'
     GROUP BY pkg_pd.pkg_id;
    Alas, I cannot get around to including pkg_name because it is not the argument of the GROUP BY clause.
    Hi,

    thanks for you response. I've tried the above but seem to be getting an SQL systax error to use near ') as "std jobs"

    Any ideas?

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Ok I figured the CASE statement out and why the systax error - the END was missing from the CASE statement. But this seems to work a charm!

    Thanks for your help Ikviens. Much appreciated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ikviens View Post
    Alas, I cannot get around to including pkg_name because it is not the argument of the GROUP BY clause.
    this is mysql, it doesn't have to be

    observe...
    Code:
    SELECT tbl_packages.pkg_id
         , tbl_packages.pkg_name
         , SUM(CASE WHEN tbl_pkg_products.pd_id = 1 
                    THEN tbl_pkg_products.qty END) AS 'Std jobs'
         , SUM(CASE WHEN tbl_pkg_products.pd_id = 2 
                    THEN tbl_pkg_products.qty END) AS 'Prem jobs'
         , SUM(CASE WHEN tbl_pkg_products.pd_id = 3
                    THEN tbl_pkg_products.qty END) AS 'Featured jobs'
      FROM tbl_packages  
    INNER 
      JOIN tbl_pkg_products
        ON tbl_pkg_products.pkg_id = tbl_packages.pkg_id
     GROUP 
        BY tbl_packages.pkg_id
    see Debunking GROUP BY myths

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2006
    Posts
    56
    Sorry, ozzii, I overlooked the END until my final edit. It goes to show how far I can get without a running database.

    If you want to display "My Pkg" in your result set, use pkg.pkg_name for the aggregate key. Under the ON condition, name and id are in one-to-one relationship.

    [If you really really wanna include both, and you don't care about execution cost, use a concatenation of name and id for the GROUP BY.]


    Hi, Rudy.
    I don't know if I should be happy or angry with the way GROUP BY behaves in MySQL. My pet peeve is that MySQL allows an attribute in the SELECT clause referenced in GROUP BY with an alias....!
    Last edited by Ikviens; 04-03-10 at 11:59.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ikviens View Post
    I don't know if I should be happy or angry with the way GROUP BY behaves in MySQL.
    don't worry, be happy

    did you read that article i linked to? i know it's long, but it is very worthwhile

    read it all, please

    i think you might even agree that it's a good feature
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2006
    Posts
    56
    Rudy, I've read it and I have a question.

    Am I selecting functionally dependent values in the following SQL without the GROUP BY?

    For a table as below:
    Code:
    +--------+-------------+----------+
    | word   | syllable_id | syllable |
    +--------+-------------+----------+
    | awaken |           1 | ə       | 
    | awaken |           2 | ˈweɪ   | 
    | awaken |           3 | kən     | 
    +--------+-------------+----------+
    3 rows in set (0.00 sec)
    An SQL with MIN() or MAX() retrieves a single row marked in red or blue


    Code:
    SELECT word, MIN|MAX(syllable_id), syllable
      FROM dictionary;

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are going to run a query with a mix of both aggregate and non-aggregate expressions in the SELECT clause, and then omit the GROUP BY clause entirely, all bets are off

    however, if you were to run this --
    Code:
    SELECT word
         , MIN(syllable_id)
         , syllable
      FROM dictionary
    GROUP
        BY word
    then the value of syllable would be indeterminate, and would ~not~ necessarily correspond to the id chosen as the min
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2006
    Posts
    56
    All bets are off? Yeah, after doing this;
    Code:
    SET sql_mode := concat('ONLY_FULL_GROUP_BY,',@@sql_mode);
    DB returns an error for my query. So it must have been that MySQL extension was at work.

    Anyway, it is hard to understand how MySQL returns the result set as it does. This extension is like smashing the whole window for ventilation when opening it a few centimetres would let enough fresh air in.

  12. #12
    Join Date
    Mar 2007
    Posts
    212
    Hi,

    I've been playing around with the SQL a little bit but when i try to run the following using a CASE statement I keep getting an error message 'unknown column std_jobs'. Any idea why that is?

    Code:
    set @jobtype = 1;
    
    SELECT CASE WHEN @jobtype = 1 THEN SUM(std_jobs) 
    	    WHEN @jobtype = 2 THEN SUM(prem_jobs) 
    	    WHEN @jobtype = 3 THEN SUM(featured_jobs) END 
         	FROM
               (SELECT CASE WHEN @jobtype = 1 THEN c_acct.std_jobs
    	  		WHEN @jobtype = 2 THEN c_acct.prem_jobs
    	  		WHEN @jobtype = 3 THEN c_acct.featured_jobs END 
    	    FROM tbl_company_acct c_acct
    		INNER JOIN tbl_recruiter_acct r ON r_acct.company_id = c_acct.company_id
    	        WHERE r_acct.recruiter_id = 1
    	    UNION ALL
        	    SELECT CASE WHEN @jobtype = 1 THEN r_acct.std_jobs
    	  		WHEN @jobtype = 2 THEN r_acct.prem_jobs
    	  		WHEN @jobtype = 3 THEN r_acct.featured_jobs END 
    	    FROM tbl_recruiter_acct r_acct
    	         WHERE r_acct.recruiter_id = 1) x;

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    Any idea why that is?
    yup, it's because the subquery doesn't have a column with that name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CASE WHEN @jobtype = 1 THEN SUM(std_jobs)  
                WHEN @jobtype = 2 THEN SUM(prem_jobs) 
                WHEN @jobtype = 3 THEN SUM(featured_jobs) END 
             AS daResult
      FROM ( SELECT c_acct.std_jobs
                  , c_acct.prem_jobs
                  , c_acct.featured_jobs
               FROM tbl_company_acct c_acct
             INNER 
               JOIN tbl_recruiter_acct r 
                 ON r_acct.company_id = c_acct.company_id
              WHERE r_acct.recruiter_id = 1
             UNION ALL
             SELECT r_acct.std_jobs
                  , r_acct.prem_jobs
                  , r_acct.featured_jobs
               FROM tbl_recruiter_acct r_acct
              WHERE r_acct.recruiter_id = 1 ) x
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    yup, it's because the subquery doesn't have a column with that name


    Your solution works! But just out of curiosity why doesnt the subquery have column? Since the variable @jobtype is set to 1, when the CASE statement within the subquery evaluates, it would select c_acct.std_jobs? Is this not the same name but with a table prefix?

Posting Permissions

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