[V4MD] / [SQL]: Promblem with SQL-Query over 3 Tables

[Ruslan Zasukhin]

on 10/20/03 8:56, Arno Oesterheld at oesterheld at axonmedia.de wrote:

Hi Arno,

> Hi,
> 
> I am using Valentina with Director (WinXP (german), D8.5.1, Valentina
> 1.9.8):
> I have 3 tables in my database on which I do this query:
> 
> SELECT * FROM artikel, z_art_pdf, pdf WHERE
> (z_art_pdf.id_art=artikel.id_art AND z_art_pdf.id_pdf=pdf.id_pdf AND
> pdf.volltext LIKE '900' no_case)  OR
> (z_art_pdf.id_art=artikel.id_art AND z_art_pdf.id_pdf=pdf.id_pdf AND
> artikel.artnr LIKE '900' no_case)  OR
> (z_art_pdf.id_art=artikel.id_art AND z_art_pdf.id_pdf=pdf.id_pdf AND
> artikel.materialbezeichnung LIKE '900' no_case)
> 
> I get 0 hits as result.
> But there should be more then 0 hits because I get some hits when I
> delete this part:
> ...(z_art_pdf.id_art=artikel.id_art AND z_art_pdf.id_pdf=pdf.id_pdf AND
> pdf.volltext LIKE '900' no_case)  OR...
> 
> A query on this part alone gives me 0 hits and that is ok. But I use
> "OR" between the conditions so I think I should get the "sum" of hits
> for the 3 subqueries, right? What is my mistake?
> 
> For the case that this is important I give you the fieldtypes below:
> z_art_pdf.id_art = LONG
> artikel.id_art = LONG
> pdf.id_pdf = LONG
> pdf.volltext = TEXT-German
> artikel.artnr = VARCHAR[20]-German
> artikel.materialbezeichnung = VARCHAR[255]-German
> 
> 
> Best Regards,
> 
> Arno
> 
> 
> P.S.: I also tried a shorter form of the above Query - but just get the
> same result:
> 
> SELECT * FROM artikel,z_art_pdf,pdf WHERE
> (z_art_pdf.id_art=artikel.id_art AND z_art_pdf.id_pdf=pdf.id_pdf) AND
> (((pdf.volltext LIKE '900' no_case)  OR (artikel.artnr LIKE '900'
> no_case)  OR (artikel.materialbezeichnung LIKE '900' no_case) ))

Valentina 1.x cannot do query that have OR for fields between different
tables. In Valentina 2.0 this will work.

You can try redesign your tables.

Otherwise I do not see easy workaround.


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------