CODEWITHSUNDEEP
mysqlsql
Maneesh M S
Maneesh M S

Reputation: 367

how to select the exactly wanted rows from 2 tables

I have a problem in my sql.
I have 2 tables named treatments and treatmentusers. The treatments table has 2 fields named id and treatment. In treatmentusers I have 3 fields id, userid, and treatmentid. My need is to select all the fields from the treatment table along with the userid in treatmentusers. But there is a condition that
   Selecting the rows only for a specific userid. (eg:userid=1 only). If the userid treatmentid combination is not present in the treatmentusers table the cell value must be a null or 0.

Upvotes: 0

Views: 125

Answers (2)

mattmanser
mattmanser

Reputation: 5796

You just need to use a left join and have the userId requirement as part of the ON clause.

SELECT t.*, tu.userid
FROM treatments t
LEFT JOIN treatmentUsers tu 
    ON t.Id = tu.treatmentId 
    AND tu.userId = 1

Upvotes: 2

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7668

SELECT T.*, TU.userid FROM treatments T
  INNER JOIN treatmentusers TU ON T.userid = TU.userid
  WHERE TU.userid = '1';

If userid in treatmentusers table is number type then

.....WHERE TU.userid = 1;

Upvotes: 0

Related Questions