Mysql - PHP request

Question

Hi, in one of my projects, we store the data on trips of our customers in a table like this

id | user | country | travel_date | ------------------------------------------------------------------ 1 | 12 | 4 | 2012-03-13 16:57:41 | 1 | 17 | 8 | 2012-03-13 16:57:41 | 1 | 12 | 5 | 2011-03-13 16:57:41 | 1 | 13 | 8 | 2011-03-13 16:57:41 | 1 | 11 | 3 | 2011-03-13 16:57:41 | 1 | 10 | 1 | 2013-03-13 16:57:41 | 1 | 12 | 1 | 2012-03-13 16:57:41 | 

And we have a country table like this

 id | name ------+---------- 1 | India 2 | China 8 | Hongkong 3 | Singapore 

Got a user table like this

 id | Name --------+---------- 12 | name 17 | name 2 11 | name 3 10 | name 4 

And we have to accept a report, for example

• Which customers traveled in China and India in 2012.

Request

  SELECT DISTINCT user_id FROM traveled_details WHERE (country=1 OR country=2) AND YEAR(travel_date)=2012 

We also got a customer table. All reports work fine.

But we have a new requirement that needs a report that clients have not traveled to China in 2012, or a List of clients traveling to the UN, ever, or clients who must travel in India.

Is my table structure sufficient to receive such a query? If yes, I am a little confused by this request ... Anyone please help?

Answer 1

Nested queries will do the job!

 SELECT user_id from travelled_details WHERE user_id NOT IN (SELECT DISTINCT user_id FROM travelled_details WHERE country NOT(2) AND YEAR(travel_date) NOT(2012) ) 

To actually print the customer names, you can use the join in the user table

Similarly, for the list of untrusted clients, use LEFT JOIN in the travel_data tables and the user in the user_id column ... More about connections here

Answer 2

Your question is not entirely clear, but from what I can collect, you will want:

 SELECT DISTINCT user_id FROM travelled_details WHERE country NOT IN('2') AND YEAR(travel_date)=2012 

which will return all non-China travelers in 2012.

Answer 3

Your table structure is beautiful, but I hope that each client does not have an ID of 1.

Following your example, to get customers who have not traveled to China, you would do something like:

 SELECT DISTINCT user_id FROM travelled_details WHERE country NOT(2) AND YEAR(travel_date) NOT(2012) 

Answer 4

 SELECT DISTINCT user_id FROM travelled_details WHERE country<>2 AND YEAR(travel_date)=2012 

Answer 5

To get the name of the country

  SELECT DISTINCT t.user_id ,t.country,c.name FROM traveled_details t, country c WHERE t.country !=2 AND AND YEAR(travel_date)=2012 

Answer 6

For the report of Customers not traveling in China in 2012, the best option is to SELECT all users who went to China in 2012 and pass this result to a subquery.

 SELECT * FROM clients_table WHERE id NOT IN (SELECT user as id FROM travel WHERE country=2 AND YEAR(travel_date)=2012) 

Thus, you will get users who did not even travel + Customers who did not travel in China in 2012.

To get a list of users who have never traveled, simply remove the condition from the query.

Answer 7

Users do not travel in China 2012

 SELECT DISTINCT user FROM travel_data WHERE id<>2 AND YEAR(travel_date)=2012 

OUTPUT

 | USER | -------- | 12 | | 17 | 

SQL Fiddle Demo Users do not travel in China in 2012

List of UN Travel Clients Ever

 SELECT DISTINCT USER FROM travel_data WHERE id=NULL 

SQL Fiddle Demo List of Unmanaged Clients Ever

Answer 8

try it

 "SELECT * FROM travelled_detail WHERE (country = 1 OR country = 2) AND YEAR(travel_date)=2012"