Login

11-16-2007, 02:29 PM

[eluser]rvent[/eluser]
Hello,

I am having a little problem with a query i am trying to make:

Code:
function list_boards()

    {

        $this->db->select('WorkOrder, PartNumber, PartQuatity, PartPriority, WODateIn, WODateOut, PartState, PartStatus, PartLocation');

        $this->db->from('WorkOrder, WOParts, PartState, PartStatus, PartPriority, PartLocation');

        $this->db->where('WorkOrder.PartNumberID = WOParts.PartNumberID and WOParts.PartStateID = PartState.PartStateID and WOParts.PartStatusID = PartStatus.PartStatusID and WOParts.PartLocationID = PartLocation.PartLocationID and WOParts.PartPriorityID = PartPriority.PartPriorityID');

        $query = $this->db->get();

        return $query->result();

    }

Everything works fine for some fields. Following the order from the select statement i get correct records for:
'WorkOrder, WODateIn, WODateOut'

The view displays all the different WorkOrders with its respective WODateIn and WODateOut, but incorrect PartNumber.

The first row generated displays 'PartNumber, PartQuatity, PartPriority, PartState, PartStatus, PartLocation' correctly for the PartNumber, but then the same info is repeated with different 'WorkOrder, WODateIn, WODateOut'

here is an example:

Code:
'WorkOrder, PartNumber, PartQuatity, PartPriority, WODateIn, WODateOut, PartState, PartStatus, PartLocation');

      97350 | 4233222 |     12 |           Low | 2007-02-13 | 2007-02-16 | Clean | Running | Secondary

      97915 | 4233222 |     12 |           Low | 2007-02-13 | 2007-01-26 | Clean | Running | Secondary

      98058 | 4233222 |     12 |           Low | 2007-02-13 | 2007-02-22 | Clean | Running | Secondary

      98106 | 4233222 |     12 |           Low | 2007-02-13 | 2007-02-01 | Clean | Running | Secondary

      98197 | 4233222 |     12 |           Low | 2007-02-13 | 2007-02-12 | Clean | Running | Secondary

      98633 | 4233222 |     12 |           Low | 2007-02-13 | 2007-01-29 | Clean | Running | Secondary

It should be displaying different 'PartNumber, PartQuatity, PartPriority, PartState, PartStatus, PartLocation' and it should correspond to the given WorkOrder..

Any ideas..? or pointers...?

11-16-2007, 02:47 PM

[eluser]Pygon[/eluser]
consider using join()

11-16-2007, 03:11 PM

[eluser]rvent[/eluser]
yeah i did Smile

Here is the new query

Code:
$this->db->select('WorkOrder, PartNumber, PartQuatity, PartPriority, 

                           WODateIn, WODateOut, PartState, PartStatus, PartLocation');

        $this->db->from('WorkOrder, WOParts, PartState, PartStatus, PartPriority, PartLocation');

        $this->db->join('WOParts as WOP', 'WorkOrder.PartNumberID = WOP.PartNumberID');

        $this->db->join('PartState as PState', 'PState.PartStateID = WOParts.PartStateID');

        $this->db->join('PartStatus as PStatus', 'PStatus.PartStatusID = WOParts.PartStatusID');

        $this->db->join('PartPriority as PPriority', 'PPriority.PartPriorityID = WOParts.PartPriorityID');

        $this->db->join('PartLocation as PLocation', 'PLocation.PartLocationID = WOParts.PartLocationID');

But i get:

Code:
An Error Was Encountered

Error Number: 1054

Unknown column 'WorkOrder.PartNumberID' in 'on clause'

SELECT WorkOrder, WOParts.PartNumber, WOParts.PartQuatity, PartPriority.PartPriority, WODateIn, WODateOut, PartState.PartState, PartStatus.PartStatus, PartLocation.PartLocation FROM WorkOrder, WOParts, PartState, PartStatus, PartPriority, PartLocation JOIN WOParts as WOP ON WorkOrder.PartNumberID = WOP.PartNumberID JOIN PartState as PState ON PState.PartStateID = WOParts.PartStateID JOIN PartStatus as PStatus ON PStatus.PartStatusID = WOParts.PartStatusID JOIN PartPriority as PPriority ON PPriority.PartPriorityID = WOParts.PartPriorityID JOIN PartLocation as PLocation ON PLocation.PartLocationID = WOParts.PartLocationID

Do i have to give it an alias in the FROM part..?

11-16-2007, 03:28 PM

[eluser]Alex007[/eluser]
Your FROM clause AND your JOINS both contain the same tables, that is probably not what you want.

Here's what I'd do:

Code:
$this->db->select('WorkOrder, PartNumber, PartQuatity, PartPriority, WODateIn, WODateOut, PartState, PartStatus, PartLocation');

        $this->db->from('WorkOrder');

        $this->db->join('WOParts', 'WorkOrder.PartNumberID = WOP.PartNumberID');

        $this->db->join('PartState', 'PartState.PartStateID = WOParts.PartStateID');

        $this->db->join('PartStatus', 'PartStatus.PartStatusID = WOParts.PartStatusID');

        $this->db->join('PartPriority', 'PartPriority.PartPriorityID = WOParts.PartPriorityID');

        $this->db->join('PartLocation', 'PartLocation.PartLocationID = WOParts.PartLocationID');

11-16-2007, 03:45 PM

[eluser]rvent[/eluser]
Thanks....

But i am still getting the same result as my first query:

Code:
WorkOrder, PartNumber, PartQuatity, PartPriority, WODateIn, WODateOut, PartState, PartStatus, PartLocation

103407 |     4233222 |        12 |          Low | 2007-04-30 | 2007-04-27 | Clean | Running | Secondary

103406 |     4233222 |        12 |          Low | 2007-04-30 | 2007-04-27 | Clean | Running | Secondary

103408 |     4233222 |        12 |          Low | 2007-04-30 | 2007-04-27 | Clean | Running | Secondary

103501 |     4233222 |        12 |          Low | 2007-05-01 | 2007-05-01 | Clean | Running | Secondary

103564 |     4233222 |        12 |          Low | 2007-05-01 | 2007-05-04 | Clean | Running | Secondary

103563 |     4233222 |        12 |          Low | 2007-05-01 | 2007-05-01 | Clean | Running | Secondary

It repeats most of the same info in all of the fields...

11-16-2007, 03:58 PM

[eluser]rvent[/eluser]
Never mind.. I feel so stupid... It was my fault.

When i did the import from the old DB into the new DB i didnt assoiated the correct workorders with the right partnumbers..

Thanks...