SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Joining Query

  1. #1
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining Query

    Ok, I dont Know If I can do this.

    I have 2 Tables, suppliers and accounts.

    Suppliers contain supplier_number, supplier_name.

    Accounts contain supplier_number, customer_number, account_number.

    I am using customer_number 268 for example. When I query the Access DB I would Like all supplier_numbers, supplier_names from the suppliers table where customer_number 268 is present in the accounts table.

    Code:
    SELECT suppliers.supplier_number,
    			suppliers.supplier_name,
    			accounts.account_number
    		FROM suppliers
    	INNER 
    		JOIN suppliers
    			ON suppliers.supplier_number = accounts.supplier_number
    		WHERE accounts.customer_number = 268
    Does this make sence?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select suppliers.supplier_number
         , suppliers.supplier_name
         , accounts.account_number
      from accounts
    inner 
      join suppliers
        on accounts.supplier_number
         = suppliers.supplier_number
     where accounts.customer_number = 268
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    select suppliers.supplier_number
         , suppliers.supplier_name
         , accounts.account_number
      from accounts
    inner 
      join suppliers
        on accounts.supplier_number
         = suppliers.supplier_number
     where accounts.customer_number = 268
    I am using ColdFusion and an Access DB. When I insert your query I get:

    Code:
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft]
    [ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    When I try and run the query in Access SQL view I get a prompt asking me for a supplier_name.

    Is there a reason for this and is this parameter I am missing?

    Thanks Rudy, U Rock!

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mdumka
    When I try and run the query in Access SQL view I get a prompt asking me for a supplier_name.

    Is there a reason for this and is this parameter I am missing?
    The prompt means that it's not recognizing that field name...are you sure the field name is spelled exactly as it is in the table?

  5. #5
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should select the field you intend to filter.
    In your select statement you:
    'SELECT accounts.account_number' but you filter:
    'WHERE: accounts.customer_number=268'

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    andy, i disagree

    if you are selecting all the accounts for customer 268, why in the world do you need to select the customer number? it's going to be 268 on every row...

    it's not required, and typically not desired, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, you're right again.
    Well, my intentions were good.

    Andy

  8. #8
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Null, you are right!

    my field name was not supplier_name but name. I just used supplier_name in my post here to make my question clearer, and when I copied rudy's query the field name was wrong.

    Thanks Folks!


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
  •