Solved

mySQL filtering information within same table

Posted on 2009-07-08
3
840 Views
Last Modified: 2012-05-07
I'm trying to do a search from multiple checkboxes that returns the ID's of the chosen checkboxes.

I want to do a search for all of the Partners available within the Program of 'Lighting Fixtures and Controls',  and Partner Type of 'Energy Auditors'. (Should be around 19 rows.)

(PROGRAMS.`xrpo_Type` = 2) = OptionsTypeID in the OptionsType Table, It's a Program
(PROGRAMS.`xrpo_TypeID` = (2)) = ProgramID, Name: 'Lighting'

(PARTNERS.`xrpo_Type` = 3) = OptionsTypeID in the OptionsType Table, Name: Partner Types
(PARTNERS.`xrpo_TypeID` = (13)) = PartnerTypeID, Name: 'Energy Auditors'

Heres my example query. This currently pulls back everything all 50 rows within the EEP_Partners table. My thoughts are kind of jumbled with this, if you need any further information, please let me know.

---------------------------------
SELECT

DISTINCT `pa_ID` as ID,
`pa_CompanyName` as Name

FROM `EEP_Partners`

INNER JOIN `xr_partners_options` PROGRAMS
INNER JOIN `xr_partners_options` PARTNERS

WHERE

PROGRAMS.`xrpo_Type` = 2 AND PROGRAMS.`xrpo_TypeID` = (2) AND
PARTNERS.`xrpo_Type` = 3 AND PARTNERS.`xrpo_TypeID` = (13) AND

`pa_DeleteFlag` = '0'

ORDER BY `Name` ASC
---------------------------------

Thanks in advance!! Full mySQL scripts are included below.
-- phpMyAdmin SQL Dump

-- version 2.11.9.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jul 08, 2009 at 02:36 PM

-- Server version: 4.1.22

-- PHP Version: 5.2.6
 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
 

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

--

-- Table structure for table `EEP_OptionsType`

--
 

CREATE TABLE IF NOT EXISTS `EEP_OptionsType` (

  `opt_ID` int(11) NOT NULL auto_increment,

  `opt_Name` varchar(100) NOT NULL default '',

  PRIMARY KEY  (`opt_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 

--

-- Dumping data for table `EEP_OptionsType`

--
 

INSERT INTO `EEP_OptionsType` (`opt_ID`, `opt_Name`) VALUES

(1, 'Primary Customers'),

(2, 'Programs'),

(3, 'Partner Type');
 

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

--

-- Table structure for table `EEP_Partners`

--
 

CREATE TABLE IF NOT EXISTS `EEP_Partners` (

  `pa_ID` int(11) NOT NULL auto_increment,

  `pa_CompanyName` varchar(150) NOT NULL default '',

  `pa_FullName` varchar(100) NOT NULL default '',

  `pa_Position` varchar(100) NOT NULL default '',

  `pa_Address` varchar(255) NOT NULL default '',

  `pa_Address2` varchar(255) NOT NULL default '',

  `pa_City` varchar(100) NOT NULL default '',

  `pa_State` varchar(2) NOT NULL default '',

  `pa_Zipcode` int(5) NOT NULL default '0',

  `pa_Phone` varchar(15) NOT NULL default '',

  `pa_Fax` varchar(15) default NULL,

  `pa_Website` varchar(255) default NULL,

  `pa_Email` varchar(255) NOT NULL default '',

  `pa_Description` longtext,

  `pa_DeleteFlag` char(1) NOT NULL default '0',

  `pa_DateAdd` varchar(25) NOT NULL default '',

  PRIMARY KEY  (`pa_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;
 

--

-- Dumping data for table `EEP_Partners`

--
 

INSERT INTO `EEP_Partners` (`pa_ID`, `pa_CompanyName`, `pa_FullName`, `pa_Position`, `pa_Address`, `pa_Address2`, `pa_City`, `pa_State`, `pa_Zipcode`, `pa_Phone`, `pa_Fax`, `pa_Website`, `pa_Email`, `pa_Description`, `pa_DeleteFlag`, `pa_DateAdd`) VALUES

(8, 'Eco Engineering, LLC', 'Tom Kirkpatrick', 'President', '11815 Highway Dr', 'Suite 600', 'Cincinnati', 'OH', 45241, '(513) 985-8300', '(513) 985-8300', 'www.ecoengineering.com', 'tkirkpatrick@ecoengineering.com', 'Eco Engineering is a national design build lighting contractor based on the North side of Cincinnati who specializes in energy efficient lighting retrofits of existing buildings ⬠including manufacturing, distribution centers, office and institutional facilities.  We are manufacturer and distributor neutral, and offer lighting auditing, design, construction, and installation services along with EPACT tax deduction certification and rebate management.', '0', ''),

(4, 'Garber Electrical Contractors', 'Mark Penny', 'Sales/Project Manager', '317 N. Washington St', '', 'New Paris', 'OH', 45347, '(937) 437-2841', '(937) 437-0804', '', 'mpenny@garberelectric.com', '', '0', ''),

(5, 'Slagle Mechanical Contractors', 'Tim Locke', 'Service Manager', '877 W Russell Rd', '', 'Sidney', 'OH', 45365, '(937) 492-4151', '(937) 492-7318', 'www.slaglemech.com', '', '', '0', ''),

(6, 'Sylvania Lighting Services', 'Len Segal', 'Executive Major Account Representative', '10279 Fawncrest Ct', '', 'Loveland', 'OH', 45140, '(513) 774-7055', '(815) 572-0799', 'www.sylvania.com/sls', '', '', '0', ''),

(7, 'McAfee Heating & Air Conditioning', 'Greg McAfee', 'President', '4770 Hempstead Station', '', 'Kettering', 'OH', 45429, '(937) 438-1976', '(937) 438-1919', 'www.mcair.com', 'greg@mcair.com', '', '0', ''),

(2, 'Becker Electrical Supply', 'Ellen Phipps', 'Lighting Quotations Supervisor', '1341 E 4th St', '', 'Dayton', 'OH', 45402, '(937) 226-1341', '(937) 226-1790', 'www.beckerelectric.com', 'ellenphipps@beckerelectric.com', '', '0', ''),

(1, 'Monroe Mechanical, Inc', 'Kelly Baily', '', '225 American Way', 'PO Box 380', 'Monroe', 'OH', 45050, '(513) 539-7555', '(513) 539-9555', 'www.monroeinc.com', 'kbaily@monroeinc.com', '', '0', ''),

(9, 'Rexel Electrical & Datacom', 'Rob Christman', 'Lighting Specialist', '6196 Poe Ave', '', 'Dayton', 'OH', 45414, '(937) 367-7085', '(937) 264-9067', 'www.rexelusa.com', 'rchristman@rexelusa.com', '', '0', ''),

(10, 'Teleco, Inc.', 'Tom Tobias', 'Design Engineer', '2040 Brandt Pike', '', 'Dayton', 'OH', 45404, '(937) 236-1155', '(937) 236-3842', 'www.telecoenergy.com', 'ttobias@telecoenergy.com', '', '0', ''),

(11, 'Tobias Heating & Air Conditioning Co.', 'Tom Tobias', '', '2040 Brandt Pike', '', 'Dayton', 'OH', 45404, '(937) 236-3400', '(937) 236-3842', 'www.tobiasheating.com', 'ttobias@tobiasheating.com', '', '0', ''),

(12, 'Mr. Electric', 'Mike McGrew', 'Vice President/Owner/Operator', '4517 Gateway Circle', '', 'Kettering', 'OH', 45440, '(937) 436-1303', '(937) 434-9068', 'www.mrelectric.com', 'mike@ketteringelectric.com', '', '0', ''),

(13, 'Kettering Electric', 'Mike McGrew', 'President/Owner', '4517 Gateway Circle', '', 'Kettering', 'OH', 45440, '(937) 434-9065', '(937) 434-9068', 'www.ketteringelectric.com', 'mike@ketteringelectric.com', '', '0', ''),

(14, 'Wat-Kem Mechanical', 'Steven Meier', 'Project Coordinator', '2755 S County Rd 25A', '', 'Troy', 'OH', 45373, '(937) 533-6332', '(937) 573-3082', 'www.watkem.com', '', '', '0', ''),

(15, 'Miami Valley Lighting & Supply', 'Steve or Dick', '', '10871 Engle Rd', '', 'Vandalia', 'OH', 45377, '(937) 387-6980', '(937) 387-6999', '', 'mvlites@aol.com', '', '0', ''),

(16, 'Buschur Electric, Inc', 'Steve Hilgefort', 'Vice President', '305 W 1st St.', 'PO Box 107', 'Minster', 'OH', 45865, '(419) 628-3407', '(419) 628-2021', 'www.buschurelectric.com', 'steveh@buschurelectric.com', '', '0', ''),

(17, 'The Lighting Guys, Inc', 'John Trimbach', 'President', '3409 N Main St', '', 'Dayton', 'OH', 45405, '(937) 223-5659', '(937) 223-5792', 'www.thelightingguysinc.com', 'john@thelightingguysinc.com', '', '0', ''),

(18, 'Waibel Energy Systems', 'Greg Smith', 'Certified Energy Manager/Sales Leader', '815 Falls Creek', '', 'Vandalia', 'OH', 45377, '(937) 264-4343', '(937) 264-4360', 'www.waibelenergysystems.com', 'gssmith@trane.com', 'Waibel Energy Systems specializes in supplying industrial, institutional and commercial HVAC equipment, parts, service and training throughout the Miami Valley area.  We offer control installations, system integration and energy audits and retrofits, as well as BuildingLogiX, a suite of products that integrate a customer's HVAC systems with real time analysis and in-house support for monitoring, thus improving building operations & energy efficiency.', '0', ''),

(19, 'Lightsense LLC', 'Don Wiley', 'President/Owner', '274 Roanne Ct', '', 'Centerville', 'OH', 45458, '(937) 433-1021', '(937) 433-0882', '', 'lightsenseenergy@aol.com', '', '0', ''),

(20, 'Orbit Industrial Group (Response Mechanical)', 'Carl Crawford', 'Marketing & Sales Director', '1101 Negley Place', '', 'Dayton', 'OH', 45402, '(937) 414-0257', '(937) 274-4990', 'www.orbitindustrialgroup.com', '', '', '0', ''),

(21, 'Rieck Services', '', '', '5245 Wadsworth Rd', '', 'Dayton', 'OH', 45414, '(937) 274-1987', '(937) 274-7392', 'www.rieckservices.com', 'info@rieckservices.com', '', '0', ''),

(22, 'Honeywell International Inc.', 'Keith Valiquette', 'Account Executive', '1232 Dayton Yellow Springs Rd', '', 'Fairborn', 'OH', 45324, '(937) 754-4129', '(937) 754-4105', 'www.honeywell.com', 'keith.valiquette@honeywell.com', '', '0', ''),

(23, 'Sanders & Associates', 'Robert Sanders', 'Owner', '1601 Tyrone Rd', '', 'Troy', 'OH', 45373, '(937) 335-0293', '(937) 335-0293', 'www.sandersandassoc.com', 'rsanders@woh.rr.com', '', '0', ''),

(24, 'Clinton Electrical & Plumbing Supply', 'Jan Claibourne', 'Electrical Sales', '1450 W Main St', '', 'Wilmington', 'OH', 45177, '(937) 382-3804', '(937) 382-7138', 'www.cepsupply.com', 'jan@cepsupply.com', '', '0', ''),

(25, 'Philips Lighting', 'Mark Ross', 'Senior Account Manager', '20 Fox Harbor Dr', '', 'Troy', 'OH', 45373, '(937) 216-0336', '(732) 652-4387', 'www.philips.com', 'mark.e.ross@philips.com', '', '0', ''),

(26, 'Tanner Heating & Air Conditioning Inc.', 'Tom Tanner', '', '2238 E. River Rd', '', 'Dayton', 'OH', 45439, '(937) 299-2500', '(937) 299-2590', 'www.tannerhvac.com', 'info@tannerhvac.com', '', '0', ''),

(27, 'Sidney Electric Company', 'Bill McClain', '', '840 S. Vandemark Rd', '', 'Sidney', 'OH', 45365, '(937) 498-2357', '(937) 498-1178', 'www.sidneyelectric.com', 'info@sidneyelectric.com', '', '0', ''),

(28, 'Freedom Electric', 'Scott Whip', 'Manager, Electrical Contracting', '700 S. Main St', '', 'Dayton', 'OH', 45402, '(937) 228-0660', '(937) 228-9482', 'www.freedomelectric.com', 'scottw@freedomelectric.com', '', '0', ''),

(29, 'Express Electrical Services', 'Dana Beighle', 'President', '11 S. Main St', '', 'Tipp City', 'OH', 45371, '(937) 667-6824', '(937) 669-2363', 'www.expresssvc.com', 'dbeighle@expressscv.com', '', '0', ''),

(30, '3-Way Electric', 'Monte Miller', 'Preseident', '710 Hiddeson Ave', 'PO Box 171', 'Greenville', 'OH', 45331, '(937) 548-8843', '(937) 548-7987', '', '3wayelectric@gmail.com', '', '0', ''),

(31, 'The Habegger Corporation', 'Kerry Ward', 'Territory Manager', '632 E. First St', '', 'Dayton', 'OH', 45402, '(937) 224-8050', '(937) 224-5593', 'www.habeggercorp.com', 'kerryward@habeggercorp.com', '', '0', ''),

(32, 'Detmer and Sons Inc.', '', '', '1170 Channingway Dr.', '', 'Fairborn', 'OH', 45324, '(937) 879-2373', '(937) 879-2563', 'www.detmersons.com', 'hvac@detmersons.com', '', '0', ''),

(33, 'Applied Mechanical Systems, Inc.', 'Susan Herrmann', 'Customer Relations', '5598 Wolfcreek Pike', '', 'Dayton', 'OH', 45426, '(937) 854-3073', '(937) 837-9039', 'www.appliedmechanicalsys.com', 'sherrmann@appliedmechanicalsys.com', '', '0', ''),

(34, 'Wesco Distributon, Inc.', 'Trent Pinnick', 'Lighting Specialist', '2080 Winners Circle', '', 'Dayton', 'OH', 45404, '(937) 228-9668', '(937) 228-3667', 'www.wesco.com', 'tpinnick@wesco.com', '', '0', ''),

(35, 'Northern Kentucky Electric Service', 'Mike Glotfelter', 'Project Manager', 'PO Box 380', '', 'Alexandria', 'KY', 41001, '(859) 393-8068', '(859) 477-6554', 'www.nkyelectric.com', 'mglotfelter@nkyelectric.com', '', '0', ''),

(36, 'Mechanical Systems of Dayton, Inc.', 'Gary Gayhart', 'Service Representative', '4401 Springfield St.', '', 'Dayton', 'OH', 45431, '(937) 254-3235', '(937) 254-4295', 'www.msdinc.com', 'ggayhart@msdinc.net', '', '0', ''),

(37, 'Hy-Tek', 'Jeff Fogle', 'Sales Engineer', '7847 Tanners Gate Dr.', '', 'Florence', 'KY', 41042, '(859) 647-0608', '(859) 647-1405', 'www.hy-tek.net', 'jfogle@hy-tek.net', '', '0', ''),

(38, 'Wells Electric Service LLC', 'Cindy', '', '4008 N. Dixie Dr', '', 'Dayton', 'OH', 45414, '(937) 274-7351', '(937) 274-9059', '', 'wellselectric@att.net', '', '0', ''),

(39, 'H & S Plumbing Inc.', 'Randall Shoup', 'President', '5590 W. Kessler-Cowlersville Rd', '', 'West Milton', 'OH', 45383, '(937) 836-8725', '(937) 698-1234', 'www.hsplumbing.net', 'randy@hsplumbing.net', '', '0', ''),

(40, 'Reddy Electric Co.', 'Stephen Staner', 'Vice President- Service Department', '1145 Bellbrook Ave.', '', 'Xenia', 'OH', 45385, '(937) 372-8205', '(937) 372-1556', 'www.reddyelectric.com', 'steves@reddyelectric.com', '', '0', ''),

(41, 'M & R Electric Motor Service, Inc.', 'Larry Mader', 'Insude Sales', '1516 E. Fifth St.', '', 'Dayton', 'OH', 45403, '(937) 222-6282', '(937) 222-1901', 'www.mrelectricsales.com', 'info@mrelectricsales.com', '', '0', ''),

(42, 'LeVeck Ligting Products & Service', '', '', '8415 St. Rt. 202', '', 'Tipp City', 'OH', 45371, '(800) 824-3615', '', 'www.leveck.com', '', '', '0', ''),

(43, 'Industrial Air Centers, Inc.', 'Travis Lovejoy', 'Area Manager', '6428 Castle Dr.', '', 'Mason', 'OH', 45040, '(513) 770-4161', '(513) 770-4165', 'www.iacserv.com', 'tlovejoy@iacserv.com', '', '0', ''),

(44, 'Trame Mechanical', '', '', '1712 E. First St.', '', 'Dayton', 'OH', 45403, '', '', 'www.trame.com', '', '', '0', ''),

(45, 'Larry Lee Heating and A/C', 'Larry Lee', 'Owner', '125 Poplar Ct.', '', 'Lakeview', 'OH', 43331, '(937) 539-0756', '(937) 842-4367', 'larryleeheatingandac.com', 'larry100lee@yahoo.com', '', '0', ''),

(46, 'C & J Electric Inc.', 'Charlie Axe', 'President', '9195 St. Rt. 119', '', 'Anna', 'OH', 45302, '(937) 394-2300', '(937) 394-3516', '', 'caxe-cjelectric@watchtv.net', '', '0', ''),

(47, 'Dickman Supply, Inc.', 'Tim Bickel', 'Lighting Specialist', '1991 St. Marys Ave.', '', 'Sidney', 'OH', 45365, '(937) 492-6166', '(937) 498-4126', 'www.dickmansupply.com', 'tbickel@dickmansupply.com', '', '0', ''),

(48, 'The Bulbologist Lighting', 'Richard Martinson', 'President', '4573 Timberwilde Dr.', '', 'Kettering', 'OH', 45440, '(937) 298-9956', '(937) 298-4489', '', 'bulbology@aol.com', '', '0', ''),

(49, 'Air Handling Equipment, Inc.', 'Kurt Barhorst', 'Vice President', '1389 Riverside Dr', '', 'Sidney', 'OH', 45365, '(937) 492-5331', '(937) 492-3147', 'www.air-handlingequipment.com', 'kurt@ahequip.net', '', '0', ''),

(50, 'Jutte Electric Ltd.', 'Bob Leverette', 'Project Manager', '1001 Industrial Drive West', '', 'Fort Recovery', 'OH', 45846, '(419) 852-8355', '(419) 375-2246', 'www.jutteelectric.com', 'bleverette@jutteelectric.com', '', '0', ''),

(51, 'King Lighting Inc.', 'Tony Rowland', '', '333 W. First St.', '', 'Dayton', 'OH', 45402, '(937) 223-0303', '(937) 223-3322', 'www.king-lighting.com', 'tony.rowland@king-lighting.com', '', '0', '');
 

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

--

-- Table structure for table `EEP_PartnerType`

--
 

CREATE TABLE IF NOT EXISTS `EEP_PartnerType` (

  `pt_ID` int(5) NOT NULL auto_increment,

  `pt_TypeName` varchar(255) NOT NULL default '',

  `pt_DeleteFlag` char(1) NOT NULL default '0',

  `pt_DateAdd` varchar(25) NOT NULL default '',

  `pt_DateAdd_User` int(5) NOT NULL default '0',

  `pt_DateEdit` varchar(25) default NULL,

  `pt_DateEdit_User` int(5) default NULL,

  PRIMARY KEY  (`pt_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
 

--

-- Dumping data for table `EEP_PartnerType`

--
 

INSERT INTO `EEP_PartnerType` (`pt_ID`, `pt_TypeName`, `pt_DeleteFlag`, `pt_DateAdd`, `pt_DateAdd_User`, `pt_DateEdit`, `pt_DateEdit_User`) VALUES

(5, 'Contractors/Installers', '0', '2/2/2009', 1, NULL, NULL),

(6, 'Engineers', '0', '2/2/2009', 1, NULL, NULL),

(7, 'Designer', '1', '2/2/2009', 1, NULL, NULL),

(8, 'Consultants/Designers', '0', '2/2/2009', 1, NULL, NULL),

(9, 'Distributors', '0', '2/2/2009', 1, NULL, NULL),

(10, 'Manufacturers', '0', '2/2/2009', 1, NULL, NULL),

(12, 'Architects', '0', '', 0, NULL, NULL),

(13, 'Energy Auditors', '0', '', 0, NULL, NULL);
 

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

--

-- Table structure for table `EEP_Program`

--
 

CREATE TABLE IF NOT EXISTS `EEP_Program` (

  `pg_ID` int(5) NOT NULL auto_increment,

  `pg_ProgramName` varchar(255) character set utf8 NOT NULL default '',

  `pg_ProgramAbbrev` varchar(4) character set utf8 NOT NULL default '',

  `pg_Active` char(1) character set utf8 default '0',

  `pg_DeleteFlag` char(1) character set utf8 NOT NULL default '0',

  `pg_DateAdd` varchar(25) character set utf8 NOT NULL default '',

  `pg_DateAdd_User` int(5) NOT NULL default '0',

  `pg_DateEdit` varchar(25) character set utf8 default NULL,

  `pg_DateEdit_User` int(5) default '0',

  PRIMARY KEY  (`pg_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 

--

-- Dumping data for table `EEP_Program`

--
 

INSERT INTO `EEP_Program` (`pg_ID`, `pg_ProgramName`, `pg_ProgramAbbrev`, `pg_Active`, `pg_DeleteFlag`, `pg_DateAdd`, `pg_DateAdd_User`, `pg_DateEdit`, `pg_DateEdit_User`) VALUES

(1, 'Heating, Ventilation and Air Conditioning', 'HVAC', '1', '0', '2/2/09', 0, NULL, 0),

(2, 'Lighting Fixtures and Controls', 'LITE', '1', '0', '2/2/09', 0, NULL, 0),

(3, 'Motors, Drives and Compressed Air', 'MOTR', '1', '0', '2/2/09', 0, NULL, 0),

(4, 'Other Technologies', 'ADNL', '1', '0', '2/2/09', 0, NULL, 0);
 

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

--

-- Table structure for table `xr_partners_options`

--
 

CREATE TABLE IF NOT EXISTS `xr_partners_options` (

  `xrpo_ID` int(11) NOT NULL auto_increment,

  `xrpo_PID` int(11) NOT NULL default '0',

  `xrpo_Type` int(11) NOT NULL default '0',

  `xrpo_TypeID` int(11) NOT NULL default '0',

  PRIMARY KEY  (`xrpo_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=386 ;
 

--

-- Dumping data for table `xr_partners_options`

--
 

INSERT INTO `xr_partners_options` (`xrpo_ID`, `xrpo_PID`, `xrpo_Type`, `xrpo_TypeID`) VALUES

(77, 5, 3, 5),

(76, 5, 3, 8),

(75, 5, 2, 4),

(74, 5, 2, 3),

(73, 5, 2, 2),

(72, 5, 2, 1),

(71, 2, 1, 1),

(51, 4, 1, 3),

(50, 4, 1, 2),

(49, 4, 3, 5),

(48, 4, 2, 2),

(45, 3, 1, 2),

(46, 3, 1, 3),

(47, 3, 1, 1),

(34, 3, 2, 1),

(35, 3, 2, 2),

(36, 3, 2, 3),

(37, 3, 2, 4),

(38, 3, 3, 12),

(39, 3, 3, 8),

(40, 3, 3, 5),

(41, 3, 3, 9),

(42, 3, 3, 13),

(43, 3, 3, 6),

(44, 3, 3, 10),

(68, 2, 3, 13),

(69, 2, 1, 2),

(70, 2, 1, 3),

(64, 2, 2, 2),

(65, 2, 2, 4),

(66, 2, 3, 8),

(67, 2, 3, 9),

(61, 1, 1, 2),

(62, 1, 1, 3),

(63, 1, 1, 1),

(52, 1, 2, 1),

(53, 1, 2, 2),

(54, 1, 2, 3),

(55, 1, 2, 4),

(56, 1, 3, 12),

(57, 1, 3, 8),

(58, 1, 3, 5),

(59, 1, 3, 13),

(60, 1, 3, 6),

(78, 5, 3, 6),

(79, 5, 1, 2),

(80, 5, 1, 3),

(81, 6, 2, 2),

(82, 6, 3, 8),

(83, 6, 3, 5),

(84, 6, 3, 13),

(85, 6, 1, 2),

(86, 6, 1, 3),

(87, 7, 2, 1),

(88, 7, 3, 5),

(89, 7, 1, 1),

(90, 8, 2, 2),

(91, 8, 3, 8),

(92, 8, 3, 5),

(93, 8, 3, 13),

(94, 8, 3, 6),

(95, 8, 1, 2),

(96, 8, 1, 3),

(97, 9, 2, 2),

(98, 9, 3, 9),

(99, 9, 1, 2),

(100, 9, 1, 3),

(101, 10, 2, 1),

(102, 10, 2, 2),

(103, 10, 2, 3),

(104, 10, 2, 4),

(105, 10, 3, 8),

(106, 10, 3, 5),

(107, 10, 3, 13),

(108, 10, 3, 6),

(109, 10, 1, 2),

(110, 10, 1, 3),

(111, 11, 2, 1),

(112, 11, 2, 2),

(113, 11, 2, 3),

(114, 11, 3, 8),

(115, 11, 3, 5),

(116, 11, 3, 6),

(117, 11, 1, 2),

(118, 11, 1, 3),

(119, 12, 2, 2),

(120, 12, 2, 3),

(121, 12, 3, 5),

(122, 12, 1, 2),

(123, 12, 1, 1),

(124, 13, 2, 2),

(125, 13, 3, 5),

(126, 13, 1, 2),

(127, 13, 1, 3),

(128, 13, 1, 1),

(129, 14, 2, 1),

(130, 14, 3, 5),

(131, 14, 1, 2),

(132, 14, 1, 1),

(133, 15, 2, 2),

(134, 15, 3, 9),

(135, 15, 1, 2),

(136, 15, 1, 3),

(137, 16, 2, 2),

(138, 16, 2, 3),

(139, 16, 2, 4),

(140, 16, 3, 8),

(141, 16, 3, 5),

(142, 16, 1, 2),

(143, 16, 1, 3),

(144, 17, 2, 2),

(145, 17, 3, 8),

(146, 17, 3, 5),

(147, 17, 3, 9),

(148, 17, 3, 13),

(149, 17, 1, 2),

(150, 17, 1, 3),

(380, 18, 3, 5),

(379, 18, 3, 8),

(378, 18, 2, 3),

(377, 18, 2, 2),

(376, 18, 2, 1),

(383, 18, 3, 6),

(382, 18, 3, 13),

(381, 18, 3, 9),

(385, 18, 1, 3),

(384, 18, 1, 2),

(161, 19, 2, 2),

(162, 19, 3, 8),

(163, 19, 3, 5),

(164, 19, 3, 13),

(165, 19, 1, 2),

(166, 19, 1, 3),

(167, 20, 2, 1),

(168, 20, 2, 2),

(169, 20, 2, 3),

(170, 20, 3, 8),

(171, 20, 3, 5),

(172, 20, 1, 2),

(173, 20, 1, 3),

(174, 21, 2, 1),

(175, 21, 2, 3),

(176, 21, 3, 8),

(177, 21, 3, 5),

(178, 21, 3, 13),

(179, 21, 3, 6),

(180, 21, 1, 2),

(181, 21, 1, 3),

(182, 22, 2, 1),

(183, 22, 2, 2),

(184, 22, 2, 3),

(185, 22, 2, 4),

(186, 22, 3, 8),

(187, 22, 3, 5),

(188, 22, 3, 13),

(189, 22, 3, 6),

(190, 22, 3, 10),

(191, 22, 1, 2),

(192, 22, 1, 3),

(193, 23, 2, 4),

(194, 23, 3, 9),

(195, 23, 1, 2),

(196, 23, 1, 3),

(197, 24, 2, 2),

(198, 24, 2, 3),

(199, 24, 3, 8),

(200, 24, 3, 9),

(201, 24, 1, 2),

(202, 24, 1, 3),

(203, 25, 2, 2),

(204, 25, 3, 6),

(205, 25, 3, 10),

(206, 25, 1, 2),

(207, 25, 1, 3),

(208, 25, 1, 1),

(209, 26, 2, 1),

(210, 26, 3, 8),

(211, 26, 3, 5),

(212, 26, 1, 2),

(213, 26, 1, 3),

(214, 26, 1, 1),

(215, 27, 2, 2),

(216, 27, 2, 3),

(217, 27, 3, 8),

(218, 27, 3, 5),

(219, 27, 1, 2),

(220, 27, 1, 3),

(221, 28, 2, 2),

(222, 28, 2, 3),

(223, 28, 3, 5),

(224, 28, 1, 2),

(225, 28, 1, 3),

(226, 29, 2, 2),

(227, 29, 2, 3),

(228, 29, 3, 8),

(229, 29, 3, 5),

(230, 29, 3, 13),

(231, 29, 3, 6),

(232, 29, 1, 2),

(233, 29, 1, 3),

(234, 30, 2, 2),

(235, 30, 2, 3),

(236, 30, 3, 5),

(237, 30, 1, 2),

(238, 30, 1, 3),

(239, 30, 1, 1),

(240, 31, 2, 1),

(241, 31, 2, 3),

(242, 31, 3, 9),

(243, 31, 1, 2),

(244, 31, 1, 1),

(245, 32, 2, 1),

(246, 32, 3, 5),

(247, 32, 1, 2),

(248, 32, 1, 1),

(249, 33, 2, 1),

(250, 33, 2, 2),

(251, 33, 2, 3),

(252, 33, 3, 8),

(253, 33, 3, 5),

(254, 33, 3, 13),

(255, 33, 3, 6),

(256, 33, 1, 2),

(257, 33, 1, 3),

(258, 34, 2, 2),

(259, 34, 2, 3),

(260, 34, 3, 9),

(261, 34, 3, 13),

(262, 34, 1, 2),

(263, 34, 1, 3),

(264, 35, 2, 2),

(265, 35, 3, 5),

(266, 35, 1, 2),

(267, 35, 1, 3),

(268, 36, 2, 1),

(269, 36, 2, 3),

(270, 36, 2, 4),

(271, 36, 3, 5),

(272, 36, 3, 13),

(273, 36, 3, 6),

(274, 36, 1, 2),

(275, 36, 1, 3),

(276, 37, 2, 2),

(277, 37, 2, 3),

(278, 37, 3, 8),

(279, 37, 3, 9),

(280, 37, 3, 13),

(281, 37, 3, 6),

(282, 37, 1, 3),

(283, 38, 2, 2),

(284, 38, 3, 5),

(285, 38, 1, 2),

(286, 38, 1, 1),

(287, 39, 2, 4),

(288, 39, 3, 5),

(289, 39, 1, 2),

(290, 39, 1, 1),

(291, 40, 2, 2),

(292, 40, 2, 3),

(293, 40, 3, 5),

(294, 40, 3, 13),

(295, 40, 1, 2),

(296, 40, 1, 3),

(297, 41, 2, 1),

(298, 41, 2, 3),

(299, 41, 2, 4),

(300, 41, 3, 9),

(301, 41, 1, 2),

(302, 41, 1, 3),

(303, 41, 1, 1),

(304, 42, 2, 2),

(305, 42, 3, 8),

(306, 42, 3, 5),

(307, 42, 3, 9),

(308, 42, 3, 13),

(309, 42, 1, 2),

(310, 43, 2, 3),

(311, 43, 3, 5),

(312, 43, 3, 9),

(313, 43, 3, 13),

(314, 43, 3, 6),

(315, 43, 1, 3),

(316, 44, 2, 1),

(317, 44, 2, 2),

(318, 44, 2, 3),

(319, 44, 3, 8),

(320, 44, 3, 5),

(321, 44, 3, 13),

(322, 44, 1, 2),

(323, 44, 1, 3),

(324, 45, 2, 2),

(325, 45, 2, 4),

(326, 45, 3, 5),

(327, 45, 3, 13),

(328, 45, 1, 2),

(329, 45, 1, 1),

(330, 46, 2, 2),

(331, 46, 2, 3),

(332, 46, 3, 5),

(333, 46, 1, 2),

(334, 46, 1, 3),

(335, 47, 2, 2),

(336, 47, 2, 4),

(337, 47, 3, 8),

(338, 47, 3, 9),

(339, 47, 3, 13),

(340, 47, 3, 6),

(341, 47, 1, 2),

(342, 47, 1, 3),

(343, 47, 1, 1),

(344, 48, 2, 2),

(345, 48, 3, 9),

(346, 48, 1, 2),

(347, 49, 2, 3),

(348, 49, 3, 8),

(349, 49, 3, 5),

(350, 49, 3, 9),

(351, 49, 3, 13),

(352, 49, 1, 2),

(353, 49, 1, 3),

(354, 50, 2, 2),

(355, 50, 3, 8),

(356, 50, 3, 5),

(357, 50, 3, 9),

(358, 50, 1, 2),

(359, 50, 1, 3),

(360, 51, 2, 2),

(361, 51, 3, 8),

(362, 51, 3, 13),

(363, 51, 1, 2),

(364, 51, 1, 3),

(365, 51, 1, 1);

Open in new window

0
Comment
Question by:BGHTechGuru
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 24813071
I think you're just missing the relationship between EEP_Partners and xr_partners_options
FROM `EEP_Partners`

  INNER JOIN `xr_partners_options` PROGRAMS 

    ON EEP_Partners.pa_ID = PROGRAMS.xrpo_PID

  INNER JOIN `xr_partners_options` PARTNERS 

    ON EEP_Partners.pa_ID = PARTNERS.xrpo_PID

Open in new window

0
 

Author Closing Comment

by:BGHTechGuru
ID: 31601280
Thanks for the quick answer! Helped me alot!
0
 

Author Comment

by:BGHTechGuru
ID: 24814679
Here is the finished SQL query. My hat's off to snoyes_jw

SELECT  
                                    
DISTINCT `pa_ID`,
`pa_CompanyName`,
`pa_ID`,
`pa_Phone`,
`pa_Fax`,
`pa_Website`,
`pa_Address`,
`pa_Address2`,
`pa_FullName`,
`pa_Description`,
`pa_Email`,
`pa_Position`,
`pa_City`,
`pa_State`,
`pa_Zipcode`
                                    
FROM `EEP_Partners`
                                    
INNER JOIN `xr_partners_options` PROGRAMS ON EEP_Partners.pa_ID = PROGRAMS.xrpo_PID
INNER JOIN `xr_partners_options` PARTNERS ON EEP_Partners.pa_ID = PARTNERS.xrpo_PID
                  
WHERE
                                    
PROGRAMS.`xrpo_Type` =2 AND PROGRAMS.`xrpo_TypeID` IN ( 2 ) AND
PARTNERS.`xrpo_Type` =3 AND PARTNERS.`xrpo_TypeID` IN ( 13 ) AND
                                    
`pa_DeleteFlag` = '0'
                                    
ORDER BY `pa_CompanyName` ASC
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now