Get results from two different tables using a complex query or procedure. Read the details please.

Question

I have two tables.

1. employees(id, name)
2. documents(id, employee_id, file_path, document_type[PASSPORT, DL, PAN_CARD])

I want to list these details like this

-------------------------------------------------
| Employee | Passport | DL | PAN_CARD|
| ABC | 1 | 1 | 0 |
| AFG | 0 | 1 | 1 |
| XYZ | 2 | 1 | 1 |
-------------------------------------------------

*Here integer value denotes the number of scanned documents saved with a employee_id in 'documents' table.
*Sometimes we have to scan a document from both side or multiple pages. So multiple documents with same 'document_type' and 'employee_id' can exists(Example 'XYZ' with 2 passport document).

Answer 1

do it with inner join

Answer 2

you should use a 3th table like documentOfEmpl

Answer 3

the table documents must have only the type of document with these columns:
idDoc
desc

example:
0 1
passport id card

Answer 4

the table documentOfEmpl have these columns:
employe,typeDocument,file_path

Answer 5

SELECT employees.name, documents.PASSWORD,documents.DL,documents. PAN_CARD FROM documents, employees WHERE employees.id=documents.employees_id ORDER BY employees.id

I have assume that id colume in employees table is employees id. If they are not please tell me which colume in both of tables are same. If their is no colume same create a table with employees id in employees table. Remember you have to create a link between two tables. If there is no link how you perform search query.

Answer 6

That won't work because here is one to many relationship between both tables and also results from table documents needs to be grouped by document_type.