LEFT JOIN - comparing 1

[nego78]

CREATE TABLE `katglowny_Katalog` (
  `Katalog_Id` int(11) unsigned NOT NULL auto_increment,
  `Katalog_Adres` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`Katalog_Id`),
  UNIQUE KEY `IX_Adres` (`Katalog_Adres`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `katglowny_Kategorie` (
  `Kategorie_Id` int(11) unsigned NOT NULL auto_increment,
  `Dostawcy_Id` smallint(6) unsigned NOT NULL default '0',
  `Kategorie_Nazwa` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`Kategorie_Id`),
  KEY `IX_Dostawcy` (`Dostawcy_Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `katglowny_KategorieDoKatalogu` (
  `Kategorie_Id` int(11) unsigned NOT NULL default '0',
  `Katalog_Id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Kategorie_Id`,`Katalog_Id`),
  KEY `IX_KatKateg` (`Katalog_Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

In katglowny_Kategorie i have about 12000 records
I can divide in to 2 groups by Dostawcy_Id, So value in Dostawcy_Id can be 1 or 2

I'd like to select all records from katglowny_Katalog
which belongs to Dostawcy_Id = 2 in katglowny_Kategorie
AND NOT belongs to Dostawcy_Id = 1 in katglowny_Kategorie
i'd like to select distinct records

From which tabel i should start query ?

What should i repair to make it works ?

SELECT * FROM katglowny_Kategorie AS a
LEFT JOIN katglowny_KategorieDoKatalogu AS b USING (Kategorie_Id)
LEFT JOIN katglowny_Katalog AS c USING (Katalog_Id)
LEFT JOIN katglowny_KategorieDoKatalogu AS d USING (Katalog_Id)
LEFT JOIN katglowny_Kategorie AS e ON e.Kategorie_Id=d.Kategorie_Id AND e.Dostawcy_Id = 1
LEFT JOIN katglowny_Kategorie AS f ON f.Dostawcy_Id = 1 AND f.Kategorie_Nazwa = a.Kategorie_Nazwa
WHERE a.Dostawcy_Id = 2 AND e.Kategorie_Id IS NULL

gry online

 

[r937]

select Katal.Katalog_Id
     , Katal.Katalog_Adres
  from katglowny_Katalog as Katal
inner
  join katglowny_KategorieDoKatalogu as KK
    on Katal.Katalog_Id
     = KK.Katalog_Id
inner
  join katglowny_Kategorie as Kateg 
    on KK.Kategorie_Id
     = Kateg.Kategorie_Id
group
    by Katal.Katalog_Id
     , Katal.Katalog_Adres
having 0 
     < sum(case when Kateg.Dostawcy_Id = 2
                then 1 else 0 end)          
   and 0 
     = sum(case when Kateg.Dostawcy_Id = 1
                then 1 else 0 end)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)