andre j wrote:
> table one: medicans
>
> medican_ID medicanname
> 1 - prednison
> 2 - example
> 3 - anotherexample
>
> table two: bad_together ( includes the medicans that cant be used together ).
>
> medican_one medican_two
> 1 - 2
> 1 - 3
> 2 - 3
From this table I note you do not store the reverse relations (i.e. if you have (1,2), indicating that 1 cannot be used together with 2, I assume also that 2 cannot be used together with 1, so (2,1) is also a bad combination). This design decision implicates the use of UNION.
(One has to be careful with these kind of applications - wrong results can be very dangerous ;-)
> Now, i need the names of the medicans that cant be used together.
The query below list dangerous combinations one by one:
SELECT
m1.name AS `If you take`,
m2.name AS `Do not take`
FROM bad_together
JOIN medicans AS m1 ON m1.id = bad_together.id1
JOIN medicans AS m2 ON m2.id = bad_together.id2
UNION ALL
SELECT
m1.name AS `If you take`,
m2.name AS `Do not take`
FROM bad_together
JOIN medicans AS m1 ON m1.id = bad_together.id2
JOIN medicans AS m2 ON m2.id = bad_together.id1;
If you are using MySQL 4.1.x, you can use GROUP_CONCAT to get a more concise result:
SELECT
`If you take`, GROUP_CONCAT(`Do not take`) AS `Do not take`
FROM (
SELECT
m1.name AS `If you take`,
m2.name AS `Do not take`
FROM bad_together
JOIN medicans AS m1 ON m1.id = bad_together.id1
JOIN medicans AS m2 ON m2.id = bad_together.id2
UNION ALL
SELECT
m1.name AS `If you take`,
m2.name AS `Do not take`
FROM bad_together
JOIN medicans AS m1 ON m1.id = bad_together.id2
JOIN medicans AS m2 ON m2.id = bad_together.id1
) AS m
GROUP BY `If you take`;
--
felix
Please use
BBCode to format your messages in this forum.