How to item balance

by

Issue(s) with your query:

  • If you are giving an alias to a table use that alias for each reference. issue: FROM trans AS t1 WHERE (trans.Trans_date <= '2019-08-31'), here trans table is aliased as t1 and you are using again full name with trans.Trans_date.
SELECT 
    i.`Item Desc` AS ItemDesc,
    t.Trans_date AS Trans_date,
    t.B1 AS B1
FROM item AS i
INNER JOIN trans AS t ON i.Item_code = t.Item_code
WHERE t.Trans_date = (SELECT MAX(t1.Trans_date) As trans_date
                     FROM trans t1
                     WHERE t1.Trans_date <= '2019-08-31'
                     AND t1.Item_code = i.Item_code);

More Related Contents:

  1. If two columns are 1 then make new column
  2. Can we use CONCAT and JOIN together in MySQL? [closed]
  3. String concatenation in MySQL
  4. Unknown column in ‘field list’ error on MySQL Update query
  5. How to check if mysql database exists
  6. Change auto increment starting number?
  7. SELECT * FROM multiple tables. MySQL
  8. How to get the difference between two timestamps in seconds
  9. MySQL Event Scheduler on a specific time everyday
  10. Export and Import all MySQL databases at one time
  11. How to access a RowDataPacket object
  12. tomcat7 – jdbc datasource – This is very likely to create a memory leak
  13. Select from one table where not in another
  14. phpMyAdmin is throwing a #2002 cannot log in to the mysql server phpmyadmin
  15. MySQL database with unique fields ignored ending spaces
  16. How do I setup ASP.NET MVC 2 with MySQL?
  17. Set value to NULL in MySQL
  18. mysql DECLARE WHILE outside stored procedure how?
  19. MySql Single Table, Select last 7 days and include empty rows
  20. How can I check MySQL engine type for a specific table?
  21. mysql SQL: specific item to be first and then to sort the rest of the items
  22. mySQL UPDATE query returns “0 rows affected”
  23. When is a timestamp (auto) updated?
  24. Should I normalize my DB or not?
  25. MySql Query Replace NULL with Empty String in Select
  26. how to trim leading zeros from alphanumeric text in mysql function
  27. What are the max number of allowable parameters per database provider type?
  28. How to handle a query with special characters / (forward slash) and \ (backslash)
  29. Does MySQL support historical date (like 1200)?
  30. mySQL query to search all tables within a database for a string?

Leave a Comment