Hallo,
ich führe folgendes SQL-Query aus und bekomme den Error 1048 "Column 'id' cannot be null".
Ich weiß, es ist ein sehr langes Query, aber wichtig ist eigentlich nur, dass beide getrennt voneinander, also ohne UNION, einwandfrei funktionieren .. Auch, wenn ich die ganzen "MATCH AGAINST"-Anweisungen entweder aus dem SELECT oder dem WEHRE-Bereich rausnehme.
[highlight=sql](
SELECT e.id,e.title as title, 'all' as date,
MIN(ed.begin) as begin,MAX(ed.end) as end ,
(MATCH(e.title) AGAINST ('')) as score_event,
(MATCH(a.name) AGAINST ('')) as score_address,
MAX((MATCH(t.tag) AGAINST (''))) as score_tags,
(MATCH(c.name) AGAINST ('')) as score_county,
(MATCH(d.name) AGAINST ('')) as score_district
FROM events e
JOIN addresses a ON a.id = e.address_id
JOIN tags_rels tr ON (tr.entry_id = e.id AND tr.type = 'e')
JOIN tags t ON t.id = tr.tag_id JOIN counties c ON c.id = a.county_id
JOIN districts d ON d.id = a.district_id
JOIN events_dates ed ON (DATE(ed.begin) <= '2012-4-30' AND DATE(ed.end) >= '2012-4-01') AND ed.event_id = e.id
WHERE e.sedase = 0 AND DATE(end) >= DATE(NOW()) AND e.authorized > 0 AND
(
(MATCH(e.title) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(a.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(t.tag) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(c.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(d.name) AGAINST ('' IN BOOLEAN MODE))
)
GROUP BY e.id)
UNION
(SELECT e.id,e.title as title, ed.id as date,
ed.begin as begin,ed.end as end
,(MATCH(e.title) AGAINST ('')) as score_event,
(MATCH(a.name) AGAINST ('')) as score_address,
MAX((MATCH(t.tag) AGAINST (''))) as score_tags,
(MATCH(c.name) AGAINST ('')) as score_county,
(MATCH(d.name) AGAINST ('')) as score_district
FROM events e
JOIN addresses a ON a.id = e.address_id
JOIN tags_rels tr ON (tr.entry_id = e.id AND tr.type = 'e')
JOIN tags t ON t.id = tr.tag_id JOIN counties c ON c.id = a.county_id
JOIN districts d ON d.id = a.district_id JOIN events_dates ed ON (DATE(ed.begin) <= '2012-4-30' AND DATE(ed.end) >= '2012-4-01') AND ed.event_id = e.id
WHERE e.sedase = 1 AND DATE(end) >= DATE(NOW()) AND e.authorized > 0 AND
(
(MATCH(e.title) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(a.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(t.tag) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(c.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(d.name) AGAINST ('' IN BOOLEAN MODE)) )
)[/highlight]
Hat jemand eine Idee?
Vielen Dank.
Johannes
ich führe folgendes SQL-Query aus und bekomme den Error 1048 "Column 'id' cannot be null".
Ich weiß, es ist ein sehr langes Query, aber wichtig ist eigentlich nur, dass beide getrennt voneinander, also ohne UNION, einwandfrei funktionieren .. Auch, wenn ich die ganzen "MATCH AGAINST"-Anweisungen entweder aus dem SELECT oder dem WEHRE-Bereich rausnehme.
[highlight=sql](
SELECT e.id,e.title as title, 'all' as date,
MIN(ed.begin) as begin,MAX(ed.end) as end ,
(MATCH(e.title) AGAINST ('')) as score_event,
(MATCH(a.name) AGAINST ('')) as score_address,
MAX((MATCH(t.tag) AGAINST (''))) as score_tags,
(MATCH(c.name) AGAINST ('')) as score_county,
(MATCH(d.name) AGAINST ('')) as score_district
FROM events e
JOIN addresses a ON a.id = e.address_id
JOIN tags_rels tr ON (tr.entry_id = e.id AND tr.type = 'e')
JOIN tags t ON t.id = tr.tag_id JOIN counties c ON c.id = a.county_id
JOIN districts d ON d.id = a.district_id
JOIN events_dates ed ON (DATE(ed.begin) <= '2012-4-30' AND DATE(ed.end) >= '2012-4-01') AND ed.event_id = e.id
WHERE e.sedase = 0 AND DATE(end) >= DATE(NOW()) AND e.authorized > 0 AND
(
(MATCH(e.title) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(a.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(t.tag) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(c.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(d.name) AGAINST ('' IN BOOLEAN MODE))
)
GROUP BY e.id)
UNION
(SELECT e.id,e.title as title, ed.id as date,
ed.begin as begin,ed.end as end
,(MATCH(e.title) AGAINST ('')) as score_event,
(MATCH(a.name) AGAINST ('')) as score_address,
MAX((MATCH(t.tag) AGAINST (''))) as score_tags,
(MATCH(c.name) AGAINST ('')) as score_county,
(MATCH(d.name) AGAINST ('')) as score_district
FROM events e
JOIN addresses a ON a.id = e.address_id
JOIN tags_rels tr ON (tr.entry_id = e.id AND tr.type = 'e')
JOIN tags t ON t.id = tr.tag_id JOIN counties c ON c.id = a.county_id
JOIN districts d ON d.id = a.district_id JOIN events_dates ed ON (DATE(ed.begin) <= '2012-4-30' AND DATE(ed.end) >= '2012-4-01') AND ed.event_id = e.id
WHERE e.sedase = 1 AND DATE(end) >= DATE(NOW()) AND e.authorized > 0 AND
(
(MATCH(e.title) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(a.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(t.tag) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(c.name) AGAINST ('' IN BOOLEAN MODE)) OR
(MATCH(d.name) AGAINST ('' IN BOOLEAN MODE)) )
)[/highlight]
Hat jemand eine Idee?
Vielen Dank.
Johannes
Comment