Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2010
    Posts
    38
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Triple Join Type Query help pse

    I have an OsCommerce store and wanted to modify a customer lookup query to include the delivery address postcode so that i can search for a customer based on his delivery postcode which might be different to his normal home address postcode.

    The original query is as follows:-

    Code:
    select a.entry_company, c.customers_id, c.customers_lastname, c.customers_telephone, c.customers_mobile_number, c.customers_firstname, c.customers_email_address, a.entry_country_id,a.entry_city,a.entry_state from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id where a.entry_postcode like '%EX8 2NX%' OR a.entry_postcode like '%EX82NX%' and 1 order by c.customers_id DESC
    but i want to include another lookup of delivery_postcode which is in the orders table, the common field for all tables is customers_id (although there may be more than one order in the orders table for the same customers_id)

    I need to add something like the following to the query and a join to the orders table:-


    Code:
    OR o.delivery_postcode like '%EX8 2NX%' OR o.delivery_postcode like '%EX82NX%'
    any help appreciated as joins are beyond my meagre sql skills

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    You already have a join in that query

    customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id

    simply add the extra join to the end of that piece of the query the same way.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    fuzioneer (01-08-2014)

  • #3
    New Coder
    Join Date
    Nov 2010
    Posts
    38
    Thanks
    6
    Thanked 0 Times in 0 Posts
    thx for replying, i am not sure how to add it on, could you show me the completed query ?

  • #4
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    select a.entry_company, c.customers_id, c.customers_lastname, c.customers_telephone, c.customers_mobile_number, c.customers_firstname, c.customers_email_address, a.entry_country_id,a.entry_city,a.entry_state
    from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id left join orders o on c.customers_id = o.customers_id
    where a.entry_postcode like '%EX8 2NX%' OR a.entry_postcode like '%EX82NX%' OR o.delivery_postcode like '%EX8 2NX%' OR o.delivery_postcode like '%EX82NX%'
    order by c.customers_id DESC
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    fuzioneer (01-09-2014)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •