Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Unanswered: VERY URGENT!! Searching data in another Table for specific Value

    Hello Guys, I have a huge problem with searching data from one table into another table for a specific value. Let me explain bellow;

    I have Table A with Column called "Order" as shown bellow;

    Table A
    Column name:

    Order
    123456
    234567
    345678
    456789


    I have another Table B there is a column called "Order_and_Description" it contains the values bellow;

    Table B
    Column name:

    Order_and_Description
    123456-From,USA
    234567-From,Africa
    345678-From,Asia

    and I have another Column in Table B there I have the specific values for each Warehouse

    Column name:
    Type
    A
    B
    C


    MY Question is;

    How to compare values from Table-A column called "Order" in column "Order_and_description" in Table-B for a specific value in Column "Type" in Table-B?
    It should search for the first 6 order digits in column "Order_and_description"

    Please help me

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ...
      FROM tableA
    INNER
      JOIN tableB
        ON LEFT(tableB.order_and_description,6) = tableA.order
       AND tableB.type = 'C'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    There is no LEFT function in Postgres. That should be
    Code:
    ON substr(tableB.order_and_description, 1, 6) = tableA.order
    But you should better fix your database design and split order_and_description into two separate columns.

  4. #4
    Join Date
    Aug 2010
    Posts
    5
    Quote Originally Posted by r937 View Post
    Code:
    SELECT ...
      FROM tableA
    INNER
      JOIN tableB
        ON LEFT(tableB.order_and_description,6) = tableA.order
       AND tableB.type = 'C'
    Using this query i get error message "function left(character varying, integer) does not exist"

    I guess you also need my table info;

    -- Table: "Table_B"

    -- DROP TABLE "Table_B";

    CREATE TABLE "Table_B"
    (
    "TYPE" character varying(255),
    "Order_And_Description" character varying(255),
    "Location" character varying(255),
    )
    WITH (
    OIDS=FALSE
    );
    ALTER TABLE "Table_B" OWNER TO postgres;

  5. #5
    Join Date
    Aug 2010
    Posts
    5
    Quote Originally Posted by shammat View Post
    There is no LEFT function in Postgres. That should be
    Code:
    ON substr(tableB.order_and_description, 1, 6) = tableA.order
    But you should better fix your database design and split order_and_description into two separate columns.
    I did try to do that, but the data i get in text file is the same. This is how it should be according to the rules. they make the rules i get the headache

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Jamalo View Post
    I did try to do that, but the data i get in text file is the same. This is how it should be according to the rules. they make the rules i get the headache
    Just because that's the way the import file is structure doesn't mean you need to keep that bad design in your tables...

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Just because that's the way the import file is structure doesn't mean you need to keep that bad design in your tables...
    +1

    You'll get all kinds of stuff coming to you in poorly designed ways. Fix it when you import it into your schema.

    Oh, and threads marked "Urgent", "Very Urgent", "OMG I will die if this isn't fixed today" make me not want to even read them. If it is really that important, there are lots of consultants who will take your money in exchange for a high priority solution. Otherwise, wait in line with everyone else.
    Last edited by artacus72; 08-13-10 at 13:14.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by artacus72 View Post
    Oh, and threads marked "Urgent", "Very Urgent", "OMG I will die if this isn't fixed today" make me not want to even read them.
    +1

    i used to have a sign in my office that said

    FAILURE TO PLAN
    ON YOUR PART
    DOES NOT CONSTITUTE
    AN EMERGENCY
    ON MY PART
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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