Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

difficulty building mysql query to get the result Im looking for

Posted on 2012-09-16
22
Medium Priority
?
375 Views
Last Modified: 2012-09-16
My tables: companies, paymentTypes, paymentProcessors, companiesHasPayProcessors

I have a select box where you choose the payment type
Cash
Cheque
Paypal
Credit Card

in this particular case the companiesHasPayProcessors has the records meaning works with 3 different processors, so basically I should have:
Cash
Cheque
Paypal
Credit Card (Internet Secure)
Credit Card (Moneris)
Credit Card (Desjardins Merchant Services)

My current query is:
SELECT pt.PaymentType, pt.display, p.processorName
FROM paymentTypes AS pt,
processors AS p                  
INNER JOIN companyProcessorsAffiliation AS cpa ON p.processorId = cpa.processorId
WHERE cpa.companyId = 23 ORDER BY pt.PaymentType

attached image is what it gives me:

my exact mySQL structure:
--
-- Table structure for table `companies`
--

CREATE TABLE IF NOT EXISTS `companies` (
  `companyId` int(9) unsigned NOT NULL auto_increment,
  `companyLoginId` varchar(9) NOT NULL,
  `customerSince` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `primaryContactUserId` int(9) NOT NULL,
  `companyName` varchar(50) NOT NULL,
  `companyWebsite` varchar(75) NOT NULL,
  `companyStatusId` int(2) NOT NULL,
  PRIMARY KEY  (`companyId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

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

--
-- Table structure for table `companyProcessorsAffiliation`
--

CREATE TABLE IF NOT EXISTS `companyProcessorsAffiliation` (
  `cpaId` int(9) unsigned NOT NULL auto_increment,
  `companyId` int(9) NOT NULL,
  `processorId` int(4) NOT NULL,
  PRIMARY KEY  (`cpaId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

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

--
-- Table structure for table `paymentTypes`
--

CREATE TABLE IF NOT EXISTS `paymentTypes` (
  `paymentTypeId` int(2) unsigned NOT NULL auto_increment,
  `display` varchar(25) NOT NULL,
  `PaymentType` varchar(25) NOT NULL,
  PRIMARY KEY  (`paymentTypeId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

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

--
-- Table structure for table `processors`
--

CREATE TABLE IF NOT EXISTS `processors` (
  `processorId` int(4) unsigned NOT NULL auto_increment,
  `paymentTypeId` int(2) NOT NULL,
  `processorName` varchar(35) NOT NULL,
  PRIMARY KEY  (`processorId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Open in new window

Please help, Thanks in advance
mySQL-result.jpg
0
Comment
Question by:prowebinteractiveinc
  • 11
  • 11
22 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38403272
You have not specified join criteria between paymentTypes and processors ... based on the table definitions I would have expected:
SELECT pt.PaymentType, pt.display, p.processorName
FROM paymentTypes AS pt
INNER JOIN processors AS p ON pt.paymentTypeId = p.paymentTypeId
INNER JOIN companyProcessorsAffiliation AS cpa ON p.processorId = cpa.processorId
WHERE cpa.companyId = 23 ORDER BY pt.PaymentType

Open in new window

is this the problem or is there something else you needed?
0
 

Author Comment

by:prowebinteractiveinc
ID: 38403295
as soon as I put any type of join I ssem to get
Credit Card (Internet Secure)
Credit Card (Moneris)
Credit Card (Desjardins Merchant Services)

Instead of
Cash
Cheque
Paypal
Credit Card (Internet Secure)
Credit Card (Moneris)
Credit Card (Desjardins Merchant Services)

I need the rest of the results in the paymentTypes table
0
 

Author Comment

by:prowebinteractiveinc
ID: 38403619
anybody ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:lwadwell
ID: 38403845
Sorry ... my bed distracted me.

You seem to have asked another question the same as this one:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_27866771.html

But in that one you have added the data - which helps.

The data shows the that the company 23 has only 3 rows in `companyProcessorsAffiliation` to link them to `processors` (processorId 1, 2 & 3).  These three `processors` link to the 3 records in `paymentTypes` for Paypal, Elavon (Internet Secure) & Moneris respectively.

In fact - there is NO link from `processors` to `paymentTypes` for Cash or Cheque at all.

So I do not see how to produce the result you want from the data provided.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38403870
I have tried to link them and the cash, cheque, paypal disappears ?
0
 

Author Comment

by:prowebinteractiveinc
ID: 38403880
my last query I tried was

SELECT p.processorName, pt.paymentType, pt.display
FROM paymentTypes AS pt
LEFT JOIN processors AS p ON
pt.paymentTypeId=p.paymentTypeId
INNER JOIN companyProcessorsAffiliation AS cpa
ON p.processorId = cpa.processorId
WHERE cpa.companyId = 23
ORDER BY pt.PaymentType

which gives me:
Credit Card (Internet Secure)
Credit Card (Moneris)
Credit Card (Desjardins Merchant Services)

missing from paymentTypes table:
Cash
Cheque
Paypal
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38403889
The only way, based on how I understand the data structures, is to add rows into the `companyProcessorsAffiliation` and `processors` tables.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38403895
do you know of a better way as for the structure, please tell me how you would do it
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38403915
No, the structure is OK.  I do now know enough (anything actually) about you business to think of a better one.  But based on it, I would think that it should be populated like below to product the result you want:

INSERT INTO `companies` (`companyId`, `customerSince`, `primaryContactUserId`, `companyName`) VALUES
(23, '2012-09-13 14:29:44', 7, 'PROWEBINTERACTIVE INC');

INSERT INTO `companyProcessorsAffiliation` (`cpaId`, `companyId`, `processorId`) VALUES
(1, 23, 1),
(2, 23, 2),
(3, 23, 3),
(3, 23, 4),
(3, 23, 5),
(3, 23, 6);

INSERT INTO `processors` (`processorId`, `paymentTypeId`, `processorName`) VALUES
(1, 3, 'Paypal'),
(2, 4, 'Elavon (Internet Secure)'),
(3, 4, 'Moneris'),
(4, 4, 'Desjardins'),
(5, 1, 'Cash'),
(6, 2, 'Cheque');

INSERT INTO `paymentTypes` (`paymentTypeId`, `display`, `PaymentType`) VALUES
(1, 'ccgroupbox', 'Cash'),
(2, 'ccgroupbox', 'Cheque'),
(3, 'ccgroupbox', 'Paypal'),
(4, 'ccgroupbox', 'Credit Card');
0
 

Author Comment

by:prowebinteractiveinc
ID: 38404015
so no query can get what I want with the current structure as is ?
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404031
The structure ... i.e. table definitions and relationships ... is fine.  It can produce the results you want - with the right data stored in the tables.  Data is not structure.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38404047
right. the part Im not liking is cash and cheque and maybe any future other types I may add are not processors...
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404054
what is 'processors' by the definition of your data model?
Again ... I state I do not know your business, so it is very hard for me to guess the meanings.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404058
What is company for example ... is it the organisation that makes payments via the defined methods ... or can receive payments via those methods ... or handles the payments via those methods for others.   By the structure - I could interpret any of those 3 options.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38404074
processors are companies that process credit card payments
so I have a table of just those. then
the payment type, when a payment is recorded, the system requires to select the payment type, it is unusual that a company has more then one merchant account but its requested so I know to make it available so if lets say I use Elavon, Moneris, and Desjardins to process my credit cards. I want to give the choice in the select box

so again based on a company dealing with x-amount of payment processors the selectbox should show
Cash
Cheque
Paypal => should work the same as credit cards actually, Im going to change as processor
Credit Card (Elavon)
Credit Card (Moneris)
Credit Card (desjardins)

so now they make their selection (if its credit card, not cash or cheque) they can enter the credit card info and it will be processed with the selected merchant account.

Thats why I have built it as is... Im sure there must be some query to get the info I want...

Thanks
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404085
Will cash and cheque 'always' be available choices for every company?
0
 

Author Comment

by:prowebinteractiveinc
ID: 38404089
yes
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38404107
OK then ... I will suggest a small change to the data model.

Add a new column to `paymentTypes` to indicate whether it has a processor or not, e.g. hasprocessor (smallint is fine, values 0 and 1).  The idea behind this is so that the SQL can be certain whether to include as a always available or whether it must be linked via a Processor.
SQL would then be:
SELECT pt.PaymentType, pt.display, p.processorName
FROM paymentTypes AS pt
INNER JOIN processors AS p ON pt.paymentTypeId = p.paymentTypeId
INNER JOIN companyProcessorsAffiliation AS cpa ON p.processorId = cpa.processorId
WHERE cpa.companyId = 23 
AND hasprocessor = 1
UNION ALL
SELECT pt.PaymentType, pt.display, null
FROM paymentTypes AS pt
WHERE hasprocessor = 0
ORDER BY 1

Open in new window

Database script:
--
-- Table structure for table `companies`
--
DROP TABLE IF EXISTS `companies`;
CREATE TABLE IF NOT EXISTS `companies` (
  `companyId` int(9) unsigned NOT NULL auto_increment,
  `customerSince` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `primaryContactUserId` int(9) NOT NULL,
  `companyName` varchar(50) NOT NULL,
  PRIMARY KEY  (`companyId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

--
-- Dumping data for table `companies`
--
INSERT INTO `companies` (`companyId`, `customerSince`, `primaryContactUserId`, `companyName`) VALUES
(23, '2012-09-13 14:29:44', 7, 'PROWEBINTERACTIVE INC');

--
-- Table structure for table `companyProcessorsAffiliation`
--
DROP TABLE IF EXISTS `companyProcessorsAffiliation`;
CREATE TABLE IF NOT EXISTS `companyProcessorsAffiliation` (
  `cpaId` int(9) unsigned NOT NULL auto_increment,
  `companyId` int(9) NOT NULL,
  `processorId` int(4) NOT NULL,
  PRIMARY KEY  (`cpaId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `companyProcessorsAffiliation`
--
INSERT INTO `companyProcessorsAffiliation` (`cpaId`, `companyId`, `processorId`) VALUES
(1, 23, 2),
(2, 23, 1),
(3, 23, 3),
(4, 23, 4);

--
-- Table structure for table `paymentTypes`
--
DROP TABLE IF EXISTS `paymentTypes`;
CREATE TABLE IF NOT EXISTS `paymentTypes` (
  `paymentTypeId` int(2) unsigned NOT NULL auto_increment,
  `display` varchar(25) NOT NULL,
  `PaymentType` varchar(25) NOT NULL,
  `hasProcessor` tinyint,
  PRIMARY KEY  (`paymentTypeId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `paymentTypes`
--
INSERT INTO `paymentTypes` (`paymentTypeId`, `display`, `PaymentType`, `hasProcessor`) VALUES
(1, 'ccgroupbox', 'Cash',0),
(2, 'ccgroupbox', 'Cheque',0),
(3, 'ccgroupbox', 'Paypal',1),
(4, 'ccgroupbox', 'Credit Card',1);

--
-- Table structure for table `processors`
--
DROP TABLE IF EXISTS `processors`;
CREATE TABLE IF NOT EXISTS `processors` (
  `processorId` int(4) unsigned NOT NULL auto_increment,
  `paymentTypeId` int(2) NOT NULL,
  `processorName` varchar(35) NOT NULL,
  PRIMARY KEY  (`processorId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `processors`
--

INSERT INTO `processors` (`processorId`, `paymentTypeId`, `processorName`) VALUES
(1, 3, 'Paypal'),
(2, 4, 'Elavon (Internet Secure)'),
(3, 4, 'Moneris'),
(4, 4, 'Desjardins');

Open in new window

0
 

Author Comment

by:prowebinteractiveinc
ID: 38404119
paymentTypes could have one to many.. if anything maybe an extra field would need to be processors ?
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404122
>>paymentTypes could have one to many
one to many what?
it is an existence flag ... it is not a foreign key.
0
 

Author Closing Comment

by:prowebinteractiveinc
ID: 38404149
A1 you deserve the extra 500 so just reply to the other one with the link to this solution so I can give you the points. thanks
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404163
That is OK.  Please delete the other one ... I do not need the points, nor is it why I try to help on EE.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question