MySQL Left/Right/Outer Join

Question

I want to display supplier_address of active user only. Below is the database of the system.

Illustration of my db

Like I mentioned above, I want to display supplier_address of active suppliers only. The image provided above, shows A - supplier_address table and B - status_login table which hold the "active" element.

Right now, my coding look like this,

    $activeSupplier = "Active";

    // fetch data for 'ACTIVE' Supplier Basic Info Table from table (supplier_profile)
    $supplierAddressInfoListTable = SupplierAddress::find()
    ->select(['supplier_address.supplier_addressID AS supplierAddressID', ` 
    'supplier_address.supplier_profile_ID AS supplierID', 'supplier_address.address_one AS supplierAddressone',` 
    'supplier_address.address_two AS supplierAddresstwo', 'supplier_address.postcode AS supplierPostcode',
    'supplier_address.city AS supplierCity', 'status_login.description AS supplierLoginstatus',
    'state.stateID AS supplierStateID', 'state.name AS supplierStatename',
    'country.countryID AS supplierCountryID', 'country.name AS supplierCountryname'])
    ->leftJoin('supplier_profile', 'supplier_profile.supplier_profileID = supplier_address.supplier_profile_ID')
    ->leftJoin('state', 'state.stateID = supplier_address.state_ID')
    ->leftJoin('country', 'country.countryID = supplier_address.country_ID')
    ->where(['=', 'status_login.description', $activeSupplier])
    ->orderBy(['supplier_profile.supplier_profileID' => SORT_ASC])
    ->asArray()
    ->all();

I don't know how I can get by without having to rely on a temporary table. If you guys have some suggestion, please do recommend here.

Answer 1

Also you need to join the login_profile and status_login tables.

`

$activeSupplier = "Active";

    // fetch data for 'ACTIVE' Supplier Basic Info Table from table (supplier_profile)
    $supplierAddressInfoListTable = SupplierAddress::find()
    ->select(['supplier_address.supplier_addressID AS supplierAddressID', ` 
    'supplier_address.supplier_profile_ID AS supplierID', 'supplier_address.address_one AS supplierAddressone',` 
    'supplier_address.address_two AS supplierAddresstwo', 'supplier_address.postcode AS supplierPostcode',
    'supplier_address.city AS supplierCity', 'status_login.description AS supplierLoginstatus',
    'state.stateID AS supplierStateID', 'state.name AS supplierStatename',
    'country.countryID AS supplierCountryID', 'country.name AS supplierCountryname'])
    ->leftJoin('supplier_profile', 'supplier_profile.supplier_profileID = 
         supplier_address.supplier_profile_ID')
   ->leftJoin('login_profile', 'supplier_profile.supplier_profileID = 
        login_profile.supplier_profile_ID')
   ->leftJoin('status_login', 'login_profile.status_login_ID = 
          status_login.status_login_ID')
    ->leftJoin('state', 'state.stateID = supplier_address.state_ID')
    ->leftJoin('country', 'country.countryID = supplier_address.country_ID')
    ->where(['=', 'status_login.description', $activeSupplier])
    ->orderBy(['supplier_profile.supplier_profileID' => SORT_ASC])
    ->asArray()
    ->all()

`