How to avoid duplicate values in joining two or three tables?

Question

I have this two tables and I want to join their two ID's.

Household Info                             
1
2
3

Household Members
1
1
1
2
3
3
3
3
3

The values is repeating over and over again, as you have noticed on my screenshot. The output I want is, I want a query of this:

Household Info.HID                Household Members.HID
1                                 1
                                  1
                                  1
2                                 2
3                                 3
                                  3
                                  3
                                  3
                                  3

Since in the Table Household Info there are only 3 HID while the table Household Members there are three:1, one:2, and five:3

Hope you can help me on this one :3 EDITED: I am using Microsoft Access as RDBMS

Answer 1

For an RDBMS which supports CTE...

DECLARE @Household  TABLE 
    ( Household  VARCHAR(10))
;

INSERT INTO @Household
    ( Household )
VALUES
    (1),
    (2),
    (3)
;


declare @HouseholdMembers TABLE 
    ( HouseholdMembers  VARCHAR(10))
;

INSERT INTO @HouseholdMembers
    ( HouseholdMembers )
VALUES
    (1),
    (1),
    (1),
    (2),
    (3),
    (3),
    (3),
    (3),
    (3)
;

Select 
CASE WHEN RN = 1 THEN Household ELSE '' END Household,
HouseholdMembers 
from (
select h.Household,
hm.HouseholdMembers,
ROW_NUMBER()OVER(PARTITION BY hm.HouseholdMembers ORDER BY h.Household)RN from @Household h
LEFT JOIN @HouseholdMembers hm
ON hm.HouseholdMembers = h.Household)T

Answer 2

You didn't mention what are you using as RDBMS.

I think that you can use pivot for your case:

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

or to use grouping:

select c2, c3
  , sum( case when no_of_days <= 7 then 1 else 0 end) as dlt8
  , sum( case when no_of_days between 8 and 14 then 1 else 0 end) as d8to14
  , sum( case when no_of_days between 15 and 21 then 1 else 0 end) as d15to21
  , sum( case when no_of_days between 22 and 27 then 1 else 0 end) as d22to27
from mytable
group by c2, c3
order by c2, c3;

Here you can find similar answer to your question:

Dynamic alternative to pivot with CASE and GROUP BY

Edit 1

If you need something like this:

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

you can check this example:

Concatenate many rows into a single text string?

Edit 2

And the last option that I can remember is this one. It's for MySQL but you can reuse the logic:

MySQL JOIN - Return NULL for duplicate results in left table