SQL JOIN Command

Trying to Create multiple joins

Page 1 of 1

12 Replies - 1798 Views - Last Post: 17 February 2008 - 07:40 PM Rate Topic: -----

#1 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

SQL JOIN Command

Post icon  Posted 15 February 2008 - 06:35 PM

I am new to this board but hopefully someone can help. I tried to create a query for adding a "grand total balance" of all inventory amounts. I wriote what I thought was the correct way to do it, but I can't get any joins to work. Someone willing to help a new guy. Thanks Michael
use KudlerFineFoods
create table Petty_Cash
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Undeposited_Reciepts
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Checking_Accounts
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Bank_Accounts
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Workmans_Comp
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Accounts_Recievable
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Reserve_Bad_Debt
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table LaJolla_Cheese_Dairy_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL
);
GO

create table LaJolla_Wine_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table DelMar_Bakery_Dept_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table DelMar_Meat_Seafood_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL
);
GO

create table DelMar_Produce_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL
);
GO

create table DelMar_Cheese_Dairy_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table DelMar_Wine_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Encinitas_Bakery_Dept_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

create table Encinitas_Meat_Seafood_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL
);
GO

create table Encinitas_Produce_Merchandise_Inventory
(
AccountNumber varchar (6)  PRIMARY KEY,
Description varchar (60)	  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money				 NOT NULL 
);
GO

insert into Petty_Cash values ('100001', 'Petty Cash', 'Ops Cash - La Jolla', '22,000.00');
insert into Petty_Cash values ('100002', 'Petty Cash', 'Ops Cash - Del Mar', '36,000.00');
insert into Petty_Cash values ('100003', 'Petty Cash', 'Ops Cash - Encinitas', '28,000.00');
insert into Undeposited_Reciepts values ('100010', 'La Jolla Undeposited Receipts', 'Undeposited Receipts', '25,000.00');
insert into Undeposited_Reciepts values ('100011', 'Del Mar Undeposited Receipts', 'Undeposited Receipts', '31,800.00');
insert into Undeposited_Reciepts values ('100012', 'Encinitas Undeposited Receipts', 'Undeposited Receipts', '21,000.00');
insert into Checking_Accounts values ('100020', 'Bradley Bank', 'Checking Account - La Jolla', '150,000.00');
insert into Checking_Accounts values ('100021', 'Kelsey Bank', 'Checking Account - Del Mar', '167,000.00');
insert into Checking_Accounts values ('100022', 'Downey Bank', 'Checking Account - Encinitas', '125,000.00');
insert into Bank_Accounts values ('100030', 'La Jolla Payroll', 'Bank Account', '100,000.00');
insert into Bank_Accounts values ('100031', 'Del Mar Payroll', 'Bank Account', '100,000.00');
insert into Bank_Accounts values ('100032', 'Encinitas Payroll', 'Bank Account', '100,000.00');
insert into Workmans_Comp values ('100040', 'La Jolla - Workers Comp', 'Workers Comp', '75,000.00');
insert into Workmans_Comp values ('100041', 'Del Mar - Workers Comp', 'Workers Comp', '80,000.00');
insert into Workmans_Comp values ('100042', 'Encinitas - Workers Comp', 'Workers Comp', '82,000.00');
insert into Accounts_Recievable values ('111000', 'Accounts Receivable', 'La Jolla', '21,000.00');
insert into Accounts_Recievable values ('112000', 'Accounts Receivable', 'Del Mar', '34,000.00');
insert into Accounts_Recievable values ('113000', 'Accounts Receivable', 'Encinitas', '45,000.00');
insert into Reserve_Bad_Debt values ('111001', 'Reserve - Bad Credit', 'La Jolla', '45,000.00');
insert into Reserve_Bad_Debt values ('112001', 'Reserve - Bad Credit', 'Del Mar', '55,000.00');
insert into Reserve_Bad_Debt values ('113001', 'Reserve - Bad Credit', 'Encinitas', '62,000.00');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127000', 'Parmigiano Reggiano', 'La Jolla Cheese & Dairy Merchandise Inventory', '494.50');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127001', 'Cheddar', 'La Jolla Cheese & Dairy Merchandise Inventory', '556.90');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127002', 'Brie', 'La Jolla Cheese & Dairy Merchandise Inventory', '1,019.83');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127003', 'Gouda', 'La Jolla Cheese & Dairy Merchandise Inventory', '740.97');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127004', 'Grana Padano', 'La Jolla Cheese & Dairy Merchandise Inventory', '607.78');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127005', 'Emmental', 'La Jolla Cheese & Dairy Merchandise Inventory', '307.86');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127006', 'Provolone', 'La Jolla Cheese & Dairy Merchandise Inventory', '335.21');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127007', 'Mozzarella', 'La Jolla Cheese & Dairy Merchandise Inventory', '489.27');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127008', 'Chevre', 'La Jolla Cheese & Dairy Merchandise Inventory', '576.65');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127009', 'Gruyere', 'La Jolla Cheese & Dairy Merchandise Inventory', '310.16');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127010', 'Gorgonzola', 'La Jolla Cheese & Dairy Merchandise Inventory', '385.18');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127011', 'Feta', 'La Jolla Cheese & Dairy Merchandise Inventory', '1,446.62');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127012', 'Stilton', 'La Jolla Cheese & Dairy Merchandise Inventory', '251.08');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127013', 'Asiago', 'La Jolla Cheese & Dairy Merchandise Inventory', '265.58');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127014', 'Roquefort', 'La Jolla Cheese & Dairy Merchandise Inventory', '700.46');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127015', 'Manchego', 'La Jolla Cheese & Dairy Merchandise Inventory', '634.94');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127016', 'Camembert', 'La Jolla Cheese & Dairy Merchandise Inventory', '368.82');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127017', 'Pecorino Romano', 'La Jolla Cheese & Dairy Merchandise Inventory', '778.70');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127018', 'Jarlsberg', 'La Jolla Cheese & Dairy Merchandise Inventory', '262.21');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127019', 'Maytag', 'La Jolla Cheese & Dairy Merchandise Inventory', '314.00');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127020', 'Ricotta', 'La Jolla Cheese & Dairy Merchandise Inventory', '935.11');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127021', 'Havarti', 'La Jolla Cheese & Dairy Merchandise Inventory', '287.86');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127022', 'Fontina', 'La Jolla Cheese & Dairy Merchandise Inventory', '564.24');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127023', 'Raclette', 'La Jolla Cheese & Dairy Merchandise Inventory', '361.97');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127024', 'Mascarpone', 'La Jolla Cheese & Dairy Merchandise Inventory', '336.56');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127025', 'Edam', 'La Jolla Cheese & Dairy Merchandise Inventory', '511.42');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127026', 'Limburger', 'La Jolla Cheese & Dairy Merchandise Inventory', '296.34');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127027', 'Aioli Sauce', 'La Jolla Cheese & Dairy Merchandise Inventory', '261.69');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127028', 'Beurre Barratte de Celles Sur', 'La Jolla Cheese & Dairy Merchandise Inventory', '288.12');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127029', 'Beurremont 83% Butter', 'La Jolla Cheese & Dairy Merchandise Inventory', '823.82');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127030', 'Boursin', 'La Jolla Cheese & Dairy Merchandise Inventory', '423.91');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127031', 'Cabridoux', 'La Jolla Cheese & Dairy Merchandise Inventory', '540.44');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127032', 'Clotted Creme', 'La Jolla Cheese & Dairy Merchandise Inventory', '625.71');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127033', 'Danish Lurpak Butter', 'La Jolla Cheese & Dairy Merchandise Inventory', '273.89');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127034', 'Dulce de Leche', 'La Jolla Cheese & Dairy Merchandise Inventory', '2,456.18');
insert into LaJolla_Cheese_Dairy_Merchandise_Inventory values ('127035', 'English Brandy Butter', 'La Jolla Cheese & Dairy Merchandise Inventory', '315.09');
insert into LaJolla_Wine_Merchandise_Inventory values ('129001', 'Blush Wines', 'La Jolla Wine Merchandise Inventory', '2,541.00');
insert into LaJolla_Wine_Merchandise_Inventory values ('129002', 'Dessert Wines', 'La Jolla Wine Merchandise Inventory', '6,750.34');
insert into LaJolla_Wine_Merchandise_Inventory values ('129003', 'Fruit Wines', 'La Jolla Wine Merchandise Inventory', '9,334.00');
insert into LaJolla_Wine_Merchandise_Inventory values ('129004', 'Red Wines', 'La Jolla Wine Merchandise Inventory', '8,903,00');
insert into LaJolla_Wine_Merchandise_Inventory values ('129005', 'Rice Wines', 'La Jolla Wine Merchandise Inventory', '2,345.99');
insert into LaJolla_Wine_Merchandise_Inventory values ('129006', 'Sparkling Wines', 'La Jolla Wine Merchandise Inventory', '6,543.00');
insert into LaJolla_Wine_Merchandise_Inventory values ('129007', 'White Wines', 'La Jolla Wine Merchandise Inventory', '4,234.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129008', 'Agiago Cheese', 'Del Mar Bakery Department Merchandise Inventory', '4,456.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129009', 'Calamata Olive Bread', 'Del Mar Bakery Department Merchandise Inventory', '2,345.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129010', 'Challah', 'Del Mar Bakery Department Merchandise Inventory', '3,230.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129011', 'Ciabatta', 'Del Mar Bakery Department Merchandise Inventory', '1,232.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129012', 'Danishs', 'Del Mar Bakery Department Merchandise Inventory', '987.67');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129013', 'Ficelle', 'Del Mar Bakery Department Merchandise Inventory', '234.89');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129014', 'Finnish Rye', 'Del Mar Bakery Department Merchandise Inventory', '1,234.56');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129015', 'Focaccia', 'Del Mar Bakery Department Merchandise Inventory', '4,567.34');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129016', 'French Croissants', 'Del Mar Bakery Department Merchandise Inventory', '7,567.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129017', 'Fruit Croissants', 'Del Mar Bakery Department Merchandise Inventory', '345.89');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129018', 'Fruit Tarts', 'Del Mar Bakery Department Merchandise Inventory', '880.56');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129019', 'Honey Wheat', 'Del Mar Bakery Department Merchandise Inventory', '5,670.98');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129020', 'Italian Sssemolina', 'Del Mar Bakery Department Merchandise Inventory', '6,564.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('129021', 'Prussian Rye', 'Del Mar Bakery Department Merchandise Inventory', '456.90');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('131000', 'Rustic Baguette', 'Del Mar Bakery Department Merchandise Inventory', '110.00');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('131001', 'Sourdough Rounds & Batar', 'Del Mar Bakery Department Merchandise Inventory', '435.89');
insert into DelMar_Bakery_Dept_Merchandise_Inventory values ('131002', 'Swiss Muesli', 'Del Mar Bakery Department Merchandise Inventory', '123.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133000', 'Angus Beef', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,560.96');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133001', 'Beluga', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,344.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133002', 'Caviar', 'Del Mar Meat and Seafood  Merchandise Inventory', '6,789.78');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133003', 'Coho Salmon', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,289.78');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133004', 'Copper River King Salmon', 'Del Mar Meat and Seafood  Merchandise Inventory', '1,232.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133005', 'Duck', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,345.99');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133006', 'Dungeness Crab', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,442.55');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133007', 'Goose', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,343.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133008', 'Guinea Fowl', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,331.10');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133009', 'Lobster', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,590.89');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133010', 'Oregon Sanddabs', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,890.99');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133011', 'Ostrich', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,234.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133012', 'Oysters', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,555.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133013', 'Pacific Swordfish', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,453.99');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133014', 'Partridge', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,678.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133015', 'Pheasant', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,899.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133016', 'Poussin', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,663.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133017', 'Quail', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,567.99');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133018', 'Red Snapper', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,567.99');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133019', 'Sausages', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,456.99');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133020', 'Squab', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,564.00');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133021', 'Wild King Salmaon', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,345.65');
insert into DelMar_Meat_Seafood_Merchandise_Inventory values ('133022', 'Wild Turkey', 'Del Mar Meat and Seafood  Merchandise Inventory', '1,233.54');
insert into DelMar_Produce_Merchandise_Inventory values ('135000', 'Gala Apples', 'Del Mar Produce  Merchandise Inventory', '941.87');
insert into DelMar_Produce_Merchandise_Inventory values ('135001', 'Fuji Apples', 'Del Mar Produce  Merchandise Inventory', '282.38');
insert into DelMar_Produce_Merchandise_Inventory values ('135002', 'Cortland Apples', 'Del Mar Produce  Merchandise Inventory', '529.26');
insert into DelMar_Produce_Merchandise_Inventory values ('135003', 'Empire Apples', 'Del Mar Produce  Merchandise Inventory', '1,440.37');
insert into DelMar_Produce_Merchandise_Inventory values ('135004', 'McIntosh Apples', 'Del Mar Produce  Merchandise Inventory', '593.74');
insert into DelMar_Produce_Merchandise_Inventory values ('135005', 'Pink Lady Apples', 'Del Mar Produce  Merchandise Inventory', '365.93');
insert into DelMar_Produce_Merchandise_Inventory values ('135006', 'Northen Spy Apples', 'Del Mar Produce  Merchandise Inventory', '415.55');
insert into DelMar_Produce_Merchandise_Inventory values ('135007', 'Golden Delicious Apples', 'Del Mar Produce  Merchandise Inventory', '551.25');
insert into DelMar_Produce_Merchandise_Inventory values ('135008', 'Rhode Island Greening Apples', 'Del Mar Produce  Merchandise Inventory', '6,333.53');
insert into DelMar_Produce_Merchandise_Inventory values ('135009', 'Gravenstein Apples', 'Del Mar Produce  Merchandise Inventory', '251.61');
insert into DelMar_Produce_Merchandise_Inventory values ('135010', 'Atemoya', 'Del Mar Produce  Merchandise Inventory', '442.34');
insert into DelMar_Produce_Merchandise_Inventory values ('135011', 'Breadfruit', 'Del Mar Produce  Merchandise Inventory', '331.39');
insert into DelMar_Produce_Merchandise_Inventory values ('135012', 'Canistel', 'Del Mar Produce  Merchandise Inventory', '1,269.28');
insert into DelMar_Produce_Merchandise_Inventory values ('135013', 'Feijoa', 'Del Mar Produce  Merchandise Inventory', '506.67');
insert into DelMar_Produce_Merchandise_Inventory values ('135014', 'Litchi', 'Del Mar Produce  Merchandise Inventory', '8696.56');
insert into DelMar_Produce_Merchandise_Inventory values ('135015', 'Longan', 'Del Mar Produce  Merchandise Inventory', '290.00');
insert into DelMar_Produce_Merchandise_Inventory values ('135016', 'Mamey Sapote', 'Del Mar Produce  Merchandise Inventory', '476.36');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137000', 'Parmigiano Reggiano', 'Del Mar Cheese & Dairy Merchandise Inventory', '279.61');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137001', 'Cheddar', 'Del Mar Cheese & Dairy Merchandise Inventory', '2,899.77');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137002', 'Brie', 'Del Mar Cheese & Dairy Merchandise Inventory', '362.71');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137003', 'Gouda', 'Del Mar Cheese & Dairy Merchandise Inventory', '294.40');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137004', 'Grana Padano', 'Del Mar Cheese & Dairy Merchandise Inventory', '437.43');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137005', 'Emmental', 'Del Mar Cheese & Dairy Merchandise Inventory', '276.48');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137006', 'Provolone', 'Del Mar Cheese & Dairy Merchandise Inventory', '653.98');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137007', 'Mozzarella', 'Del Mar Cheese & Dairy Merchandise Inventory', '696.46');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137008', 'Chevre', 'Del Mar Cheese & Dairy Merchandise Inventory', '1,778.05');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137009', 'Gruyere', 'Del Mar Cheese & Dairy Merchandise Inventory', '460.56');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137010', 'Gorgonzola', 'Del Mar Cheese & Dairy Merchandise Inventory', '707.75');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137011', 'Feta', 'Del Mar Cheese & Dairy Merchandise Inventory', '266.80');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137012', 'Stilton', 'Del Mar Cheese & Dairy Merchandise Inventory', '708.65');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137013', 'Asiago', 'Del Mar Cheese & Dairy Merchandise Inventory', '1,473.45');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137014', 'Roquefort', 'Del Mar Cheese & Dairy Merchandise Inventory', '331.71');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137015', 'Manchego', 'Del Mar Cheese & Dairy Merchandise Inventory', '464.53');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137016', 'Camembert', 'Del Mar Cheese & Dairy Merchandise Inventory', '264.59');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137017', 'Pecorino Romano', 'Del Mar Cheese & Dairy Merchandise Inventory', '513.33');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137018', 'Jarlsberg', 'Del Mar Cheese & Dairy Merchandise Inventory', '277.45');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137019', 'Maytag', 'Del Mar Cheese & Dairy Merchandise Inventory', '33,475.13');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137020', 'Ricotta', 'Del Mar Cheese & Dairy Merchandise Inventory', '853.87');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137021', 'Havarti', 'Del Mar Cheese & Dairy Merchandise Inventory', '315.98');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137022', 'Fontina', 'Del Mar Cheese & Dairy Merchandise Inventory', '851.76');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137023', 'Raclette', 'Del Mar Cheese & Dairy Merchandise Inventory', '253.34');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137024', 'Mascarpone', 'Del Mar Cheese & Dairy Merchandise Inventory', '304.85');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137025', 'Edam', 'Del Mar Cheese & Dairy Merchandise Inventory', '616.32');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137026', 'Limburger', 'Del Mar Cheese & Dairy Merchandise Inventory', '745.91');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137027', 'Aioli Sauce', 'Del Mar Cheese & Dairy Merchandise Inventory', '268.03');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137028', 'Beurre Barrratte de Celles Sur', 'Del Mar Cheese & Dairy Merchandise Inventory', '376.28');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137029', 'Beurremont 83% Butter', 'Del Mar Cheese & Dairy Merchandise Inventory', '278.38');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137030', 'Boursin', 'Del Mar Cheese & Dairy Merchandise Inventory', '7,935.99');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137031', 'Cabridoux', 'Del Mar Cheese & Dairy Merchandise Inventory', '367.66');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137032', 'Clotted Creme', 'Del Mar Cheese & Dairy Merchandise Inventory', '567.60');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137033', 'Danish Lurpak Butter', 'Del Mar Cheese & Dairy Merchandise Inventory', '361.69');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137034', 'Dulce de Leche', 'Del Mar Cheese & Dairy Merchandise Inventory', '252.40');
insert into DelMar_Cheese_Dairy_Merchandise_Inventory values ('137035', 'English Brandy Butter', 'Del Mar Cheese & Dairy Merchandise Inventory', '1,520.67');
insert into DelMar_Wine_Merchandise_Inventory values ('139000', 'Red Wines', 'Del Mar Wine Merchandise Inventory', '304.81');
insert into DelMar_Wine_Merchandise_Inventory values ('139001', 'White Wines', 'Del Mar Wine Merchandise Inventory', '117,030.98');
insert into DelMar_Wine_Merchandise_Inventory values ('139002', 'Blush Wines', 'Del Mar Wine Merchandise Inventory', '395.10');
insert into DelMar_Wine_Merchandise_Inventory values ('139003', 'Sparkling Wines', 'Del Mar Wine Merchandise Inventory', '384.17');
insert into DelMar_Wine_Merchandise_Inventory values ('139004', 'Dessert Wines', 'Del Mar Wine Merchandise Inventory', '748.40');
insert into DelMar_Wine_Merchandise_Inventory values ('139005', 'Fruit Wines', 'Del Mar Wine Merchandise Inventory', '303.39');
insert into DelMar_Wine_Merchandise_Inventory values ('139006', 'Rice Wines', 'Del Mar Wine Merchandise Inventory', '399.16');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141000', 'Rustic Baguette', 'Encinitas Bakery Department Merchandise Inventory', '511.72');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141001', 'Challah', 'Encinitas Bakery Department Merchandise Inventory', '473.86');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141002', 'Calamata Olive Bread', 'Encinitas Bakery Department Merchandise Inventory', '362.66');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141003', 'Ciabatta', 'Encinitas Bakery Department Merchandise Inventory', '478.83');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141004', 'Ficelle', 'Encinitas Bakery Department Merchandise Inventory', '950.16');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141005', 'Finnish Rye', 'Encinitas Bakery Department Merchandise Inventory', '349.38');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141006', 'Prussian Rye', 'Encinitas Bakery Department Merchandise Inventory', '332.16');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141007', 'Italian Sssemolina', 'Encinitas Bakery Department Merchandise Inventory', '775.23');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141008', 'Sourdough Rounds and Batar', 'Encinitas Bakery Department Merchandise Inventory', '363.85');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141009', 'Swiss Muesli', 'Encinitas Bakery Department Merchandise Inventory', '386.70');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141010', 'Focaccia', 'Encinitas Bakery Department Merchandise Inventory', '335.08');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141011', 'Honey Wheat', 'Encinitas Bakery Department Merchandise Inventory', '1,235.08');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141012', 'Agiago Cheese', 'Encinitas Bakery Department Merchandise Inventory', '561.14');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141013', 'French Croissants', 'Encinitas Bakery Department Merchandise Inventory', '2,517.00');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141014', 'Fruit Croissants', 'Encinitas Bakery Department Merchandise Inventory', '352.62');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141015', 'Danishs', 'Encinitas Bakery Department Merchandise Inventory', '278.53');
insert into Encinitas_Bakery_Dept_Merchandise_Inventory values ('141016', 'Fruit Tarts', 'Encinitas Bakery Department Merchandise Inventory', '286.07');  
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143000', 'Angus Beef', 'Encinitas Meat and Seafood Merchandise Inventory', '1,665.20');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143001', 'Wild Turkey', 'Encinitas Meat and Seafood Merchandise Inventory', '1,253.82');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143002', 'Duck', 'Encinitas Meat and Seafood Merchandise Inventory', '5,041.08');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143003', 'Pheasant', 'Encinitas Meat and Seafood Merchandise Inventory', '635.77');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143004', 'Quail', 'Encinitas Meat and Seafood Merchandise Inventory', '365.92');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143005', 'Ostrich', 'Encinitas Meat and Seafood Merchandise Inventory', '413.75');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143006', 'Partridge', 'Encinitas Meat and Seafood Merchandise Inventory', '529.24');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143007', 'Squab', 'Encinitas Meat and Seafood Merchandise Inventory', '2,471.77');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143008', 'Poussin', 'Encinitas Meat and Seafood Merchandise Inventory', '513.12');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143009', 'Guinea Fowl', 'Encinitas Meat and Seafood Merchandise Inventory', '5,031.91');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143010', 'Goose', 'Encinitas Meat and Seafood Merchandise Inventory', '252.16');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143011', 'Sausages', 'Encinitas Meat and Seafood Merchandise Inventory', '311.86');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143012', 'Lobster', 'Encinitas Meat and Seafood Merchandise Inventory', '589.10');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143013', 'Coho Salmon', 'Encinitas Meat and Seafood Merchandise Inventory', '423.49');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143014', 'Wild King Salmon', 'Encinitas Meat and Seafood Merchandise Inventory', '1,343.44');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143015', 'Copper River King Salmon', 'Encinitas Meat and Seafood Merchandise Inventory', '348.63');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143016', 'Red Snapper', 'Encinitas Meat and Seafood Merchandise Inventory', '458.53');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143017', 'Pacific Swordfish', 'Encinitas Meat and Seafood Merchandise Inventory', '1,896.75');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143018', 'Dungeness Crab', 'Encinitas Meat and Seafood Merchandise Inventory', '1,113.25');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143019', 'Oregon Sanddabs', 'Encinitas Meat and Seafood Merchandise Inventory', '285.16');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143020', 'Oysters', 'Encinitas Meat and Seafood Merchandise Inventory', '897.36');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143021', 'Caviar', 'Encinitas Meat and Seafood Merchandise Inventory', '250.61');
insert into Encinitas_Meat_Seafood_Merchandise_Inventory values ('143022', 'Beluga', 'Encinitas Meat and Seafood Merchandise Inventory', '252.94');
insert into Encinitas_Produce_Merchandise_Inventory values ('145000', 'Gala Apples', 'Encinitas Produce Merchandise Inventory', '758.18');
insert into Encinitas_Produce_Merchandise_Inventory values ('145001', 'Fuji Apples', 'Encinitas Produce Merchandise Inventory', '1,424.09');
insert into Encinitas_Produce_Merchandise_Inventory values ('145002', 'Cortland Apples', 'Encinitas Produce Merchandise Inventory', '423.26');
insert into Encinitas_Produce_Merchandise_Inventory values ('145003', 'Empire Apples', 'Encinitas Produce Merchandise Inventory', '284.55');
insert into Encinitas_Produce_Merchandise_Inventory values ('145004', 'McIntosh Apples', 'Encinitas Produce Merchandise Inventory', '501.02');
insert into Encinitas_Produce_Merchandise_Inventory values ('145005', 'Pink Lady Apples', 'Encinitas Produce Merchandise Inventory', '424.07');
insert into Encinitas_Produce_Merchandise_Inventory values ('145006', 'Northen Spy Apples', 'Encinitas Produce Merchandise Inventory', '1,050.70');
insert into Encinitas_Produce_Merchandise_Inventory values ('145007', 'Golden Delicious Apples', 'Encinitas Produce Merchandise Inventory', '850.05'); 
insert into Encinitas_Produce_Merchandise_Inventory values ('145008', 'Rhode Island Greening Apples', 'Encinitas Produce Merchandise Inventory', '3,457.13');
insert into Encinitas_Produce_Merchandise_Inventory values ('145009', 'Gravenstein Apples', 'Encinitas Produce Merchandise Inventory', '2,944.58');
insert into Encinitas_Produce_Merchandise_Inventory values ('145010', 'Atemoya', 'Encinitas Produce Merchandise Inventory', '921.44');
insert into Encinitas_Produce_Merchandise_Inventory values ('145011', 'Breadfruit', 'Encinitas Produce Merchandise Inventory', '4,951.69');
insert into Encinitas_Produce_Merchandise_Inventory values ('145012', 'Canistel', 'Encinitas Produce Merchandise Inventory', '582.16');
insert into Encinitas_Produce_Merchandise_Inventory values ('145013', 'Feijoa', 'Encinitas Produce Merchandise Inventory', '541.19');
insert into Encinitas_Produce_Merchandise_Inventory values ('145014', 'Litchi', 'Encinitas Produce Merchandise Inventory', '266.08');
insert into Encinitas_Produce_Merchandise_Inventory values ('145015', 'Longan', 'Encinitas Produce Merchandise Inventory', '827.25'); 
insert into Encinitas_Produce_Merchandise_Inventory values ('145016', 'Mamey Sapote', 'Encinitas Produce Merchandise Inventory', '877.22'); 

select * from Petty_Cash;
select * from Undeposited_Reciepts;
select * from Checking_Accounts;
select * from Bank_Accounts;
select * from Workmans_Comp;
select * from Accounts_Recievable;
select * from Reserve_Bad_Debt;
select * from LaJolla_Cheese_Dairy_Merchandise_Inventory;
select * from LaJolla_Wine_Merchandise_Inventory;
select * from DelMar_Bakery_Dept_Merchandise_Inventory;
select * from DelMar_Meat_Seafood_Merchandise_Inventory;
select * from DelMar_Produce_Merchandise_Inventory;
select * from DelMar_Cheese_Dairy_Merchandise_Inventory;
select * from DelMar_Wine_Merchandise_Inventory;
select * from Encinitas_Bakery_Dept_Merchandise_Inventory;
select * from Encinitas_Meat_Seafood_Merchandise_Inventory;
select * from Encinitas_Produce_Merchandise_Inventory;

use KudlerFineFoods
select AccountNumber 
from  dbo.LaJolla_Cheese_Dairy_Merchandise_Inventory;
GO
 
use KudlerFineFoods
select ShortDescr, SUM(Balance) as SubTotal
from dbo.LaJolla_Cheese_Dairy_Merchandise_Inventory 
group by ShortDescr
order by SubTotal desc
GO

use KudlerFineFoods
select SUM(Balance) as GrandTotal
from dbo.Petty_Cash, dbo.Undeposited_Reciepts, dbo.Checking_Accounts, dbo.Bank_Accounts,
dbo.Workmans_Comp, dbo.Accounts_Recievable, dbo.Reserve_Bad_Debt, dbo.LaJolla_Cheese_Dairy_Merchandise_Inventory,
dbo.LaJolla_Wine_Merchandise_Inventory, dbo.DelMar_Bakery_Dept_Merchandise_Inventory, dbo.DelMar_Meat_Seafood_Merchandise_Inventory,
dbo.DelMar_Produce_Merchandise_Inventory, dbo.DelMar_Cheese_Dairy_Merchandise_Inventory, 
dbo.DelMar_Wine_Merchandise_Inventory, dbo.Encinitas_Bakery_Dept_Merchandise_Inventory,
dbo.Encinitas_Meat_Seafood_Merchandise_Inventory, dbo.Encinitas_Produce_Merchandise_Inventory
order by GrandTotal;
go



Is This A Good Question/Topic? 0
  • +

Replies To: SQL JOIN Command

#2 girasquid  Icon User is offline

  • Barbarbar
  • member icon

Reputation: 108
  • View blog
  • Posts: 1,825
  • Joined: 03-October 06

Re: SQL JOIN Command

Posted 15 February 2008 - 07:42 PM

Where is your join statement? It'd be easier to help you if you trimmed down your query to just one line, it would be easier to tell where the problem is.

join syntax usually goes something like this:
SELECT t1.username, t2.password FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id


Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL JOIN Command

Posted 15 February 2008 - 07:45 PM

While I'm happy to look at your question, I first must ask; do you really want to do it like this?

Please consider this approach.
use KudlerFineFoods

create table AccountCategory (
AccountCategoryId int  PRIMARY KEY,
Description varchar (60)	  NOT NULL
);
GO

create table Account (
AccountCategoryId int NOT NULL,
AccountNumber varchar (6) NOT NULL,
Description varchar (60)  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money NOT NULL ,
constraint primary key (AccountCategoryId, AccountNumber),
);
GO

insert into AccountCategory values (1, 'Petty Cash');
insert into AccountCategory values (2, 'Undeposited Reciepts');
insert into AccountCategory values (3, 'Checking Accounts');

insert into Account values (1,'100001', 'Petty Cash', 'Ops Cash - La Jolla', '22,000.00');
insert into Account values (1,'100002', 'Petty Cash', 'Ops Cash - Del Mar', '36,000.00');
insert into Account values (1,'100003', 'Petty Cash', 'Ops Cash - Encinitas', '28,000.00');
insert into Account values (2,'100010', 'La Jolla Undeposited Receipts', 'Undeposited Receipts', '25,000.00');
insert into Account values (2,'100011', 'Del Mar Undeposited Receipts', 'Undeposited Receipts', '31,800.00');
insert into Account values (2,'100012', 'Encinitas Undeposited Receipts', 'Undeposited Receipts', 
insert into Account values (3,'100020', 'Bradley Bank', 'Checking Account - La Jolla', '150,000.00');
insert into Account values (3,'100021', 'Kelsey Bank', 'Checking Account - Del Mar', '167,000.00');
insert into Account values (3,'100022', 'Downey Bank', 'Checking Account - Encinitas', '125,000.00');



If this wont work for you, why? Seriously, it looks like you're causing yourself a tremendous amount of frustration for no reason.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

Re: SQL JOIN Command

Posted 15 February 2008 - 08:29 PM

View Postgirasquid, on 15 Feb, 2008 - 07:42 PM, said:

Where is your join statement? It'd be easier to help you if you trimmed down your query to just one line, it would be easier to tell where the problem is.

join syntax usually goes something like this:
SELECT t1.username, t2.password FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id



Thanks for your advise and help. I think I have it figured out.

View Postbaavgai, on 15 Feb, 2008 - 07:45 PM, said:

While I'm happy to look at your question, I first must ask; do you really want to do it like this?

Please consider this approach.
use KudlerFineFoods

create table AccountCategory (
AccountCategoryId int  PRIMARY KEY,
Description varchar (60)	  NOT NULL
);
GO

create table Account (
AccountCategoryId int NOT NULL,
AccountNumber varchar (6) NOT NULL,
Description varchar (60)  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money NOT NULL ,
constraint primary key (AccountCategoryId, AccountNumber),
);
GO

insert into AccountCategory values (1, 'Petty Cash');
insert into AccountCategory values (2, 'Undeposited Reciepts');
insert into AccountCategory values (3, 'Checking Accounts');

insert into Account values (1,'100001', 'Petty Cash', 'Ops Cash - La Jolla', '22,000.00');
insert into Account values (1,'100002', 'Petty Cash', 'Ops Cash - Del Mar', '36,000.00');
insert into Account values (1,'100003', 'Petty Cash', 'Ops Cash - Encinitas', '28,000.00');
insert into Account values (2,'100010', 'La Jolla Undeposited Receipts', 'Undeposited Receipts', '25,000.00');
insert into Account values (2,'100011', 'Del Mar Undeposited Receipts', 'Undeposited Receipts', '31,800.00');
insert into Account values (2,'100012', 'Encinitas Undeposited Receipts', 'Undeposited Receipts', 
insert into Account values (3,'100020', 'Bradley Bank', 'Checking Account - La Jolla', '150,000.00');
insert into Account values (3,'100021', 'Kelsey Bank', 'Checking Account - Del Mar', '167,000.00');
insert into Account values (3,'100022', 'Downey Bank', 'Checking Account - Encinitas', '125,000.00');



If this wont work for you, why? Seriously, it looks like you're causing yourself a tremendous amount of frustration for no reason.

Hope this helps.



This is my first shot at coding SQL and pretty much teaching myself. I haven't learned all the short methods, which is the reson for such a long code. I appreciate your help and advise.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL JOIN Command

Posted 16 February 2008 - 04:11 AM

View Postmconklin1108, on 15 Feb, 2008 - 10:29 PM, said:

This is my first shot at coding SQL and pretty much teaching myself. I haven't learned all the short methods, which is the reson for such a long code. I appreciate your help and advise.


No worries. I'd like to point you in the right direction, if I may.

Just a few questions: Is this a chart of accounts? Do you intend to have a ledger? Which database are you using?

This post has been edited by baavgai: 16 February 2008 - 04:13 AM

Was This Post Helpful? 0
  • +
  • -

#6 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

Re: SQL JOIN Command

Posted 16 February 2008 - 08:10 PM


This post has been edited by mconklin1108: 16 February 2008 - 08:27 PM

Was This Post Helpful? 0
  • +
  • -

#7 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

Re: SQL JOIN Command

Posted 16 February 2008 - 08:33 PM

View Postbaavgai, on 16 Feb, 2008 - 04:11 AM, said:

View Postmconklin1108, on 15 Feb, 2008 - 10:29 PM, said:

This is my first shot at coding SQL and pretty much teaching myself. I haven't learned all the short methods, which is the reson for such a long code. I appreciate your help and advise.


No worries. I'd like to point you in the right direction, if I may.

Just a few questions: Is this a chart of accounts? Do you intend to have a ledger? Which database are you using?


Quote

No worries. I'd like to point you in the right direction, if I may.

Just a few questions: Is this a chart of accounts? Do you intend to have a ledger? Which database are you using?


Thanks for the offer. This is a chart of accounts, though from a fake company that I got from a friend, just so I could learn how to code SQL. The software I am using is Microsoft SQL Developer 2005, which is not so user friendly. Anyway- I did take your advise and change the format of my code. Your suggestion made it easier to read and follow. Anyway-My next chanllenge is trying to use the JOIN Command in order to get a grand total balance of all account and inventories. I tried to use the one suggested but cannot get it to work.
Was This Post Helpful? 0
  • +
  • -

#8 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

Re: SQL JOIN Command

Posted 16 February 2008 - 08:43 PM

baavgai- Here is the code re-written in the format that you suggested. Thanks for your advise as it is helpful.



use KudlerFineFoods

create table AccountCategory (
AccountCategoryId int  PRIMARY KEY,
Description varchar (60)	  NOT NULL
);
GO

create table Account (
AccountCategoryId int NOT NULL,
AccountNumber varchar (6) NOT NULL,
Description varchar (60)  NOT NULL,
ShortDescr varchar (60)	   NOT NULL,
Balance money NOT NULL ,
constraint primary key (AccountCategoryId, AccountNumber),
);
GO

insert into AccountCategory values (1, 'Petty Cash');
insert into AccountCategory values (2, 'Undeposited Reciepts');
insert into AccountCategory values (3, 'Checking Accounts');
insert into AccountCategory values (4, 'Bank Accounts');
insert into AccountCategory values (5, 'Workmans Comp');
insert into AccountCategory values (6, 'Accounts Recievable');
insert into AccountCategory values (7, 'Reserve Bad Debt');
insert into AccountCategory values (8, 'LaJolla Cheese Dairy Merchandise Inventory');
insert into AccountCategory values (9, 'LaJolla Wine Merchandise Inventory');
insert into AccountCategory values (10, 'DelMar Bakery Dept Merchandise Inventory');
insert into AccountCategory values (11, 'DelMar Meat Seafood Merchandise Inventoryt');
insert into AccountCategory values (12, 'DelMar Produce Merchandise Inventory');
insert into AccountCategory values (13, 'DelMar Cheese Dairy Merchandise Inventory');
insert into AccountCategory values (14, 'DelMar Wine Merchandise Inventory');
insert into AccountCategory values (15, 'Encinitas Bakery Dept Merchandise Inventory');
insert into AccountCategory values (16, 'Encinitas Meat Seafood Merchandise Inventory');
insert into AccountCategory values (17, 'Encinitas Produce Merchandise Inventory');

insert into Account values (1,'100001', 'Petty Cash', 'Ops Cash - La Jolla', '22,000.00');
insert into Account values (1,'100002', 'Petty Cash', 'Ops Cash - Del Mar', '36,000.00');
insert into Account values (1,'100003', 'Petty Cash', 'Ops Cash - Encinitas', '28,000.00');

insert into Account values (2,'100010', 'La Jolla Undeposited Receipts', 'Undeposited Receipts', '25,000.00');
insert into Account values (2,'100011', 'Del Mar Undeposited Receipts', 'Undeposited Receipts', '31,800.00');
insert into Account values (2,'100012', 'Encinitas Undeposited Receipts', 'Undeposited Receipts', 

insert into Account values (3,'100020', 'Bradley Bank', 'Checking Account - La Jolla', '150,000.00');
insert into Account values (3,'100021', 'Kelsey Bank', 'Checking Account - Del Mar', '167,000.00');
insert into Account values (3,'100022', 'Downey Bank', 'Checking Account - Encinitas', '125,000.00');

insert into Account values (4,'100030', 'La Jolla Payroll', 'Bank Account', '100,000.00');
insert into Account values (4,'100031', 'Del Mar Payroll', 'Bank Account', '100,000.00');
insert into Account values (4,'100032', 'Encinitas Payroll', 'Bank Account', '100,000.00');

insert into Account values (5,'100040', 'La Jolla - Workers Comp', 'Workers Comp', '75,000.00');
insert into Account values (5,'100041', 'Del Mar - Workers Comp', 'Workers Comp', '80,000.00');
insert into Account values (5,'100042', 'Encinitas - Workers Comp', 'Workers Comp', '82,000.00');

insert into Account values (6,'111000', 'Accounts Receivable', 'La Jolla', '21,000.00');
insert into Account values (6,'112000', 'Accounts Receivable', 'Del Mar', '34,000.00');
insert into Account values (6,'113000', 'Accounts Receivable', 'Encinitas', '45,000.00');

insert into Account values (7,'111001', 'Reserve - Bad Credit', 'La Jolla', '45,000.00');
insert into Account values (7,'112001', 'Reserve - Bad Credit', 'Del Mar', '55,000.00');
insert into Account values (7,'113001', 'Reserve - Bad Credit', 'Encinitas', '62,000.00');

insert into Account values (8,'127000', 'Parmigiano Reggiano', 'La Jolla Cheese & Dairy Merchandise Inventory', '494.50');
insert into Account values (8,'127001', 'Cheddar', 'La Jolla Cheese & Dairy Merchandise Inventory', '556.90');
insert into Account values (8,'127002', 'Brie', 'La Jolla Cheese & Dairy Merchandise Inventory', '1,019.83');
insert into Account values (8,'127003', 'Gouda', 'La Jolla Cheese & Dairy Merchandise Inventory', '740.97');
insert into Account values (8,'127004', 'Grana Padano', 'La Jolla Cheese & Dairy Merchandise Inventory', '607.78');
insert into Account values (8,'127005', 'Emmental', 'La Jolla Cheese & Dairy Merchandise Inventory', '307.86');
insert into Account values (8,'127006', 'Provolone', 'La Jolla Cheese & Dairy Merchandise Inventory', '335.21');
insert into Account values (8,'127007', 'Mozzarella', 'La Jolla Cheese & Dairy Merchandise Inventory', '489.27');
insert into Account values (8,'127008', 'Chevre', 'La Jolla Cheese & Dairy Merchandise Inventory', '576.65');
insert into Account values (8,'127009', 'Gruyere', 'La Jolla Cheese & Dairy Merchandise Inventory', '310.16');
insert into Account values (8,'127010', 'Gorgonzola', 'La Jolla Cheese & Dairy Merchandise Inventory', '385.18');
insert into Account values (8,'127011', 'Feta', 'La Jolla Cheese & Dairy Merchandise Inventory', '1,446.62');
insert into Account values (8,'127012', 'Stilton', 'La Jolla Cheese & Dairy Merchandise Inventory', '251.08');
insert into Account values (8,'127013', 'Asiago', 'La Jolla Cheese & Dairy Merchandise Inventory', '265.58');
insert into Account values (8,'127014', 'Roquefort', 'La Jolla Cheese & Dairy Merchandise Inventory', '700.46');
insert into Account values (8,'127015', 'Manchego', 'La Jolla Cheese & Dairy Merchandise Inventory', '634.94');
insert into Account values (8,'127016', 'Camembert', 'La Jolla Cheese & Dairy Merchandise Inventory', '368.82');
insert into Account values (8,'127017', 'Pecorino Romano', 'La Jolla Cheese & Dairy Merchandise Inventory', '778.70');
insert into Account values (8,'127018', 'Jarlsberg', 'La Jolla Cheese & Dairy Merchandise Inventory', '262.21');
insert into Account values (8,'127019', 'Maytag', 'La Jolla Cheese & Dairy Merchandise Inventory', '314.00');
insert into Account values (8,'127020', 'Ricotta', 'La Jolla Cheese & Dairy Merchandise Inventory', '935.11');
insert into Account values (8,'127021', 'Havarti', 'La Jolla Cheese & Dairy Merchandise Inventory', '287.86');
insert into Account values (8,'127022', 'Fontina', 'La Jolla Cheese & Dairy Merchandise Inventory', '564.24');
insert into Account values (8,'127023', 'Raclette', 'La Jolla Cheese & Dairy Merchandise Inventory', '361.97');
insert into Account values (8,'127024', 'Mascarpone', 'La Jolla Cheese & Dairy Merchandise Inventory', '336.56');
insert into Account values (8,'127025', 'Edam', 'La Jolla Cheese & Dairy Merchandise Inventory', '511.42');
insert into Account values (8,'127026', 'Limburger', 'La Jolla Cheese & Dairy Merchandise Inventory', '296.34');
insert into Account values (8,'127027', 'Aioli Sauce', 'La Jolla Cheese & Dairy Merchandise Inventory', '261.69');
insert into Account values (8,'127028', 'Beurre Barratte de Celles Sur', 'La Jolla Cheese & Dairy Merchandise Inventory', '288.12');
insert into Account values (8,'127029', 'Beurremont 83% Butter', 'La Jolla Cheese & Dairy Merchandise Inventory', '823.82');
insert into Account values (8,'127030', 'Boursin', 'La Jolla Cheese & Dairy Merchandise Inventory', '423.91');
insert into Account values (8,'127031', 'Cabridoux', 'La Jolla Cheese & Dairy Merchandise Inventory', '540.44');
insert into Account values (8,'127032', 'Clotted Creme', 'La Jolla Cheese & Dairy Merchandise Inventory', '625.71');
insert into Account values (8,'127033', 'Danish Lurpak Butter', 'La Jolla Cheese & Dairy Merchandise Inventory', '273.89');
insert into Account values (8,'127034', 'Dulce de Leche', 'La Jolla Cheese & Dairy Merchandise Inventory', '2,456.18');
insert into Account values (8,'127035', 'English Brandy Butter', 'La Jolla Cheese & Dairy Merchandise Inventory', '315.09');

insert into Account values (9,'129001', 'Blush Wines', 'La Jolla Wine Merchandise Inventory', '2,541.00');
insert into Account values (9,'129002', 'Dessert Wines', 'La Jolla Wine Merchandise Inventory', '6,750.34');
insert into Account values (9,'129003', 'Fruit Wines', 'La Jolla Wine Merchandise Inventory', '9,334.00');
insert into Account values (9,'129004', 'Red Wines', 'La Jolla Wine Merchandise Inventory', '8,903,00');
insert into Account values (9,'129005', 'Rice Wines', 'La Jolla Wine Merchandise Inventory', '2,345.99');
insert into Account values (9,'129006', 'Sparkling Wines', 'La Jolla Wine Merchandise Inventory', '6,543.00');
insert into Account values (9,'129007', 'White Wines', 'La Jolla Wine Merchandise Inventory', '4,234.00');

insert into Account values (10,'129008', 'Agiago Cheese', 'Del Mar Bakery Department Merchandise Inventory', '4,456.00');
insert into Account values (10,'129009', 'Calamata Olive Bread', 'Del Mar Bakery Department Merchandise Inventory', '2,345.00');
insert into Account values (10,'129010', 'Challah', 'Del Mar Bakery Department Merchandise Inventory', '3,230.00');
insert into Account values (10,'129011', 'Ciabatta', 'Del Mar Bakery Department Merchandise Inventory', '1,232.00');
insert into Account values (10,'129012', 'Danishs', 'Del Mar Bakery Department Merchandise Inventory', '987.67');
insert into Account values (10,'129013', 'Ficelle', 'Del Mar Bakery Department Merchandise Inventory', '234.89');
insert into Account values (10,'129014', 'Finnish Rye', 'Del Mar Bakery Department Merchandise Inventory', '1,234.56');
insert into Account values (10,'129015', 'Focaccia', 'Del Mar Bakery Department Merchandise Inventory', '4,567.34');
insert into Account values (10,'129016', 'French Croissants', 'Del Mar Bakery Department Merchandise Inventory', '7,567.00');
insert into Account values (10,'129017', 'Fruit Croissants', 'Del Mar Bakery Department Merchandise Inventory', '345.89');
insert into Account values (10,'129018', 'Fruit Tarts', 'Del Mar Bakery Department Merchandise Inventory', '880.56');
insert into Account values (10,'129019', 'Honey Wheat', 'Del Mar Bakery Department Merchandise Inventory', '5,670.98');
insert into Account values (10,'129020', 'Italian Sssemolina', 'Del Mar Bakery Department Merchandise Inventory', '6,564.00');
insert into Account values (10,'129021', 'Prussian Rye', 'Del Mar Bakery Department Merchandise Inventory', '456.90');
insert into Account values (10,'131000', 'Rustic Baguette', 'Del Mar Bakery Department Merchandise Inventory', '110.00');
insert into Account values (10,'131001', 'Sourdough Rounds & Batar', 'Del Mar Bakery Department Merchandise Inventory', '435.89');
insert into Account values (10,'131002', 'Swiss Muesli', 'Del Mar Bakery Department Merchandise Inventory', '123.00');

insert into Account values (11,'133000', 'Angus Beef', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,560.96');
insert into Account values (11,'133001', 'Beluga', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,344.00');
insert into Account values (11,'133002', 'Caviar', 'Del Mar Meat and Seafood  Merchandise Inventory', '6,789.78');
insert into Account values (11,'133003', 'Coho Salmon', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,289.78');
insert into Account values (11,'133004', 'Copper River King Salmon', 'Del Mar Meat and Seafood  Merchandise Inventory', '1,232.00');
insert into Account values (11,'133005', 'Duck', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,345.99');
insert into Account values (11,'133006', 'Dungeness Crab', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,442.55');
insert into Account values (11,'133007', 'Goose', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,343.00');
insert into Account values (11,'133008', 'Guinea Fowl', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,331.10');
insert into Account values (11,'133009', 'Lobster', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,590.89');
insert into Account values (11,'133010', 'Oregon Sanddabs', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,890.99');
insert into Account values (11,'133011', 'Ostrich', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,234.00');
insert into Account values (11,'133012', 'Oysters', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,555.00');
insert into Account values (11,'133013', 'Pacific Swordfish', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,453.99');
insert into Account values (11,'133014', 'Partridge', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,678.00');
insert into Account values (11,'133015', 'Pheasant', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,899.00');
insert into Account values (11,'133016', 'Poussin', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,663.00');
insert into Account values (11,'133017', 'Quail', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,567.99');
insert into Account values (11,'133018', 'Red Snapper', 'Del Mar Meat and Seafood  Merchandise Inventory', '5,567.99');
insert into Account values (11,'133019', 'Sausages', 'Del Mar Meat and Seafood  Merchandise Inventory', '3,456.99');
insert into Account values (11,'133020', 'Squab', 'Del Mar Meat and Seafood  Merchandise Inventory', '4,564.00');
insert into Account values (11,'133021', 'Wild King Salmaon', 'Del Mar Meat and Seafood  Merchandise Inventory', '2,345.65');
insert into Account values (11,'133022', 'Wild Turkey', 'Del Mar Meat and Seafood  Merchandise Inventory', '1,233.54');

insert into Account values (12,'135000', 'Gala Apples', 'Del Mar Produce  Merchandise Inventory', '941.87');
insert into Account values (12,'135001', 'Fuji Apples', 'Del Mar Produce  Merchandise Inventory', '282.38');
insert into Account values (12,'135002', 'Cortland Apples', 'Del Mar Produce  Merchandise Inventory', '529.26');
insert into Account values (12,'135003', 'Empire Apples', 'Del Mar Produce  Merchandise Inventory', '1,440.37');
insert into Account values (12,'135004', 'McIntosh Apples', 'Del Mar Produce  Merchandise Inventory', '593.74');
insert into Account values (12,'135005', 'Pink Lady Apples', 'Del Mar Produce  Merchandise Inventory', '365.93');
insert into Account values (12,'135006', 'Northen Spy Apples', 'Del Mar Produce  Merchandise Inventory', '415.55');
insert into Account values (12,'135007', 'Golden Delicious Apples', 'Del Mar Produce  Merchandise Inventory', '551.25');
insert into Account values (12,'135008', 'Rhode Island Greening Apples', 'Del Mar Produce  Merchandise Inventory', '6,333.53');
insert into Account values (12,'135009', 'Gravenstein Apples', 'Del Mar Produce  Merchandise Inventory', '251.61');
insert into Account values (12,'135010', 'Atemoya', 'Del Mar Produce  Merchandise Inventory', '442.34');
insert into Account values (12,'135011', 'Breadfruit', 'Del Mar Produce  Merchandise Inventory', '331.39');
insert into Account values (12,'135012', 'Canistel', 'Del Mar Produce  Merchandise Inventory', '1,269.28');
insert into Account values (12,'135013', 'Feijoa', 'Del Mar Produce  Merchandise Inventory', '506.67');
insert into Account values (12,'135014', 'Litchi', 'Del Mar Produce  Merchandise Inventory', '8696.56');
insert into Account values (12,'135015', 'Longan', 'Del Mar Produce  Merchandise Inventory', '290.00');
insert into Account values (12,'135016', 'Mamey Sapote', 'Del Mar Produce  Merchandise Inventory', '476.36');

insert into Account values (13,'137000', 'Parmigiano Reggiano', 'Del Mar Cheese & Dairy Merchandise Inventory', '279.61');
insert into Account values (13,'137001', 'Cheddar', 'Del Mar Cheese & Dairy Merchandise Inventory', '2,899.77');
insert into Account values (13,'137002', 'Brie', 'Del Mar Cheese & Dairy Merchandise Inventory', '362.71');
insert into Account values (13,'137003', 'Gouda', 'Del Mar Cheese & Dairy Merchandise Inventory', '294.40');
insert into Account values (13,'137004', 'Grana Padano', 'Del Mar Cheese & Dairy Merchandise Inventory', '437.43');
insert into Account values (13,'137005', 'Emmental', 'Del Mar Cheese & Dairy Merchandise Inventory', '276.48');
insert into Account values (13,'137006', 'Provolone', 'Del Mar Cheese & Dairy Merchandise Inventory', '653.98');
insert into Account values (13,'137007', 'Mozzarella', 'Del Mar Cheese & Dairy Merchandise Inventory', '696.46');
insert into Account values (13,'137008', 'Chevre', 'Del Mar Cheese & Dairy Merchandise Inventory', '1,778.05');
insert into Account values (13,'137009', 'Gruyere', 'Del Mar Cheese & Dairy Merchandise Inventory', '460.56');
insert into Account values (13,'137010', 'Gorgonzola', 'Del Mar Cheese & Dairy Merchandise Inventory', '707.75');
insert into Account values (13,'137011', 'Feta', 'Del Mar Cheese & Dairy Merchandise Inventory', '266.80');
insert into Account values (13,'137012', 'Stilton', 'Del Mar Cheese & Dairy Merchandise Inventory', '708.65');
insert into Account values (13,'137013', 'Asiago', 'Del Mar Cheese & Dairy Merchandise Inventory', '1,473.45');
insert into Account values (13,'137014', 'Roquefort', 'Del Mar Cheese & Dairy Merchandise Inventory', '331.71');
insert into Account values (13,'137015', 'Manchego', 'Del Mar Cheese & Dairy Merchandise Inventory', '464.53');
insert into Account values (13,'137016', 'Camembert', 'Del Mar Cheese & Dairy Merchandise Inventory', '264.59');
insert into Account values (13,'137017', 'Pecorino Romano', 'Del Mar Cheese & Dairy Merchandise Inventory', '513.33');
insert into Account values (13,'137018', 'Jarlsberg', 'Del Mar Cheese & Dairy Merchandise Inventory', '277.45');
insert into Account values (13,'137019', 'Maytag', 'Del Mar Cheese & Dairy Merchandise Inventory', '33,475.13');
insert into Account values (13,'137020', 'Ricotta', 'Del Mar Cheese & Dairy Merchandise Inventory', '853.87');
insert into Account values (13,'137021', 'Havarti', 'Del Mar Cheese & Dairy Merchandise Inventory', '315.98');
insert into Account values (13,'137022', 'Fontina', 'Del Mar Cheese & Dairy Merchandise Inventory', '851.76');
insert into Account values (13,'137023', 'Raclette', 'Del Mar Cheese & Dairy Merchandise Inventory', '253.34');
insert into Account values (13,'137024', 'Mascarpone', 'Del Mar Cheese & Dairy Merchandise Inventory', '304.85');
insert into Account values (13,'137025', 'Edam', 'Del Mar Cheese & Dairy Merchandise Inventory', '616.32');
insert into Account values (13,'137026', 'Limburger', 'Del Mar Cheese & Dairy Merchandise Inventory', '745.91');
insert into Account values (13,'137027', 'Aioli Sauce', 'Del Mar Cheese & Dairy Merchandise Inventory', '268.03');
insert into Account values (13,'137028', 'Beurre Barrratte de Celles Sur', 'Del Mar Cheese & Dairy Merchandise Inventory', '376.28');
insert into Account values (13,'137029', 'Beurremont 83% Butter', 'Del Mar Cheese & Dairy Merchandise Inventory', '278.38');
insert into Account values (13,'137030', 'Boursin', 'Del Mar Cheese & Dairy Merchandise Inventory', '7,935.99');
insert into Account values (13,'137031', 'Cabridoux', 'Del Mar Cheese & Dairy Merchandise Inventory', '367.66');
insert into Account values (13,'137032', 'Clotted Creme', 'Del Mar Cheese & Dairy Merchandise Inventory', '567.60');
insert into Account values (13,'137033', 'Danish Lurpak Butter', 'Del Mar Cheese & Dairy Merchandise Inventory', '361.69');
insert into Account values (13,'137034', 'Dulce de Leche', 'Del Mar Cheese & Dairy Merchandise Inventory', '252.40');
insert into Account values (13,'137035', 'English Brandy Butter', 'Del Mar Cheese & Dairy Merchandise Inventory', '1,520.67');

insert into Account values (14,'139000', 'Red Wines', 'Del Mar Wine Merchandise Inventory', '304.81');
insert into Account values (14,'139001', 'White Wines', 'Del Mar Wine Merchandise Inventory', '117,030.98');
insert into Account values (14,'139002', 'Blush Wines', 'Del Mar Wine Merchandise Inventory', '395.10');
insert into Account values (14,'139003', 'Sparkling Wines', 'Del Mar Wine Merchandise Inventory', '384.17');
insert into Account values (14,'139004', 'Dessert Wines', 'Del Mar Wine Merchandise Inventory', '748.40');
insert into Account values (14,'139005', 'Fruit Wines', 'Del Mar Wine Merchandise Inventory', '303.39');
insert into Account values (14,'139006', 'Rice Wines', 'Del Mar Wine Merchandise Inventory', '399.16');

insert into Account values (15,'141000', 'Rustic Baguette', 'Encinitas Bakery Department Merchandise Inventory', '511.72');
insert into Account values (15,'141001', 'Challah', 'Encinitas Bakery Department Merchandise Inventory', '473.86');
insert into Account values (15,'141002', 'Calamata Olive Bread', 'Encinitas Bakery Department Merchandise Inventory', '362.66');
insert into Account values (15,'141003', 'Ciabatta', 'Encinitas Bakery Department Merchandise Inventory', '478.83');
insert into Account values (15,'141004', 'Ficelle', 'Encinitas Bakery Department Merchandise Inventory', '950.16');
insert into Account values (15,'141005', 'Finnish Rye', 'Encinitas Bakery Department Merchandise Inventory', '349.38');
insert into Account values (15,'141006', 'Prussian Rye', 'Encinitas Bakery Department Merchandise Inventory', '332.16');
insert into Account values (15,'141007', 'Italian Sssemolina', 'Encinitas Bakery Department Merchandise Inventory', '775.23');
insert into Account values (15,'141008', 'Sourdough Rounds and Batar', 'Encinitas Bakery Department Merchandise Inventory', '363.85');
insert into Account values (15,'141009', 'Swiss Muesli', 'Encinitas Bakery Department Merchandise Inventory', '386.70');
insert into Account values (15,'141010', 'Focaccia', 'Encinitas Bakery Department Merchandise Inventory', '335.08');
insert into Account values (15,'141011', 'Honey Wheat', 'Encinitas Bakery Department Merchandise Inventory', '1,235.08');
insert into Account values (15,'141012', 'Agiago Cheese', 'Encinitas Bakery Department Merchandise Inventory', '561.14');
insert into Account values (15,'141013', 'French Croissants', 'Encinitas Bakery Department Merchandise Inventory', '2,517.00');
insert into Account values (15,'141014', 'Fruit Croissants', 'Encinitas Bakery Department Merchandise Inventory', '352.62');
insert into Account values (15,'141015', 'Danishs', 'Encinitas Bakery Department Merchandise Inventory', '278.53');
insert into Account values (15,'141016', 'Fruit Tarts', 'Encinitas Bakery Department Merchandise Inventory', '286.07');

insert into Account values (16,'143000', 'Angus Beef', 'Encinitas Meat and Seafood Merchandise Inventory', '1,665.20');
insert into Account values (16,'143001', 'Wild Turkey', 'Encinitas Meat and Seafood Merchandise Inventory', '1,253.82');
insert into Account values (16,'143002', 'Duck', 'Encinitas Meat and Seafood Merchandise Inventory', '5,041.08');
insert into Account values (16,'143003', 'Pheasant', 'Encinitas Meat and Seafood Merchandise Inventory', '635.77');
insert into Account values (16,'143004', 'Quail', 'Encinitas Meat and Seafood Merchandise Inventory', '365.92');
insert into Account values (16,'143005', 'Ostrich', 'Encinitas Meat and Seafood Merchandise Inventory', '413.75');
insert into Account values (16,'143006', 'Partridge', 'Encinitas Meat and Seafood Merchandise Inventory', '529.24');
insert into Account values (16,'143007', 'Squab', 'Encinitas Meat and Seafood Merchandise Inventory', '2,471.77');
insert into Account values (16,'143008', 'Poussin', 'Encinitas Meat and Seafood Merchandise Inventory', '513.12');
insert into Account values (16,'143009', 'Guinea Fowl', 'Encinitas Meat and Seafood Merchandise Inventory', '5,031.91');
insert into Account values (16,'143010', 'Goose', 'Encinitas Meat and Seafood Merchandise Inventory', '252.16');
insert into Account values (16,'143011', 'Sausages', 'Encinitas Meat and Seafood Merchandise Inventory', '311.86');
insert into Account values (16,'143012', 'Lobster', 'Encinitas Meat and Seafood Merchandise Inventory', '589.10');
insert into Account values (16,'143013', 'Coho Salmon', 'Encinitas Meat and Seafood Merchandise Inventory', '423.49');
insert into Account values (16,'143014', 'Wild King Salmon', 'Encinitas Meat and Seafood Merchandise Inventory', '1,343.44');
insert into Account values (16,'143015', 'Copper River King Salmon', 'Encinitas Meat and Seafood Merchandise Inventory', '348.63');
insert into Account values (16,'143016', 'Red Snapper', 'Encinitas Meat and Seafood Merchandise Inventory', '458.53');
insert into Account values (16,'143017', 'Pacific Swordfish', 'Encinitas Meat and Seafood Merchandise Inventory', '1,896.75');
insert into Account values (16,'143018', 'Dungeness Crab', 'Encinitas Meat and Seafood Merchandise Inventory', '1,113.25');
insert into Account values (16,'143019', 'Oregon Sanddabs', 'Encinitas Meat and Seafood Merchandise Inventory', '285.16');
insert into Account values (16,'143020', 'Oysters', 'Encinitas Meat and Seafood Merchandise Inventory', '897.36');
insert into Account values (16,'143021', 'Caviar', 'Encinitas Meat and Seafood Merchandise Inventory', '250.61');
insert into Account values (16,'143022', 'Beluga', 'Encinitas Meat and Seafood Merchandise Inventory', '252.94');

insert into Account values (17,'145000', 'Gala Apples', 'Encinitas Produce Merchandise Inventory', '758.18');
insert into Account values (17,'145001', 'Fuji Apples', 'Encinitas Produce Merchandise Inventory', '1,424.09');
insert into Account values (17,'145002', 'Cortland Apples', 'Encinitas Produce Merchandise Inventory', '423.26');
insert into Account values (17,'145003', 'Empire Apples', 'Encinitas Produce Merchandise Inventory', '284.55');
insert into Account values (17,'145004', 'McIntosh Apples', 'Encinitas Produce Merchandise Inventory', '501.02');
insert into Account values (17,'145005', 'Pink Lady Apples', 'Encinitas Produce Merchandise Inventory', '424.07');
insert into Account values (17,'145006', 'Northen Spy Apples', 'Encinitas Produce Merchandise Inventory', '1,050.70');
insert into Account values (17,'145007', 'Golden Delicious Apples', 'Encinitas Produce Merchandise Inventory', '850.05'); 
insert into Account values (17,'145008', 'Rhode Island Greening Apples', 'Encinitas Produce Merchandise Inventory', '3,457.13');
insert into Account values (17,'145009', 'Gravenstein Apples', 'Encinitas Produce Merchandise Inventory', '2,944.58');
insert into Account values (17,'145010', 'Atemoya', 'Encinitas Produce Merchandise Inventory', '921.44');
insert into Account values (17,'145011', 'Breadfruit', 'Encinitas Produce Merchandise Inventory', '4,951.69');
insert into Account values (17,'145012', 'Canistel', 'Encinitas Produce Merchandise Inventory', '582.16');
insert into Account values (17,'145013', 'Feijoa', 'Encinitas Produce Merchandise Inventory', '541.19');
insert into Account values (17,'145014', 'Litchi', 'Encinitas Produce Merchandise Inventory', '266.08');
insert into Account values (17,'145015', 'Longan', 'Encinitas Produce Merchandise Inventory', '827.25'); 
insert into Account values (17,'145016', 'Mamey Sapote', 'Encinitas Produce Merchandise Inventory', '877.22');



Was This Post Helpful? 0
  • +
  • -

#9 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL JOIN Command

Posted 16 February 2008 - 09:33 PM

Cool. So, here's the grand total. ;)

select sum(Balance) as GrandTotal from Account



Here are the Totals by AccountCategory:
select AccountCategoryId, sum(Balance) as Total
	from Account
	group by AccountCategoryId



Here are the Totals by AccountCategory, but more human readable:
select b.Description as CategoryDescription, count(*) as Entries, sum(a.Balance) as Total
	from Account a
		inner join AccountCategory b
			on a.AccountCategoryId=b.AccountCategoryId
	group by AccountCategoryId



Also, for Microsoft, look into the IDENTITY column types. The general form is to have an id column for nearly all tables, giving you a simple integer value to join the pieces together with.

Things you can do that will help, or at least teach. Make AccountCategoryId an identity. Put a unique index on Description. In Account, make AccountCategoryId a foreign key.

The caveat here is that if you make an auto generated value on a column, you'll have to get that value back out to do something with it. For example,

insert into AccountCategory values ('Petty Cash');

insert into Account
	select AccountCategoryId,'100001', 'Petty Cash', 'Ops Cash - La Jolla', 22,000.00
		from AccountCategory
		where Description='Petty Cash';



Have fun.
Was This Post Helpful? 0
  • +
  • -

#10 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

Re: SQL JOIN Command

Posted 16 February 2008 - 09:54 PM

Quote

Have fun.

baavgai
I am definitly having fun with this stuff. I made a database of my son's Hockey team using age, dates they started playing, city each child lives in and other information. Then I wrote different queries to see if I could pull particular information. Pretty cool stuff- though definitly need some kind resource book to get ideas from instead of trying to search the web for certain code, which can take time.

Again thanks for the help and great advise and of course with the Grand Total., this one had me puzzled. This Microsoft software developer is hard to please and I was surprised that it did not come with any instructions, just 8 CD ROMS. One questions though, I am somewhat familiar to coding c,c++,Java. In those editors, the user can add line numbers to the code to indentify errors faster. Is this possible for SQL Developer 2005. I have search the help menu and the web and cannot find any infomation. Just Wondering??
Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL JOIN Command

Posted 17 February 2008 - 06:17 AM

Sounds like you're looking for some kind of debugging tool for SQL? Sorry, they're all pitiful.

A sql statement is a complete expression, not series of sequential instructions like a computer program. There's not really anywhere to put some kind of flag or feedback; but it's worse than that. You create a statement and send it to the server, then you wait for a response. The server parses the statement, validating syntax; if the parse fails you get back a bad syntax message. If it parsed, then it's a valid expression and the server runs it. You get back either a valid result set or an exception, the reason why a valid result set could not be provided. You can't really trace that, because it's not in your hands.

There are some tools that will allow you to "tune" sql statements. More complex sql can develop bottlenecks in unexpected places. That assumes you have a working statement that just isn't doing the best thing, so not as helpful for a learning tool.

There are also tools that can help you write sql. In Microsoft's tools, there should be something called a query designer. It should have some kind of GUI interface where you pick some tables, show how you want them linked together or grouped and have the thing spit out sql for you. If you define primary and foreign key relationships, such tools work better.

I'm afraid I've not played with SQL Developer 2005, so I can't give specifics. Once you know sql, you tend not to used the query gui tools anyway, they write messy code. However, they also can show you how it's done. So they're great for training. There must be some such tool in what you have, you just have to find it. :P
Was This Post Helpful? 0
  • +
  • -

#12 mconklin1108  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 06-September 07

Re: SQL JOIN Command

Posted 17 February 2008 - 06:45 PM

Quote

I'm afraid I've not played with SQL Developer 2005, so I can't give specifics. Once you know sql, you tend not to used the query gui tools anyway, they write messy code. However, they also can show you how it's done. So they're great for training. There must be some such tool in what you have, you just have to find it. :P



It seems to be that way. That is what I get for trying something new. I was able to get my code above to execute just fine and I owe you all the thanks. Learned a lot. Had a couple of errors that was hard to find with just msg errors but was able to locate and correct.

One more question if you don't mind. I was playing around with my other code, the one I made for my son's hockey team. I was wanting to create breaks in the way it display the information after execution. my goal is to break up the information between my "Right/Left Wing", "Centers" and "Defense" Each group has a different numbers to tell them apart. What I mean by this ,is that for example- "Wings" are 1-19, "Centers" are 20-29 and "Defense" is 30-39.

When I run my query, I want to be able to apply a break between each group. The reason for my madness is that I can then group the other teams by numbers and create matchups before we hit the ice. I know--competitve father. I search the web and was able to find the following information

Quote

To produce grouping of items as in a control break report, use the BREAK ON command, in conjunction with the ORDER BY option in the SELECT command.

Break on Item skip 2

SQL will examine each row and track the value in Item. When the value for Item changes, SQL will skip two lines. Also the value for Item is printed on the first line of its section to eliminate duplicate printing of each of these items for every row in each section.


I think I understand what this is saying but when I code it out, it seems to want a WHILE in the mix.
CONFUSED!!!
Was This Post Helpful? 0
  • +
  • -

#13 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL JOIN Command

Posted 17 February 2008 - 07:40 PM

Sounds like you're looking for some kind of Report Writer.

SQL selects return result sets, a table of cells, made up of rows and columns; that's it. Now, there are games you can play with values in those cells, like left padding them based on same criteria, but it's really just a hack.

If I were tasked with making some presentable report based on database data and I didn't have a report writer, I'd reach for Excel. If it was for me, I'd use the spreadsheet in OpenOffice.org, for basically the same effect. You can format your heart out in those spreadsheets.

If you're a web type person, you can use HTML for your reports. You can even make SQL Server dump out data in an XML format, which gets you most of the way there. In simplest form, you can dump out the rows as concatenated strings with "<td>" tags and such.

I guess the point is, SQL doesn't write reports, report writing software does. Good luck.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1