I have two tables, one which has each visit(call) on a house logged, another
which has each letter sent to a house logged.
I would like to create a query which has two columns, Letters.Date and
Calls.Date, for a specific HouseID.
I have tried this query for HouseID 16:
Select Letters.Date,Calls.Date From (Select * From Letters Where HouseID=16)
As Letters JOIN Calls On Calls.HouseID=Letters.HouseID
Unfortunately, there are many duplicates, because there are many
combinations where the HouseID matches. So I tried using Distinct and Group
By, but each time I do that it shows more Call dates than there really are.
This is the query that does that:
Select Distinct Letters.Date,Calls.Date From (Select * From Letters Where
HouseID=16) As Letters JOIN Calls On Calls.HouseID=Letters.HouseID
There are meant to be three call dates, but it shows four.
So how do I get the desired result?
This would be:
26/5/08 15/5/08
27/5/08 15/5/08
28/5/08 15/5/08
16/5/08
The tables and data used are:
CREATE TABLE "Letters" (
"ID" INTEGER PRIMARY KEY,
"HouseID" TEXT,
"Date" TEXT
)
BEGIN TRANSACTION;
insert into Letters ("ID", "HouseID", "Date") values ('1', '16', '26/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('2', '11', '17/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('3', '16', '27/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('4', '16', '28/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('5', '18',
'89/89/89');
insert into Letters ("ID", "HouseID", "Date") values ('6', '10', '25/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('7', '16', '16/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('8', '10', 'test');
insert into Letters ("ID", "HouseID", "Date") values ('9', '7', '29/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('10', '11',
'25/5/08');
insert into Letters ("ID", "HouseID", "Date") values ('11', '7', '6/5/08');
COMMIT;
CREATE TABLE "Calls" (
"ID" INTEGER PRIMARY KEY,
"HouseID" TEXT,
"Date" TEXT
)
BEGIN TRANSACTION;
insert into Calls ("ID", "HouseID", "Date") values ('1', '1', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('2', '2', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('3', '3', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('4', '4', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('5', '5', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('6', '6', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('7', '7', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('8', '8', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('9', '1', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('10', '1', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('11', '7', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('12', '7', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('13', '7', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('14', '2', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('15', '8', '28/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('16', '9', '19/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('18', '11', '19/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('19', '11', '19/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('20', '12', '19/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('21', '12', '19/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('22', '13', '19/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('23', '14', '25/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('24', '14', '5');
insert into Calls ("ID", "HouseID", "Date") values ('25', '14', '');
insert into Calls ("ID", "HouseID", "Date") values ('26', '15', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('27', '16', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('29', '18', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('30', '19', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('31', '16', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('32', '20', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('33', '21', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('34', '18', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('35', '16', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('36', '22', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('37', '23', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('38', '10', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('39', '24', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('40', '25', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('41', '26', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('42', '27', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('43', '28', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('44', '29', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('45', '30', '12/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('46', '31', '15/10/10');
insert into Calls ("ID", "HouseID", "Date") values ('47', '7', '21/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('48', '32', '25/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('49', '33', '26/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('50', '4', '26/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('51', '34', '26/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('52', '6', '27/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('53', '35', '27/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('54', '36', '27/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('55', '2', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('56', '33', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('57', '4', '15/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('58', '37', '15/6/08');
insert into Calls ("ID", "HouseID", "Date") values ('59', '38', '15/6/08');
insert into Calls ("ID", "HouseID", "Date") values ('60', '39', '15/6/08');
insert into Calls ("ID", "HouseID", "Date") values ('61', '40', '27/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('62', '41', '27/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('63', '20', '27/5/08');
insert into Calls ("ID", "HouseID", "Date") values ('64', '42', '6/7/8');
insert into Calls ("ID", "HouseID", "Date") values ('65', '3', '7/7/7');
COMMIT;
--
View this message in context:
http://www.nabble.com/Multitable-query-question-tp17526832p17526832.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
