CODEWITHSUNDEEP
sqliteandroid-4.0-ice-cream-sandwich
SBotirov
SBotirov

Reputation: 14138

SQLite mistake on joining tables

I have following tables:

PRAGMA foreign_keys = ON;

CREATE TABLE products(
    '_id' INTEGER PRIMARY KEY AUTOINCREMENT,
    'name' VARCHAR(20),
    'price' INTEGER
);

CREATE TABLE orders(
    '_id' INTEGER PRIMARY KEY AUTOINCREMENT,
    'order_number' INTEGER,
    'order_sum' INTEGER
);

CREATE TABLE ord_details(
    '_id' INTEGER PRIMARY KEY AUTOINCREMENT,
    'order_id' INTEGER,
    'product_id' INTEGER,
    'product_count' INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE ord_times(
    '_id' INTEGER PRIMARY KEY AUTOINCREMENT,
    'order_id' INTEGER,
    'start_t' TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    'end_t' TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(_id) ON DELETE CASCADE ON UPDATE CASCADE
);

So, in tables have following data:

In 'products' table

---------------------
1 | roll        | 100
2 | cheese      | 500
3 | burger      | 300
4 | mega burger | 550

In 'orders' table

-------------
1 | 11 | 600

In 'ord_details' table

--------------
1 | 1 | 1 | 1
1 | 1 | 2 | 1

In 'ord_times' table

--------------------------------------------------------
1 | 1 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM

when I execute this SQL command and it gives incorrect result:

SELECT orders.order_number, products.name as prname, products.price, ord_details.product_count, orders.order_sum, ord_times.start_t, ord_times.end_t FROM orders 
LEFT JOIN ord_details ON orders._id=ord_details.order_id 
LEFT JOIN products ON ord_details.product_id=products._id
LEFT JOIN ord_times ON orders._id=ord_times.order_id

Result:

-----------------------------------------------------------------------------------
11 | roll        | 100 | 1 | 600 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM
11 | cheese      | 500 | 1 | 600 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM
11 | burger      | 300 | 0 | 600 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM
11 | mega burger | 550 | 0 | 600 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM

I'm expecting as following result:

-----------------------------------------------------------------------------------
11 | roll        | 100 | 1 | 600 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM
11 | cheese      | 500 | 1 | 600 | 12/23/2013 12:24:38 PM | 12/23/2013  12:27:02 PM

When I execute this code in sqlite-shell-win32-x86-3080200 normally working:

enter image description here

but execute this SQL in android return incorrect result. Here inserting code:

INSERT INTO products (name, price) VALUES ("roll", 100);
INSERT INTO products (name, price) VALUES ("cheese", 500);
INSERT INTO products (name, price) VALUES ("burger", 300);
INSERT INTO products (name, price) VALUES ("mega burger", 550);
INSERT INTO orders (order_number, order_sum) VALUES (11, 600);
INSERT INTO ord_details (order_id, product_id, product_count) VALUES (1, 1, 1);
INSERT INTO ord_details (order_id, product_id, product_count) VALUES (1, 2, 1);
INSERT INTO ord_times (order_id, start_t, end_t) VALUES (1,'12/23/2013 12:24:38 PM', '12/23/2013  12:27:02 PM');

Why "ord_details.product_id=products._id" expression not working in android?

Upvotes: 1

Views: 61

Answers (1)

Anoop
Anoop

Reputation: 369

Try to use JOIN instead of LEFT JOIN while joining the OrderDetails and Products table so the code will look like 

SELECT orders.order_number, products.name as prname, products.price, 

ord_details.product_count, orders.order_sum, ord_times.start_t, ord_times.end_t FROM orders 

LEFT JOIN ord_details ON orders._id=ord_details.order_id 

JOIN products ON ord_details.product_id=products._id

LEFT JOIN ord_times ON orders._id=ord_times.order_id

Upvotes: 1

Related Questions