CODEWITHSUNDEEP
mysqlsql
Schnurze
Schnurze

Reputation: 1

Newbie needs help on query (MYSQL)

I'm new here and nearly new in SQL.

My problem: I've a table (T1) with

Datetime, CostNo, Amount

second table (T2) with

Date, CostNo, BillNo

In the query should be

Date, CostNo, BillNo, Amount

My problem are several BillNos for the same CostNo the same day.

I need a kind of sorting the BillNo according to the Datetime.

T1 f.e.:

2018-11-02 11:14:52 3637    24.10  
2018-11-02 11:16:43 965     2.50  
2018-11-02 11:40:28 2552    3.50  
2018-11-02 11:40:51 2552    3.00  
2018-11-02 11:41:10 2552    3.50  
2018-11-02 11:41:36 2552    3.00  
2018-11-02 11:55:03 980     3.00  
2018-11-02 11:59:11 1976    3.00

T2 f.e.:

2018-11-02  3637    26189  
2018-11-02  965     26190  
2018-11-02  2552    26191  
2018-11-02  2552    26192  
2018-11-02  2552    26193  
2018-11-02  2552    26194  
2018-11-02  980     26195  
2018-11-02  1976    26196

so my query:

select
T2.BillDate,
T2.CostNo,
T2.BillNo,
T1.Amount
from 
    `T2`,
    `T1`
where
    T1.CostNo =T2.CostNo
    AND DATE(T1.BillDateTime) = T2.BillDate

works fine until CostNo 2552:

2018-11-02  3637    26189   24.10  
2018-11-02  965     26190   2.50  
2018-11-02  2552    26191   3.50  
2018-11-02  2552    26191   3.00  
2018-11-02  2552    26191   3.50  
2018-11-02  2552    26191   3.00  
2018-11-02  2552    26192   3.50  
2018-11-02  2552    26192   3.00  
2018-11-02  2552    26192   3.50  
2018-11-02  2552    26192   3.00  
2018-11-02  2552    26193   3.50  
2018-11-02  2552    26193   3.00  
2018-11-02  2552    26193   3.50  
2018-11-02  2552    26193   3.00  
2018-11-02  2552    26194   3.50  
2018-11-02  2552    26194   3.00  
2018-11-02  2552    26194   3.50  
2018-11-02  2552    26194   3.00  
2018-11-02  980     26195   3.00  
2018-11-02  1976    26196   3.00

This should be the result:

2018-11-02  3637    26189   24.10  
2018-11-02  965     26190   2.50  
2018-11-02  2552    26191   3.50  
2018-11-02  2552    26192   3.00  
2018-11-02  2552    26193   3.50  
2018-11-02  2552    26194   3.00  
2018-11-02  980     26195   3.00  
2018-11-02  1976    26196   3.00

Upvotes: 0

Views: 90

Answers (2)

Schnurze
Schnurze

Reputation: 1

OK thanks for the help. I found a solution by myself. Here it is, if someone will know it: The BillNo from the 2. table is linked to the DateTime of table 1 So I build a temporaray table from table 1 with SUM/GROUP BY and ORDER BY on DateTime and add a row ID (auto_increment). Next I build a temporary table from table 2, order by BillNo and add a row ID. Now I can do a query with T1.ID = T2.ID and that's it.

Feel free to post a better and/or shorter solution.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I am guessing that you want:

select T2.BillDate, T2.CostNo, T2.BillNo, T1.Amount
from `T2` join
     `T1`
     on T1.CostNo = T2.CostNo and DATE(T1.BillDateTime) = T2.BillDate
order by T!.CostNo, T1.BillDateTime DESC;

First note the proper use of JOIN. Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Second, if you want the results in a particular order, then your query should have an ORDER BY clause. SQL tables and result sets (with no ORDER BY) represent unordered sets. So, you need an ORDER BY. I am guessing you want to keep all the CostNos together, and then within each, ordered by the date/time.

Upvotes: 1

Related Questions