CODEWITHSUNDEEP
mysql
Alphy
Alphy

Reputation: 331

Join more than four tables in sql

I have the following tables with the following columns: More in http://sqlfiddle.com/#!2/8776a0/2

request : {id,request_id, client_id, product_name, designation_date_1, can }
assigned_samples:{id,labref, user_id, stat}
users{id, fname, lname}
clients:{id, name}
tracking_table{labref, activity, added_date}

What I would like to do is join all these tables and come up with one big table containing all the data when joining with tracking table, the condition is to check if activity column has given value like "a", if a matching row is not found, the date_added should be null else show the date_added

result table {labref, prod, desc_date, can, CONCAT(fname,' ',lname), date_added}

SELECT r.request_id, r.product_name, r.can, r.designation_date_1, CONCAT(u.fname,' ', u.lname) as analyst, a_s.stat
FROM request r, assigned_samples a_s, user u, clients c
WHERE r.client_id=c.id
AND a_s.analyst_id = u.id
AND r.request_id = a_s.labref
AND r.client_id='2'
GROUP BY r.request_id

If i add a condition to the query, it returns the data, but the date_added is all the same which is wrong

SELECT r.request_id, r.product_name, r.can, r.designation_date_1, CONCAT(u.fname,' ', u.lname) as analyst, a_s.stat
FROM request r, assigned_samples a_s, user u, clients c, tracking_table t
WHERE r.client_id=c.id
AND a_s.analyst_id = u.id
AND r.request_id = a_s.labref
AND t.activity ='Authorizing COA Release'
AND r.client_id='2'
GROUP BY r.request_id

Upvotes: 1

Views: 39

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

join condition should be added for tracking_table.

Try this:

SQLFiddle: http://sqlfiddle.com/#!2/3a116f/1

(I slightly changed the input data for demo)

SELECT r.request_id, r.product_name, r.can, r.designation_date_1, CONCAT(u.fname,' ', u.lname) as analyst, a_s.stat,
  date_added
FROM request r, assigned_samples a_s, user u, clients c, tracking_table t
WHERE r.client_id=c.id
AND a_s.analyst_id = u.id
AND r.request_id = a_s.labref
AND r.client_id='2'
AND r.request_id = t.labref
AND t.activity ='Authorizing COA Release'

Upvotes: 1

Related Questions