jeudi 5 février 2015

Matching data from two tables with unequal number of rows

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!


Aucun commentaire:

Enregistrer un commentaire