Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

need some help with this query

So i have two tables one is a list of valid promo code and the other is a list of promo codes used
I am trying to find out if the promo code is valid and the count of the uses of this promo code with the key of uid.

if will need to be an outer join to promo_history
The two tables are below.

So my where clause will be where promo_codes.pcode = 'somepromocode' and if the promocode is found if would like to have a count of how many times it is in the promo_history based on uid

I hope this makes sense. I am trying to do this with a single query


CREATE TABLE `promo_codes` (
      `pcid` INT(8) NOT NULL,
      `pcode` VARCHAR(25) NOT NULL COLLATE 'utf8_unicode_ci',
      `credit` DECIMAL(5,2) NULL DEFAULT '0.00',
      `begin` DATE NULL DEFAULT NULL,
      `end` DATE NULL DEFAULT NULL,
      `allowPerUID` TINYINT(3) UNSIGNED NULL DEFAULT '0',
      PRIMARY KEY (`pcid`),
      UNIQUE INDEX `pcid` (`pcid`)
)



CREATE TABLE `promo_history` (
      `phid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `uid` INT(10) UNSIGNED NULL DEFAULT NULL,
      `pcid` INT(10) UNSIGNED NULL DEFAULT NULL,
      `pdate` TIMESTAMP NOT NULL DEFAULT ,
      PRIMARY KEY (`phid`),
      UNIQUE INDEX `phid` (`phid`),
)
0
paries
Asked:
paries
  • 2
1 Solution
 
tlaytonCommented:
Try something like this:  
SELECT 
	pc.pcode AS 'Promo Code', 
	COUNT(ph.phid) AS 'Count in History' 
FROM 
	promo_codes pc 
	LEFT OUTER JOIN promo_history ph ON
		pc.pcid = ph.pcid
WHERE
	pc.pcode = 'YourCodeHere'
GROUP BY 
	pc.pcode HAVING COUNT(ph.phid) > 0

Open in new window

0
 
tlaytonCommented:
Oops, sorry thought you were asking about SQL Server, don't really know MySQL too well...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now