SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join 5 queries...I think I have it right but it's not working

    Here is my dilema. I am not sure if I need to do a LEFT INNER JOIN or just a JOIN. I have all of this is a information coming from an osCommerce database with the following tables:

    customers
    customers_id customers_gender customers_firstname customers_lastname customers_dob customers_email_address customers_default_address_id customers_telephone customers_fax customers_password customers_newsletter customers_employee

    address_book
    address_book_id customers_id entry_gender entry_company entry_firstname entry_lastname entry_street_address entry_suburb entry_postcode entry_city entry_state entry_country_id entry_zone_id

    customers_info
    customers_info_id customers_info_date_of_last_logon customers_info_number_of_logons customers_info_date_account_created customers_info_date_account_last_modified customers_info_source_id global_product_notifications

    zones
    zone_id zone_country_id zone_code zone_name

    sources
    sources_id sources_name

    my query

    Code:
    $query = mysql_query ("SELECT customers.customers_firstname,
                           customers.customers_lastname,
                           customers.customers_email_address, 
                           customers.customers_telephone, 
                           customers.customers_fax, 
                           address_book.entry_company, 
                           address_book.entry_street_address, 
                           address_book.entry_suburb, 
                           address_book.entry_city, 
                           address_book.entry_postcode, 
                           address_book.entry_country_id, 
                           address_book.entry_zone_id, 
                           zones. zone_id,
                           zones.zone_code, 
                           customers_info.customers_info_source_id,
                           sources.sources_id, 
                           sources.sources_name
      FROM customers
    JOIN address_book.entry_zone_id
      ON zones.zone_id = zones.zone_code
    JOIN customers_info.customers_info_source_id
      ON sources.sources_id = sources.sources_name
    JOIN address_book
      ON customers.customers_id = address_book.customers_id");
    while($row = @mysql_fetch_array($query))
    What I am trying to do is get this to work for an excel download of all of the information instead of all of the codes that represent zones and states.

    I know this is alot of info, but any help would be greatly appriciated.

    Rob

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when you say JOIN something, the something has to be a table name

    in your query, some of them are column names

    also, i can't figure out how the customers_info table relates to the customers table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The "customers_info" has the "source_id", which in my case it is a printer, but it is a code. I want the actual source name which is in the table "sources".

    As far as Joining all of this together, I thought I was doing that correctly by:

    JOIN customers_info.customers_info_source_id
    ON sources.sources_id = sources.sources_name

    I want to identify the column "customers_info_source_id" from the "customers_info" and take the ID from the "sources" table and make that identify with the name....??

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    JOIN customers_info.customers_info_source_id is not right, because it refers to a column

    and yes, i understand you want the customers_info table related to the sources table, but how does the customers_info table relate to the customers table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...so it should be:

    Code:
    JOIN customers_info
    Becasue I am telling it to relate those two fields on the customers_info table in the ON statement ???

    As for how the customers_info table relates to the customers table it is from the ID's.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WebRob
    Becasue I am telling it to relate those two fields on the customers_info table in the ON statement ???
    correct


    Quote Originally Posted by WebRob
    As for how the customers_info table relates to the customers table it is from the ID's.
    okay, i believe you -- but which ones??

    customers
    customers_id customers_gender customers_firstname customers_lastname customers_dob customers_email_address customers_default_address_id customers_telephone customers_fax customers_password customers_newsletter customers_employee

    customers_info
    customers_info_id customers_info_date_of_last_logon customers_info_number_of_logons customers_info_date_account_created customers_info_date_account_last_modified customers_info_source_id global_product_notifications
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry...

    customers_id and customers_info_id are the same in both tables.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
      FROM customers
    INNER  
      JOIN address_book
        ON address_book.customers_id 
         = customers.customers_id 
    INNER  
      JOIN zones
        ON zones.zone_id 
         = address_book.entry_zone_id
    INNER  
      JOIN customers_info
        ON customers_info.customers_info_id 
         = customers.customers_id 
    INNER
      JOIN sources    
        ON sources.sources_id 
         = customers_info.customers_info_source_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy...this is awesome, thank you so much.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my pleasure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy...I have one more question!!

    I am trying to add one more piece to my puzzle.
    Code:
    SELECT customers.customers_firstname, customers.customers_lastname, customers.customers_email_address, customers.customers_telephone, customers.customers_fax, address_book.entry_company, address_book.entry_street_address, address_book.entry_city, address_book.entry_suburb, address_book.entry_postcode, address_book.entry_country_id, address_book.entry_zone_id, zones.zone_id, zones.zone_code, countries.countries_id, countries.countries_name, customers_info.customers_info_source_id, sources.sources_id, zones.zone_country_id, sources.sources_name
    FROM customers
    INNER
    JOIN address_book ON address_book.customers_id = customers.customers_id
    INNER
    JOIN zones ON zones.zone_id = address_book.entry_zone_id
    INNER
    JOIN customers_info ON customers_info.customers_info_id = customers.customers_id
    INNER
    JOIN sources ON sources.sources_id = customers_info.customers_info_source_id
    INNER
    JOIN countries ON countries.countries_name = zones.zone_country_id
    If you nothice at the end, I want to add the country name to the query. I am already grabbing the zones.zone_country_id from the query and I want to match that to the countries.countries_name. I am trying to follow what you did but it always returns an empty result.

    Any thoughts that might help???

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are joining an id to a name

    that's never going to match values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what I mean is that the ID on one table corresponds with the ID on another table. That table also has a name, like this:

    countries.countries_id, countries.countries_name

    So I want to grab that info, and the countries id will match this:

    zones.zone_country_id

    I want
    zones.zone_country_id = countries.countries_name

    and since countries.countries_id & countries.countries_name are on the same row, I will have the countries_name

    I hope I explained that right.

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    no, you want the join to be zones.zone_country_id = countries.countries_id and then you put countries.countries_name at the beginning of the query in the field list.

    you keep messing up one ciritical piece of joins: the join condition (the part that comes after ON) tells mysql what columns in the two tables have THE SAME data.

  15. #15
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much for the help. You showed me how the JOIN works and now I understand it.

    I just have one questions....why is it an INNER JOIN??


Bookmarks

Posting Permissions

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