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
a.Userid, a.Username, a. date, b.keyid, b.balance
Would this retrieve the 20,000 records with there relevant details??
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?
>> 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?
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