On Sat, Mar 17, 2012 at 10:06 AM, RhinosoRoss <bosss...@hotmail.com> wrote:
> > Hi everyone, > Sorry to be a pest, but I'm drowning in manual pages and clearly missing > something simple. I don't think I'm going to progress without some help. > > I noticed at work that they were using excel to store serial numbers of > devices sold in orders... Should be a database! So I knocked one up in MS > Access in a couple of minutes - but I'm trying to learn SQLite at home, I'm > a C++ programmer so the actual embedding is straightforward, it's the SQL > I'm struggling with. > In MS Access, you simply create tables and join them together in the > graphical ER-digaram, then creating queries is trivial - all the joins are > done for you. > > It's taken me a week to figure out how to get all the joins in one schema > to > list the records as they were in the original excel spreadsheet: > > Date,Order Number, Customer, Serial Number, Notes > > Oh, this should be sooooo easy! > > I have two remaining issues that I can't figure out. > 1) My SQL to list everything won't print records with NULL Notes. > 2) I don't understand if I'm supposed to be using the indexes I've created > explicitly, or if they're used automagically. > > Here's the database Schema: > > > CREATE TABLE [Customer] ( > [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > [Name] TEXT UNIQUE NOT NULL); > > CREATE TABLE [Notes] ( > [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > [Note] TEXT UNIQUE NOT NULL); > > CREATE TABLE [Order] ( > [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > [Number] TEXT UNIQUE NOT NULL, > [Customer] INTEGER NOT NULL REFERENCES Customer(ID) ON UPDATE CASCADE, > [Date] DATE); > > CREATE TABLE [SoldItem] ( > [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > [Serial] TEXT UNIQUE NULL, > [Order] INTEGER NOT NULL REFERENCES [Order](ID) ON UPDATE CASCADE, > [Notes] INTEGER REFERENCES Notes(ID) ON UPDATE CASCADE); > > The following three indices are redundant. You've already declared those columns to be UNIQUE. Omit these indices. > CREATE UNIQUE INDEX NotesNote ON Notes(Note); > > CREATE UNIQUE INDEX OrderNumber ON [Order](Number); > > CREATE UNIQUE INDEX SoldItemSerial ON SoldItem(Serial); > __________________________________________________ > This is the SQL that Access provided: > > SELECT Order.Date, Order.Number, Customer.Name, SoldItem.Serial, Notes.Note > FROM Notes INNER JOIN ( > (Customer INNER JOIN [Order] ON Customer.ID = Order.Customer) > INNER JOIN SoldItem ON Order.ID = SoldItem.Order) > ON Notes.ID = SoldItem.Notes; > __________________________________________________ > This is what I think should work in SQLite: > > SELECT Date, Number, Name, Serial, Note > FROM (SoldItem JOIN > (Customer JOIN [Order] ON Customer.ID =[Order].Customer) [Order] > ON [Order].ID = SoldItem.[Order]) SoldItem > JOIN Notes ON SoldItem.Notes = Notes.ID; > > but if SoldItem.Notes is NULL, no record is produced. I've tried > CROSS,INNER,OUTER and rearranging the order but to no avail. > Try changing the second join to a LEFT JOIN. > > If I remove the Notes table from the SQuirreL, it all works, but I want the > notes in the result! > Works: > SELECT Date, Number, Name, Serial > FROM SoldItem JOIN > (Customer JOIN [Order] ON Customer.ID =[Order].Customer) [Order] > ON [Order].ID = SoldItem.[Order] > > So could somebody please write me the SQL to to list all the records in the > database as they were originally (Date, Number, Name, Serial, Note) > including records with no notes? > > Thanks for your time - much pain will be alleviated :-) > -- > View this message in context: > http://old.nabble.com/brain-failed%3A-help-needed-tp33522661p33522661.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
