Help for JOIN

Join

[deimon]

Hello,
I have few tables, thats i wont to join ..

USPORADANI :
SELECT prostory.idobjektu FROM prostory, kapacity WHERE kapacity.idprostoru = prostory.id AND kapacity.idkategorie='1' AND kapacity.idusporadani = '5' AND kapacity.min <= '300' AND kapacity.max >= '300' group by prostory.idobjektu

CHARAKTER :
SELECT prostory.idobjektu FROM prostory, charakter_cross WHERE charakter_cross.idprostoru = prostory.id AND charakter_cross.idkategorie='1' AND charakter_cross.idcharakteru = '4' group by prostory.idobjektu

TYP :
SELECT prostory.idobjektu FROM prostory, typ_cross WHERE typ_cross.idprostoru = prostory.id AND typ_cross.idkategorie='1' AND typ_cross.idtyp = '3' group by prostory.idobjektu

on this i have every 1/3 of result.. i need this join into one mysql query . .where i have on end ID, name, and some next items from table objekty

in table "prostory" is "prostory.idobjektu = objekty.id" and some next items.

I mean something as :

SELECT objekty.prednazev, objekty.nazev FROM objekty WHERE JOIN [CHARAKTER SELECT] AND JOIN [TYP SELECT] AND JOIN [USPORADANI SELECT] objekty.expirace <= '".date("Y-m-d")."' ......

thanks

[deimon]

For better understanding..

its webfilter on page .. on frontend I need output only from table Objekty (id, nazev,...)

in table prostory are rows (id, idobjektu, povoleno)

---

Rules for selecting output are :

0) table prostory must be povoleno = 1

1) table charakter_cross (idprostoru, idkategorie, idcharakteru)

If we selected on page charakter .. we know :
IDcharakter for example 10 ..
we know where we are (idkategorie) .. for example 4
=> i need only all IDprostoru from .. and this rule add to main query.

2) table typ_cross (idprostoru, idkategorie, idtyp)

If we selected on page typ .. we know :
its as before .. only change tables... we know
where we are - idkategorie = 4,
we know IDtyp for example 9,
and we need list of IDprostoru and this rule add to main query

3) rule is special :-) there is two option..

a) if we give Uspořádání = 0 (default value) we have min = 5 and max = 50 values .. this we select in basic query .. its rows in table Objekty

b) is as before .. table kapacity (idprostoru idkategorie idusporadani min max)

If we selected on page Uspořádání and it isnt 0 .. we know :

where we are - idkategorie = 4,
we know idusporadani for example 9,
and value, thats we give (for ex. 18) is between min and max,
and we need list of IDprostoru and this rule add to main query

---

So, if we give (1) OR (2) OR both ( (3) we know everytime.. only with different option ).. we need specify MySQL query, thats wrote Objekty.id , Objekty.nazev

---

if you have some questions.. pls wrote there .. i know about this all.. but i dont wrote mulifunction query.

Many thanks for all..

[deimon]

So .. I wrote it to there ..

SELECT obj.id, top.pozice, obj.okres, obj.kraj, obj.prednazev, obj.nazev, obj.sef, obj.ulice, obj.mesto, obj.psc, obj.gps, obj.email, obj.www, obj.bew1, obj.perex1

FROM objekty AS obj, prostory AS pro

INNER JOIN kapacity AS kap ON kap.idprostoru = pro.id AND kap.idusporadani = '5' AND kap.idkategorie = '1'

INNER JOIN charakter_cross AS cha ON cha.idprostoru = pro.id AND cha.idkategorie = '1' AND cha.idkategorie = '1' 

INNER JOIN typ_cross AS typ ON typ.idprostoru = pro.id AND typ.idkategorie = '1' AND typ.idkategorie = '1' 

LEFT JOIN zvyhodneni AS top ON obj.id = top.idobjektu
AND top.okres = '0'
AND top.kraj = '0'
AND top.kategorie = '1'

WHERE

pro.idobjektu = obj.id
AND obj.kraj = '10'
AND obj.okres = '24'
AND obj.expirace > NOW() 

AND kap.min <= '500' AND kap.max >= '500'

GROUP BY obj.id
ORDER BY top.pozice DESC, obj.nazev ASC
LIMIT 0 , 20

but its problem in #1054 - Unknown column 'obj.id' in 'on clause' .. its in "LEFT JOIN zvyhodneni AS top ON obj.id = top.idobjektu"

without this error .. its "0.5025 second" .. its ok if every table have about 1000-1500 rows ?

is there some next errors or some recommendation ?

[LifeinAsia]

"top" may be a reserved word (it is for some databases). What if you try "top1" instead of "top" as the alias for zvyhodneni?

[deimon]

1) You mean change it to :

SELECT obj.id, top.pozice, obj.okres, obj.kraj, obj.prednazev, obj.nazev, obj.sef, obj.ulice, obj.mesto, obj.psc, obj.gps, obj.email, obj.www, obj.bew1, obj.perex1

FROM objekty AS obj, prostory AS pro

INNER JOIN kapacity AS kap ON kap.idprostoru = pro.id AND kap.idusporadani = '5' AND kap.idkategorie = '1'

INNER JOIN charakter_cross AS cha ON cha.idprostoru = pro.id AND cha.idkategorie = '1' AND cha.idkategorie = '1'

INNER JOIN typ_cross AS typ ON typ.idprostoru = pro.id AND typ.idkategorie = '1' AND typ.idkategorie = '1'

LEFT JOIN zvyhodneni AS top1 ON obj.id = top1.idobjektu
AND top.okres = '0'
AND top.kraj = '0'
AND top.kategorie = '1'

WHERE

pro.idobjektu = obj.id
AND obj.kraj = '10'
AND obj.okres = '24'
AND obj.expirace > NOW()

AND kap.min <= '500' AND kap.max >= '500'

GROUP BY obj.id
ORDER BY top1.pozice DESC, obj.nazev ASC
LIMIT 0 , 20

---

it as before.. problem must be in #1054 - Unknown column 'obj.id' in 'on clause' .. so i mean at row "LEFT JOIN zvyhodneni AS top1 ON obj.id = top1.idobjektu" in "obj.id".. but i don´t know where..

its query for me .. insert at SELECT rule new "column" .. where obj.id (objekty table) = top1.idobjektu (zvyhoendni table) and obj.kraj = ... etc.

its normal query .. or not ?

---
2) now (if its functioned) .. is ordering by top1.pozice DESC .. becouse if i havent any rows (by filtering) in "zvyhodneni" table .. in virtual column its wrote NULL .. and if its exist this can be only 1, 2 or 3 .. so by this ordering is first ordeing by pozice DESC =>

3 - 2 - 1 - NULL .. is possible if in table isnt any row after filtering.. it make for ex. 99 ... AND ordering will be true .. as top1.pozice ASC .. 1 - 2 - 3 - 99

i mean something as (SELECT CASE zvyhodneni WHEN zvyhodneni THEN zvyhodneni ELSE 99 END) AS pozice ... is it possible ?
---
3) its speed ok ? avarage 0,6 seconds .. where you prefer indexes ?

Many thanks !

[LifeinAsia]

I don't know if it will make any difference, but instead of

objekty AS obj, prostory AS pro

try

objekty AS obj INNER JOIN prostory AS pro ON pro.idobjektu = obj.id