Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Talking Unanswered: Confused - over thinking simple query

    Hi All,

    I have two tables: Oracle sql

    Table customer( userid username, date_oftrans) -- (20,000 rows)

    Table cust_info( userid keyid, balance) -- (59,000,000,000 rows)

    I want all the customers with their relevant info attached. How do i write the query so that it uses a join between table Customer and Cust_info so all the records from Customer re retrieved along with the relevant cust_info?

    this is what I have so far..

    Select a.Userid, a.Username,a. date, b.keyid, b.balance
    from customer a, Cust_info b
    where a.userid=b.userid
    group by
    a.Userid, a.Username, a. date, b.keyid, b.balance

    Would this retrieve the 20,000 records with there relevant details??

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What do you mean by "relevant cust_info" and/or "relevant details"?

    Would you rephrase that by using table names and column names.


    By the way,
    why did you mentioned "Oracle sql" in "Microsoft SQL Server" forum?

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    You are doing everything wrong!

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no ideA. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    A table is a set, so its name is a plural or collective noun. Do you really have only one customer as you said? Nah! Why are users in the Customers table?

    CREATE TABLE Customers -- more than one?
    (customer_id CHAR(16) NOT NULL
    customer_name VARCHAR(35) NOT NULL,
    transaction_date) DATE NOT NULL);

    I picked a credit card number as customer_id. I picked the USPS mailing label size for a user name. But a transaction is an event and not an attribute of a customer! Think about it! He has a relationship (Transactions) with some event. So this is not a valid table.

    But it gets worse! Why is customer information not part of the Customers table? There is a no such crap as a “key_id” in RDBMS! Learn data modeling. The meta-data element “key” is how something is used in one place in one table; it is not a valid datA. element name.

    A balance is a computed value on an account. We do not store computed values in SQL. What is the key? Did I guess the DRI correctly, since you were too rude to post DDL?

    CREATE TABLE Cust_Info -- invalid name!
    (customer_id CHAR(16) NOT NULL
    REFERENCES Customers(customer_id),
    key_id ??, -- absurd!
    something_balance DECIMAL (12,2) NOT NULL);

    >> I want all the customers with their relevant info attached. How do I write the query so that it uses a join between table Customer and Cust_info so all the records [sic] from Customer re retrieved along with the relevant cust_info? <<

    Rows are not records! This is a fundamental concept.

    >> this is what I have so far.. <<

    SELECT A.customer_id, A.user_name, something_date, B.key_id, B.something_balance
    FROM Customers AS A, Cust_info AS B
    WHERE A.user_id = B.user_id
    GROUP BY A.user_id, A.user_name, Something_date, B.key_id, B.something_balance;

    Why do you think that “A” is a good alias for Customers?? And “B” for Customer_Information? Another human being has to maintain your code; why are making it hard for them?

    Why are you grouping? DATE is a data type in SQL and cannot be a column name.

    We have no DDL, no specs, no sample data and you admit you are in the wrong forum. How can we help you if you will not do anything for us?

  4. #4
    Join Date
    Mar 2012
    Posts
    27
    Wow such hostility. Sorry for thinking this was a firm to ask for assistance. Further more why not just pass my question if it's that bad Tomuka & Celco

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mr Celko can be rather brash. He is very good at what he does, just not very good at this kind of interaction. I recommend that you do read and take on board his points, but don't read too much in to his tone.

    Is this an Oracle or SQL Server issue?
    If the former we can move it to that topic where you will likely receive more specific replies
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Yes, you can ask for assistance. No, you do not get away with violating Netiquette and not get called on it.

    You need a fundamental education, not assistance. Forums are not good for that; we expect that you have a minimal level of education and behavior.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Yes, you can ask for assistance. No, you do not get away with violating Netiquette and not get called on it.

    You need a fundamental education, not assistance. Forums are not good for that; we expect that you have a minimal level of education and behavior.
    ...and you have been told before that you do not dictate Netiquette on this community.
    In fact, you are the one who most frequently violates it.
    If it's not practically useful, then it's practically useless.

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

Tags for this Thread

Posting Permissions

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