Andreas,
What you're looking for is a syntax like CASE WHEN ... JOIN .... I think
that's not in SQL99 (just off the top of my head, such a syntax would seem
likely to defeat any optimiser). You're going to have to (i) store the
results of an initial query in a temp table and work from there, or (ii) use
a scripting language. If the system is going to be running for a while,
choosing (ii) now might save you a little time later when MySQL implements
stored procs (since it will be easier to port a script to a SP).
PB
----- Original Message -----
From: Andreas Ahlenstorf
To: [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 5:49 PM
Subject: 'Selective' joins
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]
