Re: [sqlite] About Joining Multiple Tables
On 31 Dec 2011, at 8:36am, Sushil Dudhalkar wrote: > You are right :-) The lengths are different. I will correct that. Thank > you Simon. Will remember this for the rest of my life. No problem. Common mistake when importing data. Good luck with it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About Joining Multiple Tables
You are right :-) The lengths are different. I will correct that. Thank you Simon. Will remember this for the rest of my life. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About Joining Multiple Tables
On 31 Dec 2011, at 8:04am, Sushil Dudhalkar wrote: > Thanks Simon. I did the lookup which goes straight from the PO to the > Suppliers table. But again the same result. All the fields from the PO table > are returned but nothing from the Suppliers table. Where could i be wrong? > Thanks again for your reply. Apparently, the fields you think match, don't match. So you may have a problem where you imported your data. Try these SELECT SuppID,typeof(SuppID),length(SuppID) FROM PO; SELECT SID,typeof(SID),length(SID) FROM Suppliers; You should get lines which are identical between the two tables, but I bet you don't. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About Joining Multiple Tables
Try doing a lookup which goes straight from the PO to the Suppliers table: SELECT PO.Po_ID, PO.Po_Date, PO.QuoteNum, PO.PayTerms, PO.DeliveryDate, PO.VAt, PO.SrvcTax, PO.Amount, Suppliers.SName, Suppliers.Address1, Suppliers.Address2, Suppliers.City, FROM PO LEFT JOIN Suppliers ON PO.SuppID = Suppliers.SID and see whether that works and does return the data that would correspond to the transaction P-1001. Simon. Thanks Simon. I did the lookup which goes straight from the PO to the Suppliers table. But again the same result. All the fields from the PO table are returned but nothing from the Suppliers table. Where could i be wrong? Thanks again for your reply. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About Joining Multiple Tables
On 30 Dec 2011, at 11:44am, Sushil Dudhalkar wrote: > My query returns everything but the corresponding Supplier and customer > records. The query is as under. You are using double quotes here: > where Transactions.DocID = "P-1001" They should be single quotes: where Transactions.DocID = 'P-1001' Apart from that, I don't notice anything wrong. Try doing a lookup which goes straight from the PO to the Suppliers table: SELECT PO.Po_ID, PO.Po_Date, PO.QuoteNum, PO.PayTerms, PO.DeliveryDate, PO.VAt, PO.SrvcTax, PO.Amount, Suppliers.SName, Suppliers.Address1, Suppliers.Address2, Suppliers.City, FROM PO LEFT JOIN Suppliers ON PO.SuppID = Suppliers.SID and see whether that works and does return the data that would correspond to the transaction P-1001. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About Joining Multiple Tables
Hi! Need Help. I have these 5 tables 1. PO (Purchase Order) 2. Transactions 3. Items 4. Suppliers 5. Customers To print my purchase order i Need the Below data Transactions.DocID, Transactions.Quantity, Transactions.Rate, Items.ItemName, Items.Unit, PO.Po_ID, PO.Po_Date, PO.QuoteNum, PO.PayTerms, PO.DeliveryDate, PO.VAt, PO.SrvcTax, PO.Amount, Suppliers.SName, Suppliers.Address1, Suppliers.Address2, Suppliers.City, Customers.CName, Customers.Address1, Customers.Address2, Customers.City My query returns everything but the corresponding Supplier and customer records. The query is as under. SELECT Transactions.DocID, Transactions.Quantity, Transactions.Rate, Items.ItemName, Items.Unit, PO.Po_ID, PO.Po_Date, PO.QuoteNum, PO.PayTerms, PO.DeliveryDate, PO.VAt, PO.SrvcTax, PO.Amount, Suppliers.SName, Suppliers.Address1, Suppliers.Address2, Suppliers.City, Customers.CName, Customers.Address1, Customers.Address2, Customers.City FROM Transactions INNER JOIN Items ON Transactions.ItemID = items.ItemID LEFT JOIN PO ON Transactions.DocID = PO.Po_ID LEFT JOIN Suppliers ON PO.SuppID = Suppliers.SID LEFT JOIN Customers ON PO.CustID = Customers.CustID where Transactions.DocID = "P-1001" I am a newbie and would be obliged if anyone can guide me. Using SQLite3. Many thanks and regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users