php/mysql/joins

[litebearer]

Til now I have used single tables for whatever database needs I had.

 

Now however, I have three tables (see db_table.jpg).  Obviously, I can use my infamous 'sledge hammer' approach - gathering data from each table individully and 'merging' them into one array. It appears it is time for me to throw myself into the scary world of joins.

 

queries.jpg shows the end result (with which table the data is derived) and the psuedo code for the desired query.

 

HELP!!!

 

[attachment deleted by admin]

[ale8oneboy]

Not as bad as you think. You can pull this task off with using multiple table query with table aliases.

 

SELECT a.date, u.fullname, c.company, a.activity, a.followup_date 
FROM users u, customers c, activity_log a
WHERE
u.user_id = a.user_id
AND
c.cust_id = a.cust_id

 

Just fill in the rest of the where clause and you should be good to go. This may not be the most efficient way to do it. But it will get the job done. Use it as a stepping stone to using multiple table joins.

[Mchl]

SELECT a.date, u.fullname, c.company, a.activity, a.followup_date 
FROM users u, customers c, activity_log a
WHERE
u.user_id = a.user_id
AND
c.cust_id = a.cust_id

 

And once you realise that the code above is same thing as this:

 

SELECT a.date, u.fullname, c.company, a.activity, a.followup_date 
FROM 
  users u
INNER JOIN 
  customers c ON c.cust_id = a.cust_id
INNER JOIN
  activity_log a ON u.user_id = a.user_id

you should start feeling more confident.

[ale8oneboy]

Exactly!