Chaining results from multiple tables using SQL

I have a set of tables with following structures

**EntityFields**
fid | pid
1   | 1
2   | 1
3   | 2
4   | 2
5   | 1

**Language**
id | type    | value
1  | Entity  | FirstEntity
2  | Entity  | SecondEntity
1  | Field   | Name
2  | Field   | Age
3  | Field   | Name
4  | Field   | Age
5  | Field   | Location

Now as you may have understood, the first table gives the EntityField assignment to each Entity. The second table gives out the names for those IDs. What I want to output is something like the following

1  | FirstEntity / Name      (i.e. a concat of the Entity and the EntityField name)
2  | FirstEntity / Age
3  | FirstEntity / Location
4  | SecondEntity / Name
5  | SecondEntity / Age

Is this possible?

Thank you for the answers, unfortunately the table structure is something that I cannot change. The table structure it self belongs to another data directory system which is quite flexible and which I am using开发者_StackOverflow社区 to pull out data. I know that without providing the necessary background, this table structure looks quite weird, but it is something that works quite well (except in this scenario).

I will try out the examples here and will let you know.

For your current table structure, I think the following will work

SELECT EntityFields.fid, CONCAT(L1.value, ' / ' L2.value)
FROM EntityFields INNER JOIN Language as L1 ON EntityFields.pid=L1.id and L1.type='Entity'
INNER JOIN Language as L2 ON EntityFields.fid=L2.id and L2.type='Field'
ORDER BY EntityFields.fid

However, this query could be made much easier by having a better table structure. For example, with the following structure:

**EntityFields**
fid | pid | uid
1   | 1   | 1
2   | 1   | 2
1   | 2   | 3
2   | 2   | 4
3   | 1   | 5

**Entities**
id | value
1  | FirstEntity
2  | SecondEntity

**Fields**
id | value
1  | Name
2  | Age
3  | Location

you can use the somewhat simpler query:

SELECT uid, CONCAT(Entities.value, Fields.value)
FROM EntityFields INNER JOIN Entities ON EntityFields.pid=Entities.id
INNER JOIN Fields ON EntityFields.fid=Fields.id
ORDER BY uid

Well, I have no idea what you're trying to accomplish here. The fact that you label some records "Entity" and others "Field" and then try to connect them to each other makes it look to me like you are mixing two totally different things in the same table. Why not have an Entity table and a Field table?

You could get the results you seem to want by writing

select fid, le.value, lf.value
from entittyfields e
join language le on e.pid=le.id and type='Entity'
join language lf on e.fid=lf.id and type='Field'
order by fid

But I think you'd be wise to rethink your table design. Perhaps you could explain what you're trying to accomplish.

SELECT ef.fid AS id
     , COALESCE(e.value, '-', ef.pid, ' / ', f.value)
       AS entity_field
FROM EntityFields ef
  JOIN Language AS e
    ON e.id = ef.id
    AND e.type = 'Entity'
  JOIN Language AS f
    ON f.id = ef.id
    AND f.type = 'Field'
ORDER BY ef.pid
       , ef.fid

If I understand your question, which I don't think I do, this is simple. It appears to be a set of very poorly designed tables (Language doing more than one thing, for example). And it appears that the Language table has two types of records: a) The Entity records, which have type='Entity' and b) Field records, which have type='Field'.

At any rate, the way I would approach it is to treat the Language table as if it were two tables:

select ef.fid, Entities.value, Fields.value
from entityfields ef
  inner join language Entities
    on Entities.id = ef.id
    and Entities.type = 'Entity'
  inner join language Fields
    on Fields.id = ef.id
    and Fields.Type = 'Field'
order by 2, 3

First stab, anyway. That should help you get the answer.