3
Contributors
6
Replies
43
Views
1 Year
Discussion Span
Last Post by Stuart_4
0

I used these codes

SELECT
c1.date,
SUM(
IFNULL(
cx.purchase - cx.sold + mixed.open_qt,
0
)
) AS opening,
c1.purchase,
c1.sold,
SUM(
IFNULL(
cx.purchase - cx.sold + mixed.open_qt,
0
)
) + IFNULL(c1.purchase - c1.sold,
0) AS closing
FROM
(
SELECT
a.date,
a.qty AS purchase,
b.qty AS sold
FROM
arrival a,
pouring b
WHERE
a.date = b.date
ORDER BY
a.date
) c1
LEFT JOIN
(
SELECT
a.date,
a.qty AS purchase,
b.qty AS sold
FROM
arrival a,
pouring b
WHERE
a.date = b.date
ORDER BY
a.date
) cx ON c1.date > cx.date
GROUP BY
c1.date,
c1.purchase,
c1.sold

MySQL said:

1054 - Unknown column 'mixed.open_qt' in 'field list'

But field mixed.open_qt is present

0

But field mixed.open_qt is present

That might be, but I do not see the table "mixed" mentioned anywhere in your query.

0

I beleive my query is wrong, i am using 3 tables as
1-mixed
2-arrival
3-pouring

aa.png
aa.pngaa.png

So, I want to get date from these 3 tables

Edited by phphp

0

A well known and good reputed Visual Foxpro programmer (Vilhelm-ion-Praisach) provided this working solution.

He can be reached by this link
https://www.facebook.com/vilhelm.praisach
https://www.foxite.com/members/default.aspx?id=97adc192-3121-4dda-ad2d-9b074b4453dd

SELECT
  c1.date,
  SUM(IFNULL(cx.purchase - cx.sold,
  0)) + MAX(p.openqty) AS opening,
  c1.purchase,
  c1.sold,
  SUM(IFNULL(cx.purchase - cx.sold,
  0)) + MAX(p.openqty) + c1.purchase - c1.sold AS closing
FROM
  (
  SELECT
    open_qt AS openqty
  FROM
    mixed
) p
LEFT JOIN
  (
  SELECT
    a.date,
    a.qty AS purchase,
    b.qty AS sold
  FROM
    arrival a,
    pouring b
  WHERE
    a.date = b.date
  ORDER BY
    a.date
) c1 ON c1.purchase + p.openqty > 0
LEFT JOIN
  (
  SELECT
    a.date,
    a.qty AS purchase,
    b.qty AS sold
  FROM
    arrival a,
    pouring b
  WHERE
    a.date = b.date
  ORDER BY
    a.date
) cx ON c1.date > cx.date
GROUP BY
  c1.date,
  c1.purchase,
  c1.sold

Edited by phphp

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.