1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need a little help to recap for this query

Discussion in 'Databases' started by Hubert jiang, Apr 2, 2015.

  1. #1
    I am trying to retrieve a list of customers from customer table.I have different types of customer in this table, there are walk-in and corporate. For walk-in customers, the company field is not mandatory but for corporate, we will need to know which company they are from, so there will be null values for company col. this is the same for address as walk-in customers do not have credit note, so they have an option to leave their address while corporate customers have credit notes so we will need to know their address to send the invoice over.

    I have 3 tables: Customer, Company, Address
    For customer, My columns are: customerID, name, type, companyID, contact, gender, dob, addressID.
    For company, My columns are: companyID, name, type, addressID, tel, fax, email, website
    For Address: My Co;umns are: addressID, addressLine1, addressLine2, country, postal

    I like to retrieve these in php. but I not sure how should the query be like.

    I want to display: the CustomerID, Customer Name, Company Name, Contact, Type.

    I tried
    SELECT customerID, c.name, co.name, contact, type FROM customer c, company co WHERE c.companyID=co.companyID
    Code (markup):
    , but I will get only corporate customers. I tried the inner join method but no avail. I use to do this kind of queries 3 years ago, and I have forgotten most.

    Thanks in advance.
     
    Solved! View solution.
    Hubert jiang, Apr 2, 2015 IP
  2. #2
    
    "SELECT t1.*,t2.*,t3.* FROM customer t1 LEFT JOIN company t2 ON t1.companyID = t2.companyID LEFT JOIN address t3 ON t1.addressID = t3.addressID"
    
    Code (markup):
    That shold in theory show all customers, regardless of whatever the status of company or address - adjust columns accordingly, and process the result in PHP
     
    PoPSiCLe, Apr 2, 2015 IP