intersection instead of union

[franciska]

Hi,

please I would to know how can I obtain the AND of these two selects, I would want to obtain the INTERSECTION instead of UNION.

(
SELECT tab1.ID_tab1
FROM tab1
WHERE (
(
(
tab1.fld01 LIKE '%ke%'
OR tab1.fld02 LIKE '%ke%'
OR tab1.fld03 LIKE '%ke%'
OR tab1.fld04 LIKE '%ke%'
OR tab1.fld05 LIKE '%ke%'
OR tab1.fld06 LIKE '%ke%'
OR tab1.fld07 LIKE '%ke%'
OR tab1.fld08 LIKE '%ke%'
OR tab1.fld09 LIKE '%ke%'
OR tab1.fld10 LIKE '%ke%'
OR tab1.fld11 LIKE '%ke%'
OR tab1.fld12 LIKE '%ke%'
OR tab1.fld13 LIKE '%ke%'
OR tab1.fld14 LIKE '%ke%'
)
OR (

SELECT tab1.ID_tab1
FROM tab2, tab1
WHERE tab2.fld01 LIKE '%ke%'
AND tab1.ID_tab2 = tab2.ID_tab2
)
OR (

SELECT tab1.ID_tab1
FROM tab3, tab1
WHERE (
(
tab3.fld01 LIKE '%ke%'
OR tab3.fld02 LIKE '%ke%'
OR tab3.fld03 LIKE '%ke%'
OR tab3.fld04 LIKE '%ke%'
OR tab3.fld05 LIKE '%ke%'
OR tab3.fld06 LIKE '%ke%'
OR tab3.fld07 LIKE '%ke%'
OR tab3.fld08 LIKE '%ke%'
OR tab3.fld09 LIKE '%ke%'
OR tab3.fld10 LIKE '%ke%'
)
AND tab1.ID_tab3 = tab3.ID_tab3
)
)
)
)
)
UNION (

SELECT tab1.ID_tab1
FROM tab4, tab6, tab5, tab3, tab1
WHERE 1 =1
AND tab4.tab4 LIKE '%This word%'
AND tab4.ID_tab4 = sub_tab4.ID_tab4
AND tab6.ID_tab6 = tab5.ID_tab6
AND tab5.ID_tab5 = tab3.ID_tab5
AND tab3.ID_tab3 = tab1.ID_tab3
)

The real select I consider is similar like that. Is it possible to do the intersection? I am trying all combination without success. In this case obviously I obtain results from the first select in addition to the results of the second select.... but I don't want this

many thanks to all indeed!!

[r937]

that's not a valid query

you cannot say

WHERE ... OR ( SELECT ... )

and there is no "sub_tab4" in the last SELECT


maybe if you could give us either a much more simple example, or else the actual real example...?

[franciska]

Yeah, I have resolved finally!

See this:

SELECT tab1.ID_tab1
FROM tab2, tab3, tab4, tab5, tab1
WHERE (
(
 
   (tab1.fld01 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld02 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld03 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld04 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld05 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld06 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld07 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld08 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld09 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld10 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld11 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld12 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld13 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld14 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
OR (tab1.fld15 LIKE '%ke%' AND tab2.fld01 LIKE '%This word%')
)
AND tab2.ID_tab2 = tab3.ID_tab2
AND tab3.ID_tab3 = tab4.ID_tab3
AND tab4.ID_tab4 = tab5.ID_tab4
AND tab5.ID_tab5 = tab1.ID_tab5
)

see attachment for the table relationship. But instead of inserting always LIKE '%This word%', is there another method to do this intersection since the table involved is always tab2?

many thanks!!

[r937]

i like your windows paint artistry

SELECT tab1.ID_tab1
  FROM tab2
INNER
  JOIN tab3
    ON tab3.ID_tab2 = tab2.ID_tab2 
INNER
  JOIN tab4
    ON tab4.ID_tab3 = tab3.ID_tab3 
INNER
  JOIN tab5
    ON tab5.ID_tab4 = tab4.ID_tab4  
INNER
  JOIN tab1
    ON tab1.ID_tab5 = tab5.ID_tab5 
   AND ( tab1.fld01 LIKE '%ke%'
      OR tab1.fld02 LIKE '%ke%'
      OR tab1.fld03 LIKE '%ke%'
      OR tab1.fld04 LIKE '%ke%'
      OR tab1.fld05 LIKE '%ke%'
      OR tab1.fld06 LIKE '%ke%'
      OR tab1.fld07 LIKE '%ke%'
      OR tab1.fld08 LIKE '%ke%'
      OR tab1.fld09 LIKE '%ke%'
      OR tab1.fld10 LIKE '%ke%'
      OR tab1.fld11 LIKE '%ke%'
      OR tab1.fld12 LIKE '%ke%'
      OR tab1.fld13 LIKE '%ke%'
      OR tab1.fld14 LIKE '%ke%'
      OR tab1.fld15 LIKE '%ke%'
       )
 WHERE tab2.fld01 LIKE '%This word%'

[franciska]

Oh great. You're great.
Thanks. It goes!!

Now, I know that this is a absurd request but: can you explain something more about the inner join you have inserted? I say this because I fought till now for obtain this type of smart code. I have lost many hours of sleep
I want to improve since sometimes I do queries long about two pages or more... please can you help me??? please??

[franciska]

Great. I'm quivering for joy!!

[r937]

can you explain something more about the inner join you have inserted?

of course

what would you like to know about it?

my inner joins are the same as your inner joins, except using different syntax

the "old style" joins, using comma-delimited tables in the FROM clause, are actually not as good as the newer JOIN syntax, when you consider clarity, simplicity, and ease of maintenance

the syntax is different, but the semantics are the same

and most importantly, the results are the same

nevertheless, i think JOIN syntax is better

[franciska]

I have never used this type of syntax for inner joins. So you can explain me in the way you want. And would be better if you can make a comparsion with the query I have written. I hope I have been clear.

[r937]

actually, it would be better if you make the comparison between the queries

go ahead, it's easy

compare the join conditions in your WHERE clause to the join conditions in my ON clauses

you will see the similarity immediately, eh

[franciska]

Yeah, I think I have understood...
But in case of problems I will make you know it.
However, can you make the select you have written more shorter than that? since the fields are of the same table and the like clause is the same for each field.

[r937]

there is no simple way to make it "shorter"

you can try this --

INNER
  JOIN tab1
    ON tab1.ID_tab5 = tab5.ID_tab5 
   AND CONCAT_WS( '' 
        , tab1.fld01 
        , tab1.fld02 
        , tab1.fld03 
        , tab1.fld04 
        , tab1.fld05 
        , tab1.fld06 
        , tab1.fld07 
        , tab1.fld08 
        , tab1.fld09 
        , tab1.fld10 
        , tab1.fld11 
        , tab1.fld12 
        , tab1.fld13 
        , tab1.fld14 
        , tab1.fld15 ) LIKE '%ke%'

[franciska]

Yes, it's really what I'm looking for! It goes!!
Till now I have never discovered the effective practical utility of some functions!

for the "tab1" is there anything you can do to avoid the repetition?

Thanks to you. With this thread I have learned many thinks about this subject!
Many thanks really!

[r937]

for the "tab1" is there anything you can do to avoid the repetition?

yes, you could omit it, but i advise always qualifying columns with their tables whenever a query has more than one table in it

[franciska]

Yes I know, but I'm talking about, if possible, the use of some functions or something else, to avoiding the repetition of the word "tab1" not the omitting of that one.

[franciska]

maybe the use of separator ",tab1."?

[franciska]

I've tried to do this, but, though the semantic is right, no SQL errors, I don't obtain any result.:

SELECT tab1.ID_tab1
  FROM tab2
INNER
  JOIN tab3
    ON tab3.ID_tab2 = tab2.ID_tab2
INNER
  JOIN tab4
    ON tab4.ID_tab3 = tab3.ID_tab3
INNER
  JOIN tab5
    ON tab5.ID_tab4 = tab4.ID_tab4
INNER
  JOIN tab1
    ON tab1.ID_tab5 = tab5.ID_tab5
   AND CONCAT_WS( ', tab1.'
        , 'tab1.fld01'
        , 'fld02'
        , 'fld03'
        , 'fld04'
        , 'fld05'
        , 'fld06'
        , 'fld07'
        , 'fld08'
        , 'fld09'
        , 'fld10'
        , 'fld11'
        , 'fld12'
        , 'fld13'
        , 'fld14'
        , 'fld15' ) LIKE '%ke%'
 WHERE tab2.fld01 LIKE '%This word%'

[r937]

no, that doesn't work, as you found out

the "tab1." prefixes are part of the column identifiers, not strings

[franciska]

Ok. So we have reached the end.
Then no more additions to the query are necessary!

I have learnt many things about inner joins here.
I think I will apply this form of query where possible in my sql declarations!!
Many thanks again!!

[franciska]

Hello,

why if I use the first way you have submitted without CONCAT_WS and great repetition of LIKE I obtain a result while using the second way I obtain a different one?

With the first I obtain the right result..
with the second way I obtain a sub-result of the first (here two records are not in the result that are present in the first way).

Maybe the OR operator in the second is not considered in right way?
I have tried both with inner joins and with 'classic' aged notation...

[franciska]

Maybe is it a bug? I have mysql 4.1.9! And I have just read this: http://bugs.mysql.com/bug.php?id=11594
I think that this case refers to mine too! ....I will not use this last smarty way to write code!

[longneck]

the queries are not exactly the same. and i don't think you're suffering from that bug.

for example, say you have a row where fld01 and k and fld02 is e.

the query in post #4 will not find this row because it compares each field independently to ke. effectively, your where clause is WHERE 'k' LIKE '%ke%' OR 'e' LIKE '%ke', which is obviously false.

but the query in post #11 will find the row because fld01 is concatenated to fild02 before the comparison is made. this where clause looks like this: WHERE 'ke' LIKE '%ke', which is true!

[franciska]

The only thing that I can say is that with the query written without concat_ws I obtain all rows (and this is the right result), while with the other one (with concat_ws) I obtain two rows less.

And exactly the missing rows have the two letter 'ke' at the initial part of the phrase (i.e. 'kexxxxxxx') while the other ones that are present in both results have 'ke' in the middle (i.e. 'xxxxxxkexxxx').
I don't know if this detail can be useful...