Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Lightbulb Unanswered: HELP with a Select/Union statement

    I have 3 tables One table is the order Table, Bill to table and ship to table
    I have to Views created as followed

    This query uses the Ship to table to pull the ship to information to the shipping system.
    SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
    FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);


    This query uss the Bill to as the ship to inforamtion
    SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
    FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;


    I need this infroamtion in one table which I have done in the UNION statement as followed:
    SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
    FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
    UNION ALL
    SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
    FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

    Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

    NAME ADDR_1 ADDR_2 ADDR_3 CITY STATE ZIPCODE COUNTRY SHIP_VIA ID
    DIEBOLD INC (4076A) ATTN: RANCE AARON 343 MANOR DR PACIFICA CA 94044 18932
    DIEBOLD, INC OH UPS #88X08X 18932

    MY POINT: Is there a way to select a over all DISTINCT order ID.

    Thank you for any help hope this make sense!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by cle5cap
    ...I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.
    Define what you mean by "first line". The order of data in a database has no meaning. Do you have a date field or some other value you can sort on to determine which shipping information is "first"?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    29
    Sorry
    In the Union query the first select statement will Always be the correct shipping information when the ORDER form has one(Shipping info) if it does not have a Shipping address printed on the form then the second select statement will pull from the bill table which will only have one record per the order number.

    Example Order form:
    Bill-to Ship to information
    Bill to Compnay Ship to company
    Bill to address Ship to address
    Bill to city Ship to City
    Bill to State Ship to state
    Etc Etc

    When this order occurs then there is one record in the bill to table with that info and 1 record in the Ship to table with the shipping info This is where the problme occurs when I do the UNION it looks in the Shipping table first and pulls the shipping info and then the other statement in the UNION pulls the BILL to info which give me 2 records in the Query so what happends in this case I pull the info using ODBC to my shipping system and it says it found 2 records but I only what the First select statement to show. Another problem is when the ship to is not fill out I need to use the second part of the UNION select statement in order ot pull the bill to info for the shipping info but this works correctly due to only 1 order number exists in the bill to tableEX:

    Example Order form:
    Bill-to Ship to information
    Bill to Compnay SAME
    Bill to address
    Bill to city
    Bill to State
    Etc
    When this occurs then I need to use the Bill to table for the shipping info.

    Hopfully this clears things up.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So this query:
    Code:
    SELECT	Cust_address.NAME,
    	Cust_address.ADDR_1,
    	Cust_address.ADDR_2,
    	Cust_address.ADDR_3,
    	Cust_address.CITY,
    	Cust_address.STATE,
    	Cust_address.ZIPCODE,
    	Cust_address.COUNTRY,
    	Cust_address.SHIP_VIA,
    	customer_order.ID
    FROM	Cust_address
    	INNER JOIN customer_order
    		ON Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID
    		AND Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO
    ...contains all the orders that you need, but in some cases the address information is missing and you want to pull address information from the CUSTOMER table to fill it in?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    29
    Yes Sir that is correct!

Posting Permissions

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