Re: lug-bg: Mysql table comparison.

[Andrey Andreev <andreev@xxxxxxxxxxxxxx>]

Ivan Bogouchev wrote:
> > SELECT danni.* FROM `kriterii`, `danni` WHERE kriterii.userid = 219433 
> > AND (kriterii.a IS NULL OR danni.foo=kriterii.a) AND (kriterii.b IS 
> > NULL OR danni.bar=kriterii.b)
> Ииии, много хитро умно :))
> Като се замисля какви измишльотини на Perl съм правил за подобно 
> упражнение ;(
> Само да попитам, някой знае ли, дали има някакви ограничения за 
> дължината WHERE ?
Не "знам", но не ми се вярва да има ограничение което да е практически 
достижимо. Мисля си че queryтата могат да са си страхотно дълги. Хехе, 
сетих се за нещо което писах преди години което ме научи да си 
проектирам внимателно схемите преди да почна да пиша.
Долното е единствено query (бачка на PostgreSQL), което показва че 
основното ограничение в дължината na queryто е прага на болка, която 
начинаещ писач на SQL е в състояние да достигне преди да реши да си 
оправи схемата. :)
SELECT DISTINCT *
FROM
(
SELECT reservation.id, surname, name, rdate, topic, location, rtime
FROM reservation
INNER JOIN users ON users.id=reservation.id
WHERE id1='"+session.getValue("uid")+"' AND rdate>=date_trunc('day', now())
UNION
SELECT reservation.id1, surname, name, rdate, topic, location, rtime
FROM reservation
INNER JOIN users ON users.id=reservation.id1
WHERE reservation.id='"+session.getValue("uid")+"' AND 
rdate>=date_trunc('day', now())
UNION
SELECT reservation.id, groupname, '', rdate, topic, location, rtime
FROM reservation
INNER JOIN groups ON groups.id1=reservation.id
WHERE reservation.id1='"+session.getValue("uid")+"' AND 
rdate>=date_trunc('day', now())
UNION
SELECT reservation.id1, groupname, '', rdate, topic, location, rtime
FROM reservation
INNER JOIN groups ON groups.id1=reservation.id1
WHERE reservation.id='"+session.getValue("uid")+"' AND 
rdate>=date_trunc('day', now())
UNION
SELECT reservation.id, surname, name, rdate, topic, location, rtime
FROM reservation
INNER JOIN users ON users.id=reservation.id
INNER JOIN group_user ON group_user.id1=reservation.id
INNER JOIN groups ON group_user.id=groups.id
WHERE groups.id1='"+session.getValue("uid")+"' AND 
rdate>=date_trunc('day', now())
UNION
SELECT reservation.id1, surname, name, rdate, topic, location, rtime
FROM reservation
INNER JOIN users ON users.id=reservation.id1
INNER JOIN group_user ON group_user.id1=reservation.id1
INNER JOIN groups ON group_user.id=groups.id
WHERE groups.id1='"+session.getValue("uid")+"' AND 
rdate>=date_trunc('day', now())
UNION
SELECT reservation.id, g1.groupname, '', rdate, topic, location, rtime
FROM reservation
INNER JOIN groups AS g1 ON g1.id1=reservation.id
INNER JOIN group_user ON group_user.id1=reservation.id
INNER JOIN groups AS g2 ON g2.id=group_user.id
WHERE g2.id1='"+session.getValue("uid")+"' AND rdate>=date_trunc('day', 
now())
UNION
SELECT reservation.id1, g1.groupname, '', rdate, topic, location, rtime
FROM reservation
INNER JOIN groups AS g1 ON g1.id1=reservation.id1
INNER JOIN group_user ON group_user.id1=reservation.id1
INNER JOIN groups AS g2 ON g2.id=group_user.id
WHERE g2.id1='"+session.getValue("uid")+"' AND rdate>=date_trunc('day', 
now())
)
AS allreservations
ORDER BY rdate, rtime;


Андро

ПП: Пускам горното под Public Domain :) Ако някиой иска да взаимства 
похвати от него - жална му майка! ;)
ПП2: Честит 3 Март на всички!

--
Andrey Andreev
University of Helsinki
Dept. of Computer Science
============================================================================
A mail-list of Linux Users Group - Bulgaria (bulgarian linuxers).
http://www.linux-bulgaria.org - Hosted by Internet Group Ltd. - Stara Zagora
To unsubscribe: http://www.linux-bulgaria.org/public/mail_list.html
============================================================================