brain failed: help needed

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

brain failed: help needed

RhinosoRoss
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);

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.

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 :-)
Reply | Threaded
Open this post in threaded view
|

Re: brain failed: help needed

Puneet Kishor-2
You need LEFT JOIN.

Also, keep in mind that no operator other than IS NULL or IS NOT NULL works on NULL.

On Mar 17, 2012, at 9:06 AM, RhinosoRoss <[hidden email]> 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);
>
> 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.
>
> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: brain failed: help needed

Richard Hipp-3
In reply to this post by RhinosoRoss
On Sat, Mar 17, 2012 at 10:06 AM, RhinosoRoss <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users