0

So I have a 2 table like below:

Item

+-----------------+-------------+
| id |    Name    | Category    | 
+-----------------+-------------+
|  1 | Something1 |      A      |
|  2 | Something2 |      B      | 
|  3 | Something3 |      B      | 
|  4 | Something4 |      A      |
|  5 | Something5 |      A      |
+-----------------+-------------+

Buy 

+-----------------+-------------+
| id |    Name    |    Number   | 
+-----------------+-------------+
|  1 | Something1 |     14      | 
|  1 | Something2 |     10      | 
|  2 | Something1 |     14      | 
|  2 | Something5 |     11      | 
|  2 | Something3 |     12      |  
|  3 | Something4 |     18      | 
|  4 | Something3 |     11      |  
+-----------------+-------------+

The buy table don't have primary key because id forgein key with another bill table contain id and type(sell/buy/borrow). A bill can contain many items. I can join them one by one

+-----------------+-------------+
|STT |    name    |     Total   |
+-----------------+-------------+
|  1 | Something1 |     28      |  
|  2 | Something4 |     18      | 
|  3 | Something5 |     11      | 
+-----------------+-------------+

What I want: 

+-----------------+-------------+
|STT |    name    |     Total   |
+-----------------+-------------+
| Category: A                   |
|-------------------------------|                             
|  1 | Something1 |     28      |  
|  2 | Something4 |     18      | 
|  3 | Something5 |     11      | 
| Category: B                   |
|-------------------------------| 
|  1 | Something3 |     23      |  
|  2 | Something2 |     10      |
+-----------------+-------------+

Can it possible with just sql?(Not using temporary table)

someoneuseless
  • 303
  • 3
  • 17
  • Not sure I understand, sql only needs keys for performance purposes. A join on small tables will work fine without any. – P.Salmon Jun 27 '19 at 10:12
  • yes. I can do group by and SUM but idk how to display those Category: ABCD one by one in a table and order them seperately. – someoneuseless Jun 27 '19 at 10:14
  • @P.Salmon , *"MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. "* – Raymond Nijland Jun 27 '19 at 10:15
  • @TrầnTrịnhKuy if he had the primary key, how would you have done it? – Alberto Moro Jun 27 '19 at 10:17
  • @Raymond Nijland true but not the thrust of the question I think. – P.Salmon Jun 27 '19 at 10:17
  • 1
    @TrầnTrịnhKuy just like P.Salmon here i do not understand your question, the expected results looks like what should be done in the application, there is no way to get a header in between like that or i take the expected result (way) to literal – Raymond Nijland Jun 27 '19 at 10:17
  • True @P.Salmon not sure why you then mentioned indexes then in your first comment annyhow... *"Can it possible with just sql?(Not using temporary table)"* it's the topicstarter not simply looking for a JOIN here.. – Raymond Nijland Jun 27 '19 at 10:21
  • I intend to query just once so I can print this out in this-> query($query). Not sure if possible to do so. – someoneuseless Jun 27 '19 at 10:22
  • 3
    SQL can't return a table with different columns in each row, you can't have a category row like that. You normally do that in the application programming language that renders into something like an HTML table. – Barmar Jun 27 '19 at 10:25
  • See https://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 for how you can do it in PHP. – Barmar Jun 27 '19 at 10:28
  • Many thank. That solved my problem – someoneuseless Jun 27 '19 at 10:30

0 Answers0