Most Basic SQL Question

[kerry2807]

Hi, Am starting to teach myself SQL. I cannot figure out how to do the most simple of queries, and it is driving me nuts. Here's the basic scenario:

Table: Customer
custid
firstname
lastname

Table: Transactions
custid
transdate
transamount

I want a query which will display ALL customers and any associated transactions. However, the query I used (below) only returns the customer if there is a transaction for it. For customers with no transactions, they are not being listed and I need help to show me how to structure the query to that the listing of the master table is not dependent on there being any child records in existence.

select customer.custid, customer.firstname, customer.lastname, transactions.transdate, transactions.transamount 
from customer, transactions where customer.custid=transactions.custid

Please tell me where I am going wrong!!!

[bartonc]

You'll find SQL experts in the "Database" section under the "Forums" tab, above. There may not be an SQL expert who checks these Misc. Q.s, so try group that most closely matches the tools that you are using (the most popular RDBMSs are listed there).

Good luck.

[bartonc]

Hi, Am starting to teach myself SQL. I cannot figure out how to do the most simple of queries, and it is driving me nuts. Here's the basic scenario:

Table: Customer
custid
firstname
lastname

Table: Transactions
custid
transdate
transamount

I want a query which will display ALL customers and any associated transactions. However, the query I used (below) only returns the customer if there is a transaction for it. For customers with no transactions, they are not being listed and I need help to show me how to structure the query to that the listing of the master table is not dependent on there being any child records in existence.

select customer.custid, customer.firstname, customer.lastname, transactions.transdate, transactions.transamount 
from customer, transactions where customer.custid=transactions.custid

Please tell me where I am going wrong!!!

Have you tried just leaving off the where clause?

[MMcCarthy]

You don't say what database system you are using so the syntax will vary a bit. You will need to use a LEFT JOIN or a LEFT OUTER JOIN. Something like the following

SELECT customer.custid, customer.firstname, customer.lastname, transactions.transdate, transactions.transamount 
FROM customer LEFT JOIN transactions 
ON customer.custid = transactions.custid
 

However, as I said the syntax will vary. If you tell us the database system you are using we can try to help further.