B4J Question MySql experts, a little help ...

[marcick]

May I abuse of the forum for this ? .....

I have a query that extract the total records stored from each ID in the month of may

SELECT ID, COUNT(*) as Count FROM records
WHERE MID(StoTime, 6,2)="05"
GROUP BY ID
ORDER BY Count ASC

And I obtain the correct result as in the picture.

Now, each ID has an owner that is inside another table "Owners" that has fields "ID" and "Owner"
I would like the result of the first query contains also the owner and not only the ID and Count.

I tried to understand how works UNION ad JOIN clauses but no success ...

 

[marcick]

found it ..

SELECT records.ID, COUNT(*) as Count, Owners.owner as Owner
FROM records
LEFT JOIN Owners ON records.ID=Owners.ID
WHERE MID(StoTime, 6,2)="05"
AND Sender="R"
GROUP BY ID
ORDER BY Count ASC

 

[BillMeyer]

If I may, I would like to add my 2 cents here please.

Here is an excellent go to resource (link)

and few people know about the "With Rollup" function in MySQL

Enjoy !!

 

[marcick]

Thank you, your two cents are always welcome
Sometimes it's not easy to find an answer in those good guides, because you should before guess "which function will do this job" and then you can study the function ...