How can you display SQL results inline?

ozzii · #1 (**permalink**) 04-03-10, 08:23

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.

Ikviens · #2 (**permalink**) 04-03-10, 10:06

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.

ozzii · #3 (**permalink**) 04-03-10, 10:11

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

ozzii · #4 (**permalink**) 04-03-10, 10:24

Quote:

Originally Posted by Ikviens

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?

ozzii · #5 (**permalink**) 04-03-10, 10:30

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.

r937 · #6 (**permalink**) 04-03-10, 10:43

Quote:

Originally Posted by Ikviens

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

Ikviens · #7 (**permalink**) 04-03-10, 10:53

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....!

r937 · #8 (**permalink**) 04-03-10, 11:07

Quote:

Originally Posted by Ikviens

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

Ikviens · #9 (**permalink**) 04-05-10, 10:50

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;

r937 · #10 (**permalink**) 04-05-10, 10:57

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

Ikviens · #11 (**permalink**) 04-06-10, 09:29

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.

ozzii · #12 (**permalink**) 04-18-10, 11:35

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;

r937 · #13 (**permalink**) 04-18-10, 15:06

Quote:

Originally Posted by ozzii

Any idea why that is?

yup, it's because the subquery doesn't have a column with that name

r937 · #14 (**permalink**) 04-18-10, 15:09

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

ozzii · #15 (**permalink**) 04-18-10, 16:36

Quote:

Originally Posted by r937

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech