September 30th, 2015, 11:40 AM
Join Date: Sep 2015
Time spent in forums: 41 m 35 sec
Reputation Power: 0
Subquery in select wrong,how different columns foreach translation in right table?
Up untill now i allways made my query's in S acces with the query builder, and i assume that by building query on query it takes a long time to execute the query (sometimes up untill 60 minutes...).
That's why i'm trying to learn the sql language now and i hope that that might make those query's more efficient and faster.
i have a standard tablet with possible colours "dbo.t_colour"
and a table with translations in 4 different languages "dbo.d_colour".
with my sql statement i now can make a list where for every colour i have 4 rows, with in each row a translation.
But now i want to make a query where i have only 1 row for each colour and a extra column for each translation.
so: colour, language 1, language 2 and so on...
Before in MS acces i used to make a query first for each language with the fields id_colour and the translation.
Then i would make a fifth query in which i linked the translation query's to my colour table througt the id_colour.
i presume this can be done in 1 sql statement?
with some research i came to this:
[SQL]select k.id_colour, k.colour_code, k.active, c.id_colour,c.id_language, c.description , c.active,
(select c.description from dbo.d_colour where c.id_language = 1 )as vertaling_eng
from dbo.t_colour k join dbo.d_colour C on k.id_colour = c.id_colour
where k.active = 1[/SQL]
But then i get a message saying that the subquery gives me more than 1 value, and it gives an error.
Could someone guide me to a solution or tell me where i could find some info on how to proceed?
thanx very much!!