I'm having this main table with references to files in it:
files +-----+-------------+ | id | filename | +-----+-------------+ |168 |v008-12.jpg| +-----+-------------+
Then I have this table to relate one file to one or several categories:
relatedtable +-----+--------+------+ | id | fromid | toid | +-----+--------+------+ | 4 | 1 | 2 | | 257 | 2 | 2 | +-----+--------+------+
Which lies in this table for categories:
categories +----+--------------+ | id | categoryname | stickword +----+--------------+ | 5 | demonstrations | | 6 | people | +----+--------------+
The following sql:
SELECT DISTINCT
files.id, files.filename, categories.categoryname
FROM
files
LEFT JOIN
relatedtable
ON
files.id = relateratabell.fromid
LEFT JOIN
kategorier
ON
relatedtable.toid = categories.id
WHERE
(relatedtable.fromid IS NULL OR relatedtable.fromid IS NOT NULL)
AND
files.stickword LIKE '%basta%' //for example
ORDER BY
filename;Gives the following result:
+---+-----------+--------------------------+ | id | filename | categoryname | +---+-----------+--------------------------+ | 166| v007-86.jpg | demonstrations | | 166| v007-86.jpg | people | | 167| v008-03.jpg | demonstrations | | 167| v008-03.jpg | people | +---+-----------+--------------------------+
This is what I expected it to, but I'ld rather get a result as this one:
+---+-----------+--------------------------+ | id | filename | categoryname | +---+-----------+--------------------------+ | 166| v007-86.jpg | demonstrations, people | | 167| v008-03.jpg | demonstrations, people | +---+-----------+--------------------------+
Where the categories have been collected together into the same field, so that I don't get multiple rows for the same file… Is this possible to achieve?
Many many thanks to the one who can give me some input!
Sincerely
Victor
-- Att kriga för fred är som att knulla för att bli oskuld
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
