Manuele wrote:
This might sound silly to many... so sorry in advance...
in mysql4
Suppose I have 2 tables:
tableA has 3 columns, 2 of them reference the same column of tableB Example:
TableA (Items) Id - FirstType - SecondType 0 - 1 - NULL 1 - 2 - 3 ....
TableB (Types) Id - Description 1 - DummyType 2 - AnotherDummyType 3 - MoreDummies
Is there a way to get 'Description' field for all records in tableA related to FirstType AND SecondType ? I have tried a union query, but with no luck... Also tried any possible (to me) variant of JOINS... no luck either The problem is that I am only able to get TableB.Description once, possibly for the first match...
What I would need is a resoult of this type:
TableA.Id - TableB.Description (of TableA.FirstType) - TableB.Description (of TableA.SecondType) 0 - DummyType - NULL 1 - AnotherDummyType - MoreDummies
Hope I made myself clear as possible... Any help appreciated
Thanks, Manuele
P.S. This would be easy to make if one could make a synonym for TableB or a 'virtual' copy of it ... maybe a view? But so far mysql hasn't views... so I need to bypass the problem at application level, wich isn't really that good for me... Any use of multiple queries would also be not good... I need to make this in a single query ... if possible
You can make a virtual copy of a table with aliases. Something like this:
SELECT A.Id, B1.Description, B2.Description FROM TableA A, TableB B1, TableB B2 WHERE A.FirstType = B1.Id AND A.SecondType = B2.Id ORDER BY A.Id;
As you can see, there is one copy of TableB aliased as B1 and another copy of TableB aliased as B2. Unfortunately, this won't quite work with your sample data. You get:
+------+------------------+-------------+ | Id | Description | Description | +------+------------------+-------------+ | 1 | AnotherDummyType | MoreDummies | +------+------------------+-------------+
There is no result where TableA.Id = 0 because of the NULL in SecondType. One solution would be to alter your data slightly. Something like:
UPDATE TableA SET SecondType=0 WHERE SecondType IS NULL; INSERT INTO TableB (Id, Description) VALUES (0,NULL);
That is, instead of storing a NULL key in the SecondType column of TableA, store a key which points to a row of TableB with a NULL Description. Then, the same query I gave above yields
+------+------------------+-------------+ | Id | Description | Description | +------+------------------+-------------+ | 0 | DummyType | NULL | | 1 | AnotherDummyType | MoreDummies | +------+------------------+-------------+
which is the result you requested.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
