Where is something new after I visited?

make_day maker country 12 Jane France 12 Jane Spain 15 Andy Germany 15 Mary Italy 17 Mary Spain 17 Tom Germany 18 Mary GermanyI 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_daybut 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

SELECT making.make_day , making.maker , making.country , latest.visit_day FROM making INNER JOIN ( SELECT country , MAX(visit_day) AS visit_day FROM visiting WHERE visitor = '$visitor' ) AS latest ON latest.country = making.country AND latest.visit_day < making.make_day

by the way, “supplied argument is not a valid MySQL result resource” simply tells you your SQL is broken

in order to find out why it’s broken, you need to run the query outside of php, to see the real mysql error message

2 Likes

[quote=“r937, post:2, topic:276054”]
SELECT making.make_day
, making.maker
, making.country
, latest.visit_day
FROM making
INNER
JOIN ( SELECT country
, MAX(visit_day) AS visit_day
FROM visiting
WHERE visitor = ‘$visitor’ ) AS latest
ON latest.country = making.country
AND latest.visit_day < making.make_day
[/quote] your code looks nice.
If the variable is Jane, it retrieves Italy as I expected.
If the variable is Mary, it retrieves nothing as I expected.
So far, so good.

When the variable is Andy, I expected “Germany”, but I am afraid it retrieves nothing.

When the variable is Tom, I expected “Germany”, but I am afraid it retrieves nothing.

That’s because the make_day and visit_day are the same. You just need to adjust the join criteria to

AND latest.visit_day <= making.make_day

[quote=“DaveMaxwell, post:4, topic:276054”]
AND latest.visit_day <= making.make_day
[/quote]I am afraid although I adjust the join criteria to the above, it produces nothing when the variable is “Andy” or “Tom”.

Hmm… try adding GROUP BY country to the sub-query. It’s optional in MySQL, but results may vary.

SELECT making.make_day
     , making.maker
     , making.country
     , latest.visit_day
  FROM making
INNER
  JOIN ( SELECT country
              , MAX(visit_day) AS visit_day
           FROM visiting 
          WHERE visitor = '$visitor'
          GROUP BY country) AS latest
    ON latest.country   = making.country
   AND latest.visit_day < making.make_day

If that doesn’t work, then your data does not match what you posted in post #1 because it’s returning the correct values for me.

makes all the semantic difference in the world

omitting it means only one row is returned, and yes, the value of the un-aggregated column country is indeterminate… any aggregate function, such as in this case MAX, operates on the whole table as one group… the problem manifests itself as maybe that one indeterminate country joins, maybe it doesn’t

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.