0

I am having two database tables. In the first table I am keeping the details of the users and in the second table I pass the email as a foreign key from the first table and keeping the addresses as well in the second table. I want to match one user's country with all other users and retrieve the tuples which are matching but I don't want the details of the user which I want to match with all other users. Please help me in this regard I wasted my two days on it, your help is much appreciated. Bellow are my tables.

Fisrt Table

CREATE TABLE user(
  id_user int NOT NULL,
  email varchar(256) UNIQUE,
  password varchar(50) DEFAULT NULL,
  dateOfBirth varchar(20) DEFAULT NULL,
  Gender varchar(10) DEFAULT NULL,
  PRIMARY KEY (id_user)
)

1   ta.ahmadzai tariq   12/1/1996   Male
2   sa.ahmadzai Sadiq   1/3/1998    Male
3   ba.ahmadzai Bari    12/1/1999   Male
4   ma.ahmadzai Mohammand   12/1/2000   Male

Second Table

CREATE TABLE address(
  id_address int NOT NULL,
  email varchar(256) NOT NULL,
  address varchar(50) DEFAULT NULL,
  road varchar(20) DEFAULT NULL,
  City varchar(10) DEFAULT NULL,
  Country varchar(10) DEFAULT NULL,
  PRIMARY KEY (id_address),
  CONSTRAINT id_AddUser FOREIGN KEY (email) REFERENCES user (email) ON DELETE NO ACTION ON UPDATE NO ACTION
)
10  sa.ahmadzai Colombo 74  Colombo6    Sri Lanka
20  ba.ahmadzai Kandy   80  KandyTown   Sri Lanka
50  ma.ahmadzai Galle   20  Fort    Sri Lanka

I was able to write one query but it seems to be completely wrong against what I need.

SELECT country
FROM address
WHERE email like 'ta.ahmadzai' NOT IN 
    (SELECT DISTINCT email
     FROM "user"
     WHERE country like 'India'
    )

I want to use the query in a PHP script where I will pass the email and country as variables.

sheeno
  • 45
  • 5
  • Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) – Jens Aug 24 '17 at 10:28
  • 4
    Please provide some sample data and expected output. – JohnHC Aug 24 '17 at 10:29
  • I have no problem with the tables, I just want to help in writing the query for the problem I have mentioned above. – sheeno Aug 24 '17 at 10:36
  • Do you want to retrieve a list of all users which happen to live in the country of one specify user, or you would like to group all users by their countries? – sauerburger Aug 24 '17 at 11:32
  • Tariq's answer helped me, thank you – sheeno Aug 24 '17 at 12:02

1 Answers1

0

try this instead, below query would probably help you.

SELECT * FROM users u, address a WHERE a.email=u.email AND a.country LIKE (SELECT country FROM address WHERE email = ? );
Tariq
  • 3
  • 4