Complex selection using SQL

[mindsoul]

Complex selection using SQL only.

so i have the table as view in the attach file.

i want to select all the rows where term_lang=1 and don't have a corispondent(translation in my case) in the term_lang=2.

Praticly i want to select the terms in one particular language that are not yet translated in another.

this is the code, table

-- Version MySQL: 5.0.24

CREATE TABLE IF NOT EXISTS `terms` (
  `term_id` int(10) unsigned NOT NULL auto_increment,
  `term_category` tinyint(3) NOT NULL,
  `term_name` varchar(250) NOT NULL,
  `term_lang` int(2) NOT NULL,
  `term_relation_field` int(10) NOT NULL,
  PRIMARY KEY  (`term_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34 ;



INSERT INTO `terms` (`term_id`, `term_category`, `term_name`, `term_lang`, `term_relation_field`) VALUES
(1, 1, 'Caviale', 1, 1),
(2, 1, 'Insalata di mare', 1, 2),
(3, 6, 'Arrosto di maiale', 1, 3),
(6, 1, 'Seafood salat', 2, 2),
(7, 6, 'Cerdo asado', 4, 3),
(8, 1, 'Ensalada de mariscos', 4, 1),
(9, 1, 'Kaviar', 5, 1),
(28, 6, 'Cervella alla milanese', 1, 6),
(11, 1, 'kèviar', 8, 1),
(12, 1, 'caviale espaniol', 4, 1),
(13, 1, 'caviale fracais 2', 3, 1),
(14, 1, 'salade du mèr', 3, 2),
(15, 6, 'porc la gratar', 9, 3),
(19, 6, 'porc la gratar', 9, 3),
(20, 6, 'porca la gratar2', 9, 3),
(21, 6, 'le porc du france international', 3, 3),
(22, 1, 'икра', 7, 1),
(23, 1, 'Салат море', 7, 2),
(24, 1, 'meeresfrüchten salat', 5, 2),
(25, 6, 'Жаркое из свинины', 7, 3),
(26, 9, 'bresaola della valtelina', 1, 4),
(27, 9, 'salame di cinghiale', 1, 5),
(29, 6, 'Creier alla Milanese', 9, 6),
(30, 6, 'Cinghiale', 1, 7),
(31, 6, 'Porc mistret', 9, 7),
(32, 7, 'Astice', 1, ,
(33, 7, 'Crevete', 9, ;

 

 

[attachment deleted by admin]

[fenway]

What is your query so far?

[mindsoul]

i don't know how to do this using only sql, i have the solution using php, but the problem come's out when i work in php with big arrays, because i must select all the terms in one language then in the other language, compare and show the terms that must be translated (that are not yet translated), so when you work with multidimensional array with thousend of rows is not so big perfomance, that's why i must know if is possibile to do such a selection using SELECT and subqueries with wich i don't have a background.

[mindsoul]

i've manage to select terms in 2 languages

SELECT* FROM terms WHERE  
	term_lang IN (1,2)       

 

but i do not know hot to filter the terms already translated, the field term_relation_field tells me that but here I'm stacked

[mindsoul]

i think i have it

 SELECT* FROM terms WHERE  
	term_relation_field NOT IN (SELECT DISTINCT term_relation_field FROM terms WHERE term_lang=9) AND term_lang=1; 

very difficult all this nested select but helpful, I'll see the performance with thousand of rows

[fenway]

Try:

 

SELECT t1.* FROM terms AS t1

LEFT JOIN terms AS t2 ON ( t2.term_relation_field = t1.term_relation_field AND t2.term_lang = 9 )

WHERE t1.term_lang = 1 AND t2.term_relation_field IS NULL

 

 

[mindsoul]

i don't understand what are t1, t2 ? 2 virtual tables or something?

[fenway]

Table aliases.