0 Replies - 15831 Views - Last Post: 26 May 2010 - 09:52 AM

#1 Jstall   User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Help with a complex query, using mysql

Posted 26 May 2010 - 09:52 AM

Hey all,

I am a student doing a work term and creating a site with PHP/Mysql.

I have been working this for a couple of hours now and could really use a hand. I have attached a simple ERD that shows the structure of the tables that I am working with. It was made with some free modeling software I found as I don't have anything to model a db here.

The structure is as follows :

members - Self explanatory, members are identified by their member_id

programs - Various programs and areas of interest, members can express interest in programs

member_programs - Associative table between members and programs with member_id and program_id FK's

shouts - can be sent from user to user, the member_id refers to the senders member_id, the friend_id refers to the recipients member_id.

events - Events can appeal to many different interests and programs

events_programs - Associative table between events and programs with event_id and program_id fk's

feed - Whenever a shout is sent, or an event is created it is added to the feed table. The feed table has a field called activity_type_id which corresponds to the type of activity it is recording(4 is shout,17 is new event). There is a trans_id field that corresponds to the id of the event or shout it is referring to (if the shouts id was 42 then the trans_id would be 42).

My goal is to have one query that will return all shouts sent to the user and all events created that are in the users realm of interest. First I created the query to get the shouts :

SELECT sh.shout_id,sh.shout_body, m.first_name,m.last_name,f.feed_id
FROM `shouts` AS sh
INNER JOIN members AS m ON sh.member_id = m.member_id
INNER JOIN feed AS f ON sh.shout_id = f.trans_id
WHERE sh.friend_id = 13



This works fine, I am using the id 13 in the where clause as it is my test user.

Next I created a query that would return all events in the users realm of interest :
SELECT p.program,e.event_name,ep.event_id,ep.program_id,f.feed_id
FROM events_programs AS ep
INNER JOIN programs AS p ON p.program_id = ep.program_id
INNER JOIN events AS e ON e.event_id = ep.event_id
INNER JOIN feed AS f ON ep.event_id = f.trans_id
WHERE ep.program_id IN (SELECT program_id FROM member_programs WHERE member_id = '13')
GROUP BY ep.event_id



This also seems to work fine for me. The problem is when I try to combine the two as sub queries in another query:

SELECT f.stamp,f.activity_type_id,f.feed_id, sq1.*, sq2.*
FROM feed AS f
INNER JOIN
   (SELECT sh.shout_id,sh.shout_body, m.first_name,m.last_name,f.feed_id AS shout_feed_id
   FROM `shouts` AS sh
   INNER JOIN members AS m ON sh.member_id = m.member_id
   INNER JOIN feed AS f ON sh.shout_id = f.trans_id
   WHERE sh.friend_id = 13) AS sq1 ON f.feed_id = sq1.shout_feed_id
INNER JOIN
   (SELECT p.program,e.event_name,ep.event_id,ep.program_id,f.feed_id AS event_feed_id
   FROM events_programs AS ep
   INNER JOIN programs AS p ON p.program_id = ep.program_id
   INNER JOIN events AS e ON e.event_id = ep.event_id
   INNER JOIN feed AS f ON ep.event_id = f.trans_id
   WHERE ep.program_id IN (SELECT program_id FROM member_programs WHERE member_id = '13')
   GROUP BY ep.event_id) AS sq2 ON sq2.event_feed_id = f.feed_id
WHERE f.activity_type_id = '4' OR f.activity_type_id = '17




My thinking is since both the sub queries return a feed_id I could join using that. I have tried using both INNER joins and LEFT OUTER joins. My thinking was,since in some cases fields would be null, later with PHP I could choose what to display based on what the activity_type_id from the feed table was. However I am not getting the results I need. I know this can be tricky to follow and the code may look a bit sloppy, it gets tricky when you copy and paste from phpmyadmin but I cleaned it up as best I could. Again an ERD showing the relations is attached. I could very well be going at this the wrong way, if anyone could offer some advice it would be very much appreciated. Thanks much.

Attached image(s)

  • Attached Image

This post has been edited by Jstall: 26 May 2010 - 10:28 AM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1