[RESOLVED] Result from different rows as one row

Piranha

I don't even know how to explain my problem, so I try to do an example instead. Let's say that I have the following two tables:

CREATE TABLE species (
    id int,
    weight varchar(50),
    PRIMARY KEY (id)
);

CREATE TABLE names (
    id int,
    speciesid int,
    name varchar(50),
    language varchar(50),
    PRIMARY KEY (id),
    FOREIGN KEY (speciesid) REFERENCES species (id)
);

Each species can have names in several languages: latin, english and swedish (and it may be expanded later on). I want to be able to get one row with information from both tables like this:

id    weight    latinName    englishName    swedishName
1     45        Fake Latin   Fake Eng       Swe Fake
2     55        Lat2         Eng2           Swe2

I have the code below, and I can get it to work if I search for one id. But when I want a list as above I have no idea how to handle it, I get in trouble when when the subqueries return more than one result each. Do anyone know how I can do to get this working?

SELECT 
    species.id, 
    species.weight, 
    (SELECT names.name
	FROM names
	WHERE names.language = 'swe') as swe,
    (SELECT names.name
	FROM names
	WHERE names.language = 'eng') as eng,
    (SELECT names.name
	FROM names
	WHERE names.language = 'lat') as lat
FROM
    species,
    names
WHERE
    species.id = names.speciesid
GROUP BY
    species.id

sneakyimp

I think you need a join. i'm not super knowledgeable about joins, but think this might work

SELECT s.id, s.weight, n1.name as latinName, n2.name as englishName, n3.name as swedishName
FROM species s
LEFT JOIN names n1
ON n1.speciesid=s.id
LEFT JOIN names n2
ON n2.speciesid=s.id
LEFT JOIN names n3
ON n3.speciesid=s.id

WHERE n1.language='lat'
AND n2.language='eng'
AND n3.language='swe'

I haven't test that...it may or may not work.

Piranha

Ty for the help. I didn't even think about the possibility to join many times with the same table and give it different names. I had to modify the code a little to be able to get results even when there is a null value, look below for the change:

SELECT s.id, s.weight, n1.name as latinName, n2.name as englishName, n3.name as swedishName
FROM species s
LEFT JOIN names n1
ON n1.speciesid=s.id
AND n1.language='lat'
LEFT JOIN names n2
ON n2.speciesid=s.id
AND n2.language='eng'
LEFT JOIN names n3
ON n3.speciesid=s.id
ANd n3.language='swe'