Need help with SQL Query

[InverseOfNeo]

I want to iterate though an entire table of id numbers and amounts so for each id number it adds up the amounts to give a total for each id. Then I want to display the persons name and credit card number from the id. Here's my tables (I've left out extraneous columns):

table1
id    amount

table2
id    name    creditcardnumber

The output I want is:
id name creditcardnumber total
For each id in table2, there may be multiple or one or no records for it in table1. Table structure doesn't matter that much here, so I could add another column to table2 for the total if this would be easier. I hope this makes sense.

 

[InverseOfNeo]

I think I figured this out...
I could do something like this but there is probably a better way of doing it:

SELECT id AS person,SUM(amount) AS total1 FROM table1
GROUP BY id

UPDATE table2 SET total2=total1 
WHERE id=person

Then do a simple select statement to display what I want.

 

[KLin]

SELECT id as Person, Name, CreditCardNumber, Sum(Amount) As Total
FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id

This is assuming that the id columns relate to each other. id in Table2 is unique, but there could more than one record with the same id that relates to one single id in table1.

 

[InverseOfNeo]

Joins confuse the hell out of me. If I wanted to insert that into a new column, let's name it total, into table 2, would I just do (update because table2 will have info already):

UPDATE table2
SET table2.total = Sum(table1.Amount)
FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id

 

[KLin]

Joins confuse you? You need to do some reading on the basics of RDBMS's then.

 

[InverseOfNeo]

I know I know.... I am in the process of doing that. And I greatly appreciate your help!

 

[Evadman]

Joins confuse the hell out of me

Both of these do the same thing, the syntax is slightly different depending on the rdbms, but one should work.

select 
    table1.id, name, creditcardnumber, sum(amount) as total
from 
    table1, table2
where
    table1.id = table2.id
group by
    table1.id, name

select 
    table1.id, name, creditcardnumber, sum(amount) as total
from 
    table1 cross join table2
where
    table1.id = table2.id
group by
    table1.id, name

 

[BoberFett]

Please tell me that this is for a school project and that you're not doing this for a company which completely ignores PCI security...

 

[InverseOfNeo]

Bober, this is not for a company so no worries. I have been reading about PCI security anyway.