SQL self join

[tpoettker via AccessMonster.com]

I am trying to create a query to use in a report. I am needing to self join
one table. The table consist of Family Number, Child Number, Wish Item. I
am needing the query to concatenate the wish item to one field.

So I need a field to look like this:


basketball baseball
soccer football

The results I am getting with my current SQL (listed below) is this:

basketball baseball
basketball soccer
basketball football

What am I doing wrong?

SQL:

SELECT DISTINCT [Child Wish List1].[Family Number], [Child Wish List1].[Child
Number], [Child Wish List1].[Child Wish] + " " + [Child Wish List2].
[Child Wish]
FROM [Child Wish List] as [Child Wish List1] LEFT JOIN [Child Wish List] as
[Child Wish List2] on [Child Wish List1].[Family Number] = [Child Wish List2].
[Family Number] and [Child Wish List1].[Child Number] = [Child Wish List2].
[Child Number] and [Child Wish List1].[Child Wish] <> [Child Wish List2].
[Child Wish];

 

[S.Clark]

Give a couple examples of the data.
(I'm trying to ascertain why you think you need a Self join, so feel free to
elaborate in other ways, but the data should speak for itself.)

 

[KARL DEWEY]

The first query assigns an item number to each wish. The second determines
the maximum wish number.
[Child Wish List_1] ---
SELECT Q.[Family Number], Q.[Child Number], Q.[Child Wish], (SELECT COUNT(*)
FROM [Child Wish List] Q1
WHERE Q1.[Family Number] = Q.[Family Number] AND Q1.[Child Number] =
Q.[Child Number]
AND Q1.[Child Wish] < Q.[Child Wish])+1 AS Item
FROM [Child Wish List] AS Q
ORDER BY Q.[Family Number], Q.[Child Number], Q.[Child Wish];

[Child Wish List_A] ----
SELECT [Child Wish List_1].[Family Number], [Child Wish List_1].[Child
Number], Max([Child Wish List_1].Item) AS XX
FROM [Child Wish List_1]
GROUP BY [Child Wish List_1].[Family Number], [Child Wish List_1].[Child
Number];

--
SELECT [Child Wish List_1].[Family Number], [Child Wish List_1].[Child
Number], IIf([Child Wish List_1].[Item] Mod 2=1,[Child Wish List_1_1].[Child
Wish],"") & " " & IIf([Child Wish List_1].[Item] Mod
2=1,[Child Wish List_1].[Child Wish],"") AS Wishes
FROM [Child Wish List_1] LEFT JOIN [Child Wish List_1] AS [Child Wish
List_1_1] ON ([Child Wish List_1].[Child Number] = [Child Wish
List_1_1].[Child Number]) AND ([Child Wish List_1].[Family Number] = [Child
Wish List_1_1].[Family Number])
WHERE ((([Child Wish List_1_1].Item)=[Child Wish List_1].[Item]+1) AND
(([Child Wish List_1].[Item] Mod 2)=1))
ORDER BY [Child Wish List_1].[Family Number], [Child Wish List_1].[Child
Number], [Child Wish List_1_1].Item
UNION SELECT [Child Wish List_1].[Family Number], [Child Wish List_1].[Child
Number], [Child Wish List_1].[Child Wish] AS Wishes
FROM [Child Wish List_1] INNER JOIN [Child Wish List_A] ON [Child Wish
List_1].Item = [Child Wish List_A].[XX]
WHERE [Child Wish List_A].[Family Number] = [Child Wish List_A].[Family
Number] AND [Child Wish List_1].[Child Number] = [Child Wish List_A].[Child
Number] AND [Child Wish List_1].Item mod 2 = 1 ;

--
Build a little, test a little.

I am trying to create a query to use in a report. I am needing to self join
one table. The table consist of Family Number, Child Number, Wish Item. I
am needing the query to concatenate the wish item to one field.

So I need a field to look like this:


basketball baseball
soccer football

The results I am getting with my current SQL (listed below) is this:

basketball baseball
basketball soccer
basketball football

What am I doing wrong?

SQL:

SELECT DISTINCT [Child Wish List1].[Family Number], [Child Wish List1].[Child
Number], [Child Wish List1].[Child Wish] + " " + [Child Wish List2].
[Child Wish]
FROM [Child Wish List] as [Child Wish List1] LEFT JOIN [Child Wish List] as
[Child Wish List2] on [Child Wish List1].[Family Number] = [Child Wish List2].
[Family Number] and [Child Wish List1].[Child Number] = [Child Wish List2].
[Child Number] and [Child Wish List1].[Child Wish] <> [Child Wish List2].
[Child Wish];

--



.