-3

Possible Duplicate:
Combining several database table together?

I have following query and want done where with each value that is in $find for name_re_all, how is it in my query?

For example: name_re_all is as:

ROW 3: 11111 22222 33333 44444
ROW 2: 55555 66666 77777
ROW 1: 88888 99999 112233 445566

If value $find was 11111 show all values row 3: 11111 22222 33333 44444
Or
If value $find was 66666 show all values row 2: 55555 66666 77777
Or
If value $find was 33333 show all values row 3: 11111 22222 33333 44444
Or
If value $find was 778899 show all values row 1: 88888 99999 112233 445566
Or
and ...

$query = $this -> db -> query('
            SELECT
               @rownum := @rownum + 1 rownum,
               tour_foreign.id, 
               tour_foreign.name,
               tour_foreign.airline,
               MIN(tour_foreign_residence.name_re) AS name_re, 
               tour_foreign.service, 
               tour_foreign.date_go, 
               tour_foreign.date_back, 
               tour_foreign.term,
               tour_foreign.useradmin_submit,
               tour_foreign.date_submit,
               GROUP_CONCAT( tour_foreign_residence.name_re 
                     ORDER BY tour_foreign_residence.name_re 
                     SEPARATOR "، "
                   ) AS name_re_all
            FROM   tour_foreign 
              INNER JOIN tour_foreign_residence 
            ON ( tour_foreign.id = tour_foreign_residence.relation )
              JOIN (SELECT @rownum := 0) r
            WHERE  tour_foreign.name LIKE "%' . $find . '%" 
            GROUP BY  tour_foreign.id 
            HAVING name_re_all LIKE "%' . $find . '%"'
            );

My tables in database:

enter image description here enter image description here

Community
  • 1
  • 1
Jennifer Anthony
  • 2,227
  • 10
  • 37
  • 56
  • Please post the table structure and some (representative) example data. – GolezTrol Oct 20 '11 at 07:25
  • I put tables in post, Please see they. – Jennifer Anthony Oct 20 '11 at 07:30
  • 3
    yuck, this [mysterious query](http://stackoverflow.com/questions/7817818/combining-two-table-with-sql-join) again! Who are all these people after the same query?! – Your Common Sense Oct 20 '11 at 07:35
  • @Col. Shrapnel - this is just a example – Jennifer Anthony Oct 20 '11 at 07:37
  • @WallStreet And where is this 11111, 22222, 33333 in this example data? I'm happily willing to answer questions, but if I wanted to solve riddles I'd go somewhere else. – GolezTrol Oct 20 '11 at 07:40
  • NOpe. this is new aaccount instead of one temporarily suspended because of low-quality contributions. – Your Common Sense Oct 20 '11 at 07:40
  • 3
    @Wall Street this very same query has been posted from at least 3 different accounts (Alicia Cibrian, Nicole Spears, Jennifer Anthony, and this), with the same level of broken english. Please, just try all the answers you were given, I can't believe no one worked; in that case, something is wrong with the question then, and reposting it all over again without significant changes doesn't help you. – Damien Pirsy Oct 20 '11 at 07:40
  • @Col.Shrapnel Maybe everyone in Istanbul (?) got the same homework assignment? – GolezTrol Oct 20 '11 at 07:42
  • Then they have to DO their homework, using KNOWLEDGE got in classes. Homework is intended to be done by pupil, not some other people. – Your Common Sense Oct 20 '11 at 07:43
  • Please help me for resolved this issue instead of discussing about other issues... !!! – Jennifer Anthony Oct 20 '11 at 07:48
  • @Damien Pirsy - these users not for me, ... !!!!!!!!!!!!!!!!!!!!!!! – Jennifer Anthony Oct 20 '11 at 08:09
  • This query will never work, because you're using `'` single quotes to quote both the string as a whole and the params inside. Put `"` double quotes on the outside and single `'` quotes for the params. – Johan Oct 20 '11 at 09:18
  • @Johan - Thanks, It is right. It work true if i remove `WHERE tour_foreign.name LIKE "%' . $find . '%"` from sql, How can use `WHERE... OR HAVING name_re_all...` – Jennifer Anthony Oct 20 '11 at 09:41
  • Use as this `HAVING name_re_all LIKE '%" . $find . "%' OR name LIKE '%" . $find . "%'"` is true(without use `where`)? – Jennifer Anthony Oct 20 '11 at 09:53
  • @Col.Shrapnel Professional jobs are intended to be done by professionals. Then why are we sharing information here for people who actually get paid to do it themselves? I don't see why students shouldn't be helped while professionals should. – GolezTrol Oct 20 '11 at 10:12

1 Answers1

0

As a variant: wrap this query into subquery -

SELECT * FROM (
  your query...
) t
WHERE FIND_IN_SET('11111', name_re_all);

EDIT: You should use the subquery!

SELECT * FROM (
  SELECT
     @rownum := @rownum + 1 rownum,
     ...
     ...
     ...
     GROUP_CONCAT( tour_foreign_residence.name_re 
           ORDER BY tour_foreign_residence.name_re 
           SEPARATOR "، "
         ) AS name_re_all
  FROM   tour_foreign 
    INNER JOIN tour_foreign_residence 
  ON ( tour_foreign.id = tour_foreign_residence.relation )
    JOIN (SELECT @rownum := 0) r
  WHERE  tour_foreign.name LIKE "%' . $find . '%" 
  GROUP BY  tour_foreign.id 
--  HAVING name_re_all LIKE "%' . $find . '%"
) t
WHERE FIND_IN_SET('11111', name_re_all)
Devart
  • 119,203
  • 23
  • 166
  • 186