Welcome Guest, Not a member yet? Register   Sign In
how to sort by date field on three(3) tables
#1

[eluser]newbie boy[/eluser]
not sure if this issue goes in this thread, i think its a SQL question more than anything but if anybody can help i would appreciate it.

if i got three(3) tables, each of them with a "date" field.

how do i sort my results on the date field?

is this sort of thing possible with groupby or other method?
#2

[eluser]Dam1an[/eluser]
Assuming you're asking how do you specify on which tables date field to sort, just put the table name with a dot in front of the column name
eg
Code:
$this->db->order_by('table_1.date', 'asc');
#3

[eluser]überfuzz[/eluser]
Do you get the rows using join or union? You could us column AS namn and sort by namn on all rows. (Sort the rows as Damian showed.)
#4

[eluser]newbie boy[/eluser]
leftjoin or union....

or other ways ...

badly needed the solution for this...

thanks guys...
#5

[eluser]newbie boy[/eluser]
all tables have a date field that i must display the results through a descending order...

the query should be like the results came from a single table's date field...

somehow i need to combine all the date field from all the tables then make my query...

this, really i just don't know how...
#6

[eluser]überfuzz[/eluser]
What do you got now? The thing you describe could be done exactly as we wrote.
#7

[eluser]Vaibhav Jain[/eluser]
Hello everyone,
Well I also have same issue.
I have three tables :
1.)tbl_sales(temp table)
2.) tbl_items
3.) tbl_inventory

Now what happens when I create join with sale and item tables then it works fine and when i include third column from inventory table then data comes wrongly. Don't know what I'm doing wrong, may be some join problem.

here is the right code :
Code:
public function getData(array $inputs)
    {
        $this->db->select('name, sum(quantity_purchased) as "Qty Sold", tbl_items.quantity');
        $this->db->from('sales_items_temp');
        $this->db->join('items', 'sales_items_temp.item_id = items.item_id');
        $this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
        $this->db->group_by('items.item_id');
        $this->db->order_by('name');

        return $this->db->get()->result_array();      
    }


and this is my code :
Code:
public function getData(array $inputs)
    {
      
        $this->db->select('name, sum(tbl_inventory.trans_inventory) as "reports_quantity_received", sum(quantity_purchased) as "Qty Sold", tbl_items.quantity as "Qty remain"') ;
        $this->db->from('sales_items_temp');
        $this->db->join('items', 'sales_items_temp.item_id = items.item_id');
        $this->db->join('inventory','items.item_id = inventory.trans_items', 'LEFT OUTER');
        $this->db->where('trans_inventory >= 0');
        $this->db->where('sale_date  BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
        $this->db->or_where('trans_date BETWEEN "'.$inputs['start_date']. '" and "'.$inputs['end_date'].'"');
        $this->db->group_by('items.item_id');
        $this->db->order_by('name');

        return $this->db->get()->result_array();
    }


but this is not correct code, don't know what I'm doing wrong.:down:
If anyone know how to solve this issue then please let me know as soon as possible.
It would be great if someone give me a prompt reply..:-)

Thanks in advance..
Regards,
Vaibhav Jain




Theme © iAndrew 2016 - Forum software by © MyBB