0
SELECT D.dname, L.dlocation
FROM DEPARTMENT D, dept_locations L

Output:

dname   dlocation
Research    Houston
Research    Stafford
Research    Bellaire
Research    Houston
Research    Sugarland
Administration  Houston
Administration  Stafford
Administration  Bellaire
Administration  Houston
Administration  Sugarland
Headquarters    Houston
Headquarters    Stafford
Headquarters    Bellaire
Headquarters    Houston
Headquarters    Sugarland

Desired resurt:

dname   dlocation
Headquarters    Houston
Administration  Stafford
Research    Bellaire
Research    Houston
Research    Sugarland
Fahmi
  • 37,315
  • 5
  • 22
  • 31
Samuel
  • 52
  • 7
  • There are already answers for selecting distinct records: https://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns – BlueWater86 Jan 31 '19 at 11:35
  • No join condition?!? Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh Jan 31 '19 at 11:36
  • That expected result doesn't match the question... – jarlh Jan 31 '19 at 11:38
  • The only duplicates I see are those with 'Houston' as dlocation. But your desired result is removing a lot, and in a way that i can't understand. why do Headquarters and Administration only have 1 result each when there are 4 distinct values for each, it's not as though they are even the 1st instance of each. I think we need more clarity on the logic of your desired output before we can help. – ChrisCarroll Jan 31 '19 at 11:46

1 Answers1

0

You need inner join

SELECT D.dname, L.dlocation
FROM DEPARTMENT D inner join dept_locations L
on d.departmentid=l.departmentid
Fahmi
  • 37,315
  • 5
  • 22
  • 31