SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow intersection instead of union

    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!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's not a valid query

    you cannot say
    Code:
    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...?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I have resolved finally!

    See this:
    Code MySQL:
    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!!
    Attached Images Attached Images

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i like your windows paint artistry
    Code:
    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%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    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??

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great. I'm quivering for joy!!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by franciska View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there is no simple way to make it "shorter"

    you can try this --
    Code:
    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%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by franciska View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  15. #15
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe the use of separator ",tab1."?

  16. #16
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried to do this, but, though the semantic is right, no SQL errors, I don't obtain any result.:

    Code MySQL:
    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%'

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, that doesn't work, as you found out

    the "tab1." prefixes are part of the column identifiers, not strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!!

  19. #19
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  20. #20
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  21. #21
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  22. #22
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •