Joining tables with sql

Question

I have two tables i wold like to join. One contains Customers, named "Customers". The other table "Customeraccounts". The tables have relation on Customer id. Like this.

Customers
1, john, Smith

CustomerAccounts
1, 1, 12345678
2, 1, 98765432

How can i join the tables to the output would be:

1, john, smith, 12345678, 98765432

What have you tried? What didn't work? You know you need to JOIN, did you look at the documentation for the JOIN clause? — Oded, Jun 16 '11 at 09:27
@Oded: A JOIN would not be enough to do this. @espen: Why do you want to create this kind of output in SQL? That's something for the application layer, not for the database. — Tomalak, Jun 16 '11 at 09:30
i've tried all the join clauses i could find, i thought i wolud get it right with the inner join. But i get john, smith, 12345678 john, smith, 98765432 My goal is to get all the accounts for a customer in one line. — espen, Jun 16 '11 at 09:31

score 1 · Answer 1 · edited Jun 16 '11 at 12:41

1

SELECT * FROM Customer RIGHT OUTER JOIN CustomerAccounts
ON Customer.customer_id=CustomerAccounts.customer_id;

edited Jun 16 '11 at 12:41

Johan

74,508
24
191
319

answered Jun 16 '11 at 09:30

Jayesh

1,511
1
16
37

If you want it in a single row then you will have to apply some logic as well, apart from SQL. – Jayesh Jun 16 '11 at 09:54
select the code block and press +K to make it look like source code. – Johan Jun 16 '11 at 12:42

score 1 · Answer 2 · answered Jun 16 '11 at 09:49

1

Maybe this would work for you:

SELECT Customers.Id, 
       Customers.LastName, 
       Customers.FirstName,
       STUFF((SELECT ',' + CustomerAccounts.Number
              FROM CustomerAccounts
              WHERE CustomerAccounts.CustomerId = Customers.Id
         FOR XML PATH('')
        ),1,1,'') 
       AS AllCustomerAccounts   
FROM Customers

answered Jun 16 '11 at 09:49

daniel.herken

1,095
7
19

that one almost did the trick. that puts the acounts in to one column. – espen Jun 16 '11 at 09:55
would it be posible to split the string in to one column each. Like |Account 1| acount 2| account 3 |? – espen Jun 16 '11 at 10:02
hm, maybe but i don't know how :-( – daniel.herken Jun 16 '11 at 11:12

score 1 · Answer 3 · edited May 23 '17 at 10:34

In MySQL you can do

SELECT 
  Cust.ID
  , Cust.Forname
  , Cust.Name
  , GROUP_CONCAT(Acc.Number) as AccNumber
FROM CustomerAccount Acc 
INNER JOIN Customers Cust ON Acc.ID_Customer = Cust.ID 
GROUP BY Cust.id

In SQL server you'll have to tweak it a bit and do:

SELECT     
  Cust.ID,
  , Cust.Forname  
  , Cust.Name
  , AccNumber = replace                           
     ((SELECT A2.number AS [data()]                               
       FROM CustomerAccount A2 
       INNER JOIN Customers C2 ON A2.ID_Customer = C2.ID
       WHERE Cust.id = C2.id  
       ORDER BY A2.number FOR xml path('')), ' ', REQUIRED SEPERATOR) 
FROM CustomerAccount Acc  
INNER JOIN Customers Cust ON Acc.ID_Customer = Cust.ID 
GROUP BY Cust.id, cust.Forname, cust.Name

See also this question: Simulating group_concat MySQL function in Microsoft SQL Server 2005?

score 0 · Answer 4 · answered Jun 16 '11 at 09:35

0

Select
Acc.ID,
Cust.Forname,
Cust.Name,
Acc.Number

From CustomerAccount Acc
Inner Join Customers Cust
On Acc.ID_CUstomer = Cust.ID

I assume your column are

CustomerAccount ID|ID_Customer|Number

Customers ID|ForName|Name

I think It'll work

answered Jun 16 '11 at 09:35

bAN

13,375
16
60
93

Joining tables with sql

4 Answers4