Return unique values

Databases
1

hi

I have 2 tables like this and i want to have names with unique year
i want query return 2 years(gamal,2006 & hady, 2002)
id|name

1|samy
2|ramy
3|gamal
4|hany
5|wael
6|hady

id|year

1|2005
2|2005
3|2006
4|2004
5|2004
6|2002

2

Homework?

It involves a JOIN, GROUP BY and HAVING COUNT(*) = 1

3

Question: why do you have two separate tables? Surely the simplest way of storing this data would be:

id, name, year
1, Sammy, 2005
2, Remmy, 2005

etc.

If there is a reason why you want to separate these out, you’ll need to associate the names table with the years table somehow. A better way would be like this:

table years (id, year)
1, 2002
2, 2003
3, 2004
4, 2005
5, 2006

table names (id, name, yearid):
1, Sammy, 4
2, Rammy, 4
3, Gamal, 5
4, Hany, 3
5, Wael, 3
6, Hady, 1

You would then join the tables like so:
SELECT id, name, year FROM names INNER JOIN years ON years.id = yearid

Then to select just the unique entries:
SELECT id, name, year FROM names INNER JOIN years ON years.id = yearid GROUP BY year HAVING COUNT(year) = 1

I haven’t tested it, but that should work.