CODEWITHSUNDEEP
phpmysqljoinzen-cart
Paul Williams
Paul Williams

Reputation: 1598

Performing a join on ZenCart's address book entries

I have the following bits of code:

$referrers_query = 
select c.customers_id, c.customers_firstname, c.customers_lastname, 
c.customers_email_address, c.customers_telephone, a.entry_street_address, 
a.entry_city, a.entry_state, a.entry_country_id, n.countries_name, 
a.entry_zone_id, a.entry_postcode, r.referrer_customers_id, 
r.referrer_key, r.referrer_homepage, r.referrer_approved, 
r.referrer_banned, r.referrer_commission from  customers as c,  
address_book  as a, referrers  as r, countries as n  
where a.entry_country_id = n.countries_id and c.customers_id = r.referrer_customers_id 
and a.address_book_id = c.customers_default_address_id  order by c.customers_lastname;

What I would like to do, is instead of making a WHERE clause join, I'd like to nest the joins.

There are five database tables noted above. customers, address_book, referrers, countries, zones.

But I have no idea where to get started. The main problem with this is using the above statement, I seem to lose a few records from the select. This is because some records are using a 'zone_id' = 0. A fix to this was to simply create a blank record for 0 but besides that, can I use a join to fix this?

Upvotes: 0

Views: 146

Answers (1)

G-Nugget
G-Nugget

Reputation: 8846

The query written with explicit JOINs:

SELECT c.customers_id, c.customers_firstname, c.customers_lastname, 
    c.customers_email_address, c.customers_telephone, a.entry_street_address, 
    a.entry_city, a.entry_state, a.entry_country_id, n.countries_name, 
    a.entry_zone_id, a.entry_postcode, r.referrer_customers_id, 
    r.referrer_key, r.referrer_homepage, r.referrer_approved, 
    r.referrer_banned, r.referrer_commission
FROM customers AS c
    JOIN referrers AS r ON (c.customers_id = r.referrer_customers_id)
    JOIN address_book AS a ON (a.address_book_id = c.customers_default_address_id)
    JOIN countries AS n ON (a.entry_country_id = n.countries_id)
ORDER BY c.customers_lastname

If you want to also get the info from the zones table if a match exists, you would need to add a LEFT JOIN like this:

SELECT c.customers_id, c.customers_firstname, c.customers_lastname, 
    c.customers_email_address, c.customers_telephone, a.entry_street_address, 
    a.entry_city, a.entry_state, a.entry_country_id, n.countries_name, 
    a.entry_zone_id, a.entry_postcode, r.referrer_customers_id, 
    r.referrer_key, r.referrer_homepage, r.referrer_approved, 
    r.referrer_banned, r.referrer_commission
FROM customers AS c
    JOIN referrers AS r ON (c.customers_id = r.referrer_customers_id)
    JOIN address_book AS a ON (a.address_book_id = c.customers_default_address_id)
    JOIN countries AS n ON (a.entry_country_id = n.countries_id)
    LEFT JOIN {zones table name} AS z ON (z.{zones id column name} = a.entry_zone_id)
ORDER BY c.customers_lastname

and also add the columns you want to select to the top of the query. A LEFT JOIN always returns a result from the left table (first one listed) and if there is no match in the right table, it returns NULLs for the right table's columns.

Upvotes: 2

Related Questions