make_day maker country
12 Jane France
12 Jane Spain
15 Andy Germany
15 Mary Italy
17 Mary Spain
17 Tom Germany
18 Mary Germany
I have a table named “making” like the above and another table named “visiting” like the below.
visit_day visitor country
12 Jane Italy
12 Jane France
12 Jane Spain
14 Andy France
15 Andy Germany
15 Mary Italy
16 Tom Spain
17 Mary Spain
17 Tom Germany
18 Mary Germany
19 Tom Spain
With the tables above, I like to produce my target result like the below.
If the variable is “Jane”,
Someone(Mary) made something in Italy at 15 which is after Jane visit it at 12.
No one made anything in France after Jane visit it at 12.
No one made anything in Spain after Jane visit it at 12.
Jane never visit Germany. it means she is not interested in it.
So I like to retrieve Italy only when the variable is “Jane”. It means something new in Italy but nothing new in France and Spain.
If the variable is “Andy”,
Andy never visited in Italy.
No one made anything in France after Andy visit it at 14.
Andy never visited in Spain.
Someone(Mary) made something in Germany after Andy visit it at 15.
So I like to retrieve Germany only when the variable is “Andy”. It means something new in Germany.
If the variable is “Mary”,
No one made anything in Italy after Mary visit it at 15.
Mary never visited in France.
No one made anything in Spain after Mary visit it at 17.
No one made anything in Germany after Mary visit it at 18.
.So I like to retrieve “nothing” when the variable is “Mary”
.
If the variable is “Tom”,
Tom never visited in Italy.
Tom never visited in France.
Someone(Mary) made something in Germany after Tom visit it at 17 recently…
No one made anything in Spain after Tom visit it at 19 recently.
(please notice Tom visited Spain at 16 before.)
So I like to retrieve “Germany” when the variable is “Tom”.
In order to get my target result, I made the SQL below.
SELECT make_day, maker, making.country as country, visit_day, visitor
FROM making
LEFT JOIN visiting ON visiting.country=making.country
WHERE visitor='$visitor' and visit_day<make_day
but the code above produces the result below when the variable is “Tom”.
17(Mary)Spain// 16(Tom)
18(Mary)Germany// 17(Tom)
It should produces my target result below.because No one made anything in Spain after Tom visit it at 19 recently.
18(Mary)Germany// 17(Tom)
The following is one of my trial for it…
SELECT make_day, maker, making.country as country, visit_day, visitor
FROM making
LEFT JOIN visitingCountry ON visitingCountry.visitCountry=making.country
(SELECT country as visitCountry, max(visit_day) as maxVisit_day
FROM visiting
WHERE visitor='$visitor' GROUP BY country) as visitingCountry
However it produces the following WARNING
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource