Member Avatar for thebigo

Hello
I have two tables a Master and Child. I want to return a recordset that has all of the Master records and any related child values.
Sample Data:
Master Table
'id' 'field1'
1 Test1
2 Test2
3 Test3
Child Table
'id' 'masterID' 'field1'
1 2 Child1The following code will only return one record:

SELECT master.id, master.field1, child.field1
FROM master INNER JOIN child on master.id = child.masterID
WHERE child.id = 1

Resulting recordset:
2,Test2, Child1
What I need to achieve is:
Resulting recordset:
1,Test1,NULL
2,Test2,Child1
3,Test3,NULL
I hope that makes sense. I am not even sure if it is possible.

I have tried using various OUTER JOIN combinations but the result is the same.

Regards
James

  • 2 Contributors
  • 1 Reply
  • 116 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by Ramy Mahrous
Member Avatar for Ramy Mahrous

You just didn't try right outer join

SELECT     master.id AS [Master ID], master.field1 AS [Master field], child.field1 AS [Child Field]
FROM         child RIGHT OUTER JOIN
                      master ON child.masterid = master.id
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.