mysql left join question

I want make query select all names from table 'a' where from table 'b' i have id_one='3'. id_two is id record from table 'a', two records have relation from id_one='3'. How i can make query ?

CREATE TABLE IF NOT EXISTS `a` (
  `id` int(11) NOT NULL,
  `name` varchar(11) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Z开发者_运维技巧rzut danych tabeli `a`
--

INSERT INTO `a` (`id`, `name`, `value`) VALUES
(1, 'lalala', 0),
(2, 'allalala', 0);






CREATE TABLE IF NOT EXISTS `b` (
  `id_one` int(11) NOT NULL,
  `id_two` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Zrzut danych tabeli `b`
--

INSERT INTO `b` (`id_one`, `id_two`) VALUES
(3, 1),
(3, 2);

This is what you want:

select Name
from a inner join b on a.id = b.id_two
where b.id_one = 3

sorry I'm not fully understanding your schemas or your question, but I think what you're trying to ask for is:

SELECT * FROM a 
JOIN b on a.id = b.id_two;

Try that.

Your question title mentions left joins, but you don't need a left join to make the query you described.

Left joins are good for finding things that don't match up the way you'd expect. So, using a left join in this case depends on what you are looking for. If you are looking for b.id_two entries that don't have corresponding table a entries,

select Name, b.* from b left join a on a.id = b.id_two 

This will give you a table that lists every row in table b, with NULLs in place of the names for table a where there is no match.

Likewise, if you are looking for names that don't have entries in b.id_two, you would use

select Name, b.* from a left join b on a.id = b.id_two 

If you want to enforce that there is always a correspondence, you can define a foreign key constraint between the parent and child table.

select Name
from a join b on a.id = b.id_two
where b.id_one = 3;

Will also work to get your answer. Might I also suggest you significantly improve your create table statements to include indexing. E.G.

CREATE TABLE IF NOT EXISTS `a` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(11) NOT NULL,
 `value` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `b` (
  `id_one` bigint(20) NOT NULL,
  `id_two` bigint(20) NOT NULL,
  KEY `FKCAFBB09382DEAC` (`id_one`),
  CONSTRAINT `b_a_1` FOREIGN KEY (`id_two`) REFERENCES `a` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I would also use bigint for primary keys and use charset=utf8
these days it is just to common to want to migrate application to multi-lingual, lay the ground work now. IMHO