Ben ik weer, met een query die niet werkt:
Dit werkt wel:
SELECT u.name, u.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active, n.author
FROM news n
INNER JOIN users u ON n.author = u.id
WHERE n.active = 1
AND n.deleted=0
AND n.date_posted <= NOW()
ORDER BY n.date_posted DESC
LIMIT 4
Maar als ik nu uit de uploadstabel (via de upload_items tabel) de filename wil ophalen, krijg ik heel andere en dus dubbele resultaten?
SELECT u.name, u.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active, n.author
FROM news n
INNER JOIN users u ON n.author = u.id
INNER JOIN uploads_items ui ON ui.KindID = n.id
INNER JOIN uploads up ON up.ID = ui.UploadID
WHERE n.active = 1
AND ui.Featured = 1
AND n.deleted=0
AND n.date_posted <= NOW()
ORDER BY n.date_posted DESC
LIMIT 4
Hoe kan dat?
UPDATE!!!!!!!! WANT HET GEFIXX0RED! JOEHEI!!!!Heel simpel, door deze query te gebruiken:
SELECT m.name, m.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active,
(SELECT up.filename FROM
uploads up INNER JOIN uploads_items ui ON
up.ID = ui.UploadID
WHERE ui.KindID = n.id
AND ui.Featured = 1
AND Uploadtype_ID = 7 LIMIT 1) AS filename
FROM nieuws n
INNER JOIN members m ON n.author = m.id
WHERE n.active = 1
AND n.deleted=0
AND n.date_posted <= NOW()
ORDER BY n.date_posted DESC
LIMIT 4
Samen met hulp van iemand op een ander forum dit gefabriceerd.
Hiephoi, ik ben weer blij
Nu nog even uitvogelen waar die subselect nou voor nodig is. Want dat had ik nog niet verwacht icm met een JOIN
.
