Hello, I'm having a tricky problem: I've got a database table (PicklistData with three fields, which I have to use for a join to get the data, which is associated with them. They are called EAN, ISRC and LabelProductCode. Until now, there's nothing difficult. But a thing, which makes the problem insoluble for me: This three fields may be empty, only one must be filled out (and we can't do anything against that fact).
CREATE TABLE EncodingData ( StationID int(20) NOT NULL default '0', ProdID int(20) NOT NULL default '0', MediaNr int(20) NOT NULL default '0', Status int(11) NOT NULL default '0', PRIMARY KEY (StationID,ProdID,MediaNr), KEY StationID (StationID), KEY ProdID (ProdID), KEY MediaNr (MediaNr) ) TYPE=MyISAM; CREATE TABLE PicklistData ( ID int(10) unsigned NOT NULL auto_increment, Picklist int(10) unsigned NOT NULL default '0', EAN varchar(13) NOT NULL default '', ISRC varchar(12) NOT NULL default '', LabelProductCode varchar(50) NOT NULL default '', PRIMARY KEY (ID), KEY Picklist (Picklist), KEY EAN (EAN) ) TYPE=MyISAM; CREATE TABLE ProdID ( StationID int(11) NOT NULL default '0', ProdID int(20) NOT NULL auto_increment, EAN varchar(13) default NULL, LabelSpezProdCode varchar(254) NOT NULL default '', PRIMARY KEY (ProdID,StationID), KEY StationID (StationID), KEY ProdID (ProdID), KEY EAN (EAN) ) TYPE=MyISAM; CREATE TABLE Trackdata ( StationID int(11) NOT NULL default '0', ProdID int(11) NOT NULL default '0', MediaNr int(11) NOT NULL default '0', ISRC varchar(12) default NULL, PRIMARY KEY (StationID,ProdID,MediaNr), KEY StationID (StationID), KEY ProdID (ProdID), KEY MediaNr (MediaNr) ) TYPE=MyISAM; If PicklistData.EAN is not empty, I use this query: SELECT d.status FROM Picklists AS a JOIN PicklistData AS b ON a.ID = b.Picklist JOIN ProdID AS c ON b.EAN = c.EAN JOIN EncodingData AS d ON c.StationID = d.StationID AND c.ProdID = d.ProdID WHERE a.ID = '1' But if PicklistData.EAN is empty, I have to replace 'JOIN ProdID AS c ON b.EAN = c.EAN' by 'JOIN Trackdata AS c ON b.ISRC = c.ISRC' or 'JOIN ProdID AS c ON b.LabelProductCode = c.LabelSpezProdCode' to get the two fields StationID and ProdID at the end. So, my big problem is: How to make that with MySQL without using any scripting language and without big performance problems (the tables have a lot of records)? If it isn't possible like that, is it possible with minor changes on the database structure? Regards, Andreas Ahlenstorf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]