sql

imena polj?

prvi stolpec 1, drugi 2

select b.1 , b.2 from detail b , master a
where b.2 <> 'vrata' 
and b.2 = 'kolo' 
and a.1 = b.1 
and a.1 = b

BRBR je 2. okt 2023 ob 09:34 izjavil:

Zadnja vrstica ?

Test tabele;

CREATE TABLE IF NOT EXISTS `1_detail` (
  `id` varchar(50) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `prop` varchar(50) COLLATE utf8_slovenian_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;


INSERT INTO `1_detail` (`id`, `prop`) VALUES
	('a', 'kolo'),
	('a', 'vrata'),
	('b', 'kolo');


CREATE TABLE IF NOT EXISTS `1_master` (
  `id` varchar(50) COLLATE utf8_slovenian_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;


INSERT INTO `1_master` (`id`) VALUES
	('a'),
	('b');

sql kao, ki pa ne dela:

select b.id , b.prop from 1_detail b , 1_master a
where b.prop <> 'vrata' 
and b.prop = 'kolo' 
and a.id = b.id 

Najlepša rešitev bi bila nosql baza, najenostavnejša pa subquery.

SELECT m.id
FROM master m
LEFT JOIN detail AS d1 ON d1.id = m.id
WHERE (SELECT d2.id FROM detail d2 WHERE d2.id=m.id AND d2.prop='vrata') IS NULL

Če je performančno problem, pa kak dodatn view, ki predprocesira podatke (označi vsakemu masteru 0/1 kot has_vrata).