0

I have two tables products table and batch table. products table contains a list of products and batch table contains information on arrival date, expiry date, batch number and quantity. when recording to batch table i pull product_id and store it on batch table. I want to query the two tables using right join, however I am not able to get the other rows from the batch table. here is my code

SELECT products.product_id, products.gen_name,products.product_code, batch.product_id FROM products RIGHT OUTER JOIN batch ON batch.product_id=products.product_id;

how can i also get arrival date, expiry date, batch number and quantity from the batch table?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Njoroge Mathu
  • 45
  • 6
  • 1
    Hard to say without seeing data, but you aren't querying arrival date, expiry date, and batch number in your select either. How do you know you're not getting rows? batch.product_id is null? – manderson Mar 06 '17 at 19:03
  • Have you tried making the primary pull from the batch table, and LEFT joining on the products table? I'm not 100% sure I understand your situation, but if you want all the records from the BATCH table, this may be a better choice. You may also have an issue with nulls in the existing RIGHT join. Maybe add a WHERE batch.productiD IS NOT NULL. – DanielG Mar 06 '17 at 19:20
  • 1
    Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Mar 06 '17 at 19:26

2 Answers2

0

how can i also get arrival date, expiry date, batch number and quantity from the batch table?

Add them to the select list.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

It is hard to say without seeing table. I hope this might help you to solve your problem: SQL RIGHT JOIN

Suresh
  • 16
  • 2
  • The link helped me. Selecting all the rows did the work. changed my code to this `SELECT* FROM products RIGHT OUTER JOIN batch ON batch.product_id=products.product_id` – Njoroge Mathu Mar 08 '17 at 08:59