I have 2 tables, and I am trying to match up the data but all the answers have suggested right joins or full joins, which are not available on SQLite.
Table 1:
╔═════════╦═════════╦══════╗
║ Company ║ Product ║ Cost ║
╠═════════╬═════════╬══════╣
║ A ║ abc ║ 100 ║
║ B ║ abc ║ 150 ║
║ F ║ xyz ║ 250 ║
║ G ║ xyz ║ 300 ║
╚═════════╩═════════╩══════╝
However I have a list of more companies (with same products)
Table 2:
╔═════════╦═════════╗
║ Product ║ Company ║
╠═════════╬═════════╣
║ abc ║ A ║
║ abc ║ B ║
║ abc ║ C ║
║ abc ║ D ║
║ abc ║ E ║
║ abc ║ F ║
║ abc ║ G ║
║ xyz ║ A ║
║ xyz ║ B ║
║ xyz ║ C ║
║ xyz ║ D ║
║ xyz ║ E ║
║ xyz ║ F ║
║ xyz ║ G ║
╚═════════╩═════════╝
How do I match them up so they look like this?
Table 3:
╔═════════╦═════════╦══════╗
║ Product ║ Company ║ Cost ║
╠═════════╬═════════╬══════╣
║ abc ║ A ║ 100 ║
║ abc ║ B ║ 150 ║
║ abc ║ C ║ null ║
║ abc ║ D ║ null ║
║ abc ║ E ║ null ║
║ abc ║ F ║ null ║
║ abc ║ G ║ null ║
║ xyz ║ A ║ null ║
║ xyz ║ B ║ null ║
║ xyz ║ C ║ null ║
║ xyz ║ D ║ null ║
║ xyz ║ E ║ null ║
║ xyz ║ F ║ 250 ║
║ xyz ║ G ║ 300 ║
╚═════════╩═════════╩══════╝
When I use this code,
SELECT Company, t.Product, Cost
FROM table1 as t INNER JOIN table2 as f ON t.product = f.product
WHERE t.company = f.company
it only returns [Company] with an associated [Product] and [Cost], but does not return [Company] with null values.
When I use
SELECT Company, t.Product, Cost
FROM table1 as t INNER JOIN table2 as f ON t.company = f.company
then my output looks like
╔═══════════╦═══════════╦═════════╗
║ t.Company ║ f.Company ║ Product ║
╠═══════════╬═══════════╬═════════╣
║ A ║ A ║ abc ║
║ B ║ A ║ abc ║
║ F ║ A ║ abc ║
║ G ║ A ║ abc ║
║ A ║ B ║ abc ║
║ B ║ B ║ abc ║
║ F ║ B ║ abc ║
║ G ║ B ║ abc ║
║ A ║ C ║ abc ║
║ B ║ C ║ abc ║
║ F ║ C ║ abc ║
║ G ║ C ║ abc ║
╚═══════════╩═══════════╩═════════╝
Any help will be much appreciated. Thank you!
SQLite does support LEFT OUTER JOIN, which should do the job just fine:
select two.product, two.company, one.cost from two
left outer join one on
((one.company = two.company) and (one.product = two.product));
(where two is your "table 2" and one is your "table 1")
Running this in SQLite with the above data:
abc|A|100
abc|B|150
abc|C|
abc|D|
abc|E|
abc|F|
abc|G|
xyz|A|
xyz|B|
xyz|C|
xyz|D|
xyz|E|
xyz|F|250
xyz|G|300
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments