CODEWITHSUNDEEP
mysqlsqldatabase
jrdn
jrdn

Reputation: 840

Querying all data from MySQL employees sample database

I'm trying to query all the data from the MySQL Employees sample database. (structure and row counts)

I'm trying to do this in a way that preserves the relationships between all the tables, but I've never had to query a database this last, and honestly, I'm getting lost in Joins and such. I would really appreciate any help.

Thanks!

Edit: SQL:

SELECT *
FROM
    `employees`
    LEFT JOIN `salaries`
      ON `employees`.`emp_no` = `salaries`.`emp_no`
    LEFT JOIN `dept_manager`
      ON `employees`.`emp_no` = `dept_manager`.`emp_no`
    LEFT JOIN `titles`
      ON `employees`.`emp_no` = `titles`.`emp_no`,
    JOIN
      `departments` on `departments`
        ON `departments`.`dept_no` = `dept_emp`.`dept_no`

LIMIT 50;

Upvotes: 1

Views: 2581

Answers (2)

Kamil
Kamil

Reputation: 13931

Try this:

SELECT * from -- you can specify fields instead of *
employees e
join salaries s on e.emp_no = s.emp_no,
join titles t on e.emp_no = t.emp_no, 
join dept_emp de on e.emp_no = de.emp_no,depts,
join departments d on d.emp_no = t.emp_no,
join dept_manager dm on d.dept_no = dm.dept_no,
join employees edm on dm.emp_no = edm.emp_no -- second instance of employees to join employee who is a dept manager

Upvotes: 1

Kamil
Kamil

Reputation: 13931

I try to explain joins simple as possible.

Lets say, we have to store some relational information: person (Name, Surname) and few E-mails assigned to person.

First - we save person information in table persons. We need unique row identifier personId, that will allow us to point exact person in persons database.

table persons
personId, name, surname
       1, John, Foo
       2, Mike, Bar

When we have table like this, we can store persons emails in other table. In this case, creating unique row identifier is not necessary, but its a good practise to store these row identifiers in all tables. PersonId field is telling us what person owns that e-mail.

table emails
emailId, personId, address
      1,        1, [email protected]
      2,        1, [email protected]
      3,        2, [email protected]
      4,        2, [email protected]

Now we can select data with join.

SELECT persons.Name, persons.Surname, emails.address
FROM 
    persons 
    join
    emails
ON
    persons.personId = emails.personId

That query will return data (persons with addresses assigned to them).

  John, Foo, [email protected]
  John, Foo, [email protected]
  Mike, Bar, [email protected]
  Mike, Bar, [email protected]

Is that clear?

If joins are not clear for you - maybe visit phpcademy.org or thenewboston.org - they have very good tutorials about many languages and techologies, including SQL.

Added later:

You can also join multiple tables, like this:

SELECT primarykey, key1,key2, other, fields, detail_t_1.something
FROM
    master_t
JOIN
    detail_t_1 on detail_t_1 
    ON master_t.key1 = detail_t_1.key1,

    detail_t_2 on detail_t_2 
    ON master_t.key2 = detail_t_2.key2

Added later:

If there are multiple "detail" rows (like 2 emails in my example) - returned data will contain one person multiple times, with each email address.

In SQL returned data is always in form of table, not tree.

Upvotes: 2

Related Questions