Hello,
I am trying to write a custom report for a piece of shopping cart software when I create a custom report I enter the following sql, and yes this is the only way (well one of very few) ways this query can be written.
SELECT
Ordered_Items.record_number AS RecordID,
Ordered_Items.Name AS Item,
Options1.Value AS ChildFirst,
Options2.Value AS ChildLast,
CONCAT(Options1.Value, " ", Options2.Value) as FullName,
Options3.Value AS Grade,
Options4.Value AS Teacher,
Options5.Value AS Bread,
Options6.Value AS Filling,
Options7.Value AS DrinkChoice,
Options8.Value AS DrinkChoice2
FROM
Ordered_Items
LEFT JOIN (Ordered_Options as Options1)
ON (Options1.Ordered_Items_rn = Ordered_Items.record_number AND Options1.Options_rn = 43)
LEFT JOIN (Ordered_Options as Options2)
ON (Options2.Ordered_Items_rn = Ordered_Items.record_number AND Options2.Options_rn = 44)
LEFT JOIN (Ordered_Options as Options3)
ON (Options3.Ordered_Items_rn = Ordered_Items.record_number AND Options3.Options_rn = 45)
LEFT JOIN (Ordered_Options as Options4)
ON (Options4.Ordered_Items_rn = Ordered_Items.record_number AND Options4.Options_rn = 46)
LEFT JOIN (Ordered_Options as Options5)
ON (Options5.Ordered_Items_rn = Ordered_Items.record_number AND Options5.Options_rn = 47)
LEFT JOIN (Ordered_Options as Options6)
ON (Options6.Ordered_Items_rn = Ordered_Items.record_number AND Options6.Options_rn = 48)
LEFT JOIN (Ordered_Options as Options7)
ON (Options7.Ordered_Items_rn = Ordered_Items.record_number AND Options7.Options_rn = 49)
LEFT JOIN (Ordered_Options as Options8)
ON (Options8.Ordered_Items_rn = Ordered_Items.record_number AND Options8.Options_rn = 50)
GROUP BY FullName, Item
If I click on the SQL tab the result is returned correctly however when I try to advance to the next step I get a popup "Encountered Improper Argument" I have pasted the corresponding tables sql below.
-- phpMyAdmin SQL Dump
-- version 2.11.7.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 03, 2009 at 05:51 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
--
-- Database: `sqsandbox`
--
-- --------------------------------------------------------
--
-- Table structure for table `Ordered_Items`
--
CREATE TABLE `Ordered_Items` (
`record_number` int(11) NOT NULL auto_increment,
`Order_rn` int(11) NOT NULL default '0',
`Item_rn` int(11) NOT NULL default '0',
`Order_Number` varchar(100) NOT NULL,
`Name` varchar(100) NOT NULL,
`Quantity` int(6) NOT NULL default '0',
`Options` text NOT NULL,
`Base_Price` decimal(10,2) NOT NULL default '0.00',
`Options_Price` decimal(10,2) NOT NULL default '0.00',
`Handling_Subtotal` decimal(10,2) NOT NULL default '0.00',
`Item_Total` decimal(10,2) NOT NULL default '0.00',
`Item_Subtotal` decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (`record_number`),
KEY `Order_rn` (`Order_rn`),
KEY `Item_rn` (`Item_rn`),
KEY `Order_Number` (`Order_Number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `Ordered_Items`
--
INSERT INTO `Ordered_Items` VALUES(1, 1, 429, '1246654348', 'One Slice: Prefirst - 8th', 1, '', 72.00, 0.00, 0.00, 72.00, 72.00);
INSERT INTO `Ordered_Items` VALUES(2, 1, 429, '1246654348', 'One Slice: Prefirst - 8th', 1, '', 72.00, 0.00, 0.00, 72.00, 72.00);
INSERT INTO `Ordered_Items` VALUES(3, 1, 432, '1246654348', 'Chicken Tenders', 1, '', 70.00, 0.00, 0.00, 70.00, 70.00);
INSERT INTO `Ordered_Items` VALUES(4, 1, 432, '1246654348', 'Chicken Tenders', 1, '', 70.00, 0.00, 0.00, 70.00, 70.00);
INSERT INTO `Ordered_Items` VALUES(5, 1, 433, '1246654348', 'Fall: 1 Drink Per Day', 1, '', 54.00, 0.00, 0.00, 54.00, 54.00);
INSERT INTO `Ordered_Items` VALUES(6, 1, 433, '1246654348', 'Fall: 1 Drink Per Day', 1, '', 54.00, 0.00, 0.00, 54.00, 54.00);
INSERT INTO `Ordered_Items` VALUES(7, 1, 434, '1246654348', 'Winter: 1 Drink Per Day ', 1, '', 54.00, 0.00, 0.00, 54.00, 54.00);
INSERT INTO `Ordered_Items` VALUES(8, 1, 434, '1246654348', 'Winter: 1 Drink Per Day ', 1, '', 54.00, 0.00, 0.00, 54.00, 54.00);
INSERT INTO `Ordered_Items` VALUES(9, 1, 438, '1246654348', 'Spring: 2 Drinks Per Day', 1, '', 108.00, 0.00, 0.00, 108.00, 108.00);
INSERT INTO `Ordered_Items` VALUES(10, 1, 438, '1246654348', 'Spring: 2 Drinks Per Day', 1, '', 108.00, 0.00, 0.00, 108.00, 108.00);
-- phpMyAdmin SQL Dump
-- version 2.11.7.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 03, 2009 at 05:51 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
--
-- Database: `sqsandbox`
--
-- --------------------------------------------------------
--
-- Table structure for table `Ordered_Options`
--
CREATE TABLE `Ordered_Options` (
`record_number` int(11) unsigned NOT NULL auto_increment,
`Ordered_Items_rn` int(11) unsigned default NULL,
`Options_rn` int(11) unsigned default NULL,
`Choices_rn` int(11) unsigned default NULL,
`Choices_Custom_rn` int(11) unsigned default NULL,
`Option_Name` varchar(50) NOT NULL,
`Value` text NOT NULL,
`Price` decimal(10,2) NOT NULL default '0.00',
`Total_Price` decimal(10,2) NOT NULL,
`Price_Operator` varchar(5) NOT NULL,
`Weight` decimal(10,2) NOT NULL default '0.00',
`Weight_Operator` varchar(5) NOT NULL,
`Type` varchar(10) NOT NULL,
PRIMARY KEY (`record_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=49 ;
--
-- Dumping data for table `Ordered_Options`
--
INSERT INTO `Ordered_Options` VALUES(1, 1, 43, 0, 0, 'Child First', 'Jane', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(2, 1, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(3, 1, 45, 142, 0, 'Grade Level', 'PF', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(4, 1, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(5, 2, 43, 0, 0, 'Child First', 'John', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(6, 2, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(7, 2, 45, 150, 0, 'Grade Level', '8', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(8, 2, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(9, 3, 43, 0, 0, 'Child First', 'Jane', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(10, 3, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(11, 3, 45, 142, 0, 'Grade Level', 'PF', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(12, 3, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(13, 4, 43, 0, 0, 'Child First', 'John', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(14, 4, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(15, 4, 45, 150, 0, 'Grade Level', '8', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(16, 4, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(17, 5, 43, 0, 0, 'Child First', 'Jane', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(18, 5, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(19, 5, 45, 141, 0, 'Grade Level', 'K', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(20, 5, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(21, 5, 49, 158, 0, 'Drink Choice', '2% White Milk', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(22, 6, 43, 0, 0, 'Child First', 'John', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(23, 6, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(24, 6, 45, 150, 0, 'Grade Level', '8', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(25, 6, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(26, 6, 49, 159, 0, 'Drink Choice', 'Low Fat Chocolate Milk', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(27, 7, 43, 0, 0, 'Child First', 'Jane', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(28, 7, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(29, 7, 45, 141, 0, 'Grade Level', 'K', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(30, 7, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(31, 7, 49, 160, 0, 'Drink Choice', 'Orange Juice', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(32, 8, 43, 0, 0, 'Child First', 'John', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(33, 8, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(34, 8, 45, 150, 0, 'Grade Level', '8', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(35, 8, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(36, 8, 49, 160, 0, 'Drink Choice', 'Orange Juice', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(37, 9, 43, 0, 0, 'Child First', 'john', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(38, 9, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(39, 9, 45, 150, 0, 'Grade Level', '8', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(40, 9, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(41, 9, 49, 158, 0, 'Drink Choice', '2% White Milk', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(42, 9, 50, 160, 0, 'Second Drink Choice', 'Orange Juice', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(43, 10, 43, 0, 0, 'Child First', 'Jane', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(44, 10, 44, 0, 0, 'Child Last', 'Doe', 0.00, 0.00, 'plus', 0.00, 'plus', 'text');
INSERT INTO `Ordered_Options` VALUES(45, 10, 45, 142, 0, 'Grade Level', 'PF', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(46, 10, 46, 151, 0, 'Teacher', 'Mrs. Loercher', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(47, 10, 49, 159, 0, 'Drink Choice', 'Low Fat Chocolate Milk', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
INSERT INTO `Ordered_Options` VALUES(48, 10, 50, 161, 0, 'Second Drink Choice', 'Water', 0.00, 0.00, 'plus', 0.00, 'plus', 'select');
