CODEWITHSUNDEEP
mysql
Lk Thanvi
Lk Thanvi

Reputation: 87

Retrieving data from 3 Mysql tables

Suppose I have 3 different tables relationships as following

1st is tbl_users(id,gender,name)
2nd is tbl_feeds(id,user_id,feed_value)
3rd is tbl_favs(id,user_id,feed_id)

where id is primary key for every table.

Now suppose I want to get data where those feeds should come which is uploaded by Gender=Male users with one field in every row that should say either the user who is calling this query marked that particular feed as favourite or not.

So final data of result should be like following : where lets say the person who is calling this query have user_id=2 then is_favourite column should contain 1 if that user marked favourite that particular feed otherwise is_favourite should contain 0.

user_id feed_id feed_value is_favourite gender 
1        2        xyz         1           M
2        3        abc         0           M
3        4        mno         0           M

I hope you getting my question , I m able to get feeds as per gender but problem is I m facing problem to get is_favourite flag as per particular user for every feed entry.

I hope some one have these problem before and I can get help from those for sure.

I would be so thankful if some one can resolve my this issue.

Thanks

Upvotes: 0

Views: 56

Answers (2)

Twister1002
Twister1002

Reputation: 557

In order to link your tables, you need to find the most common link between them all. This link is user_id. You'll want to create a relationship between all tables with JOIN in order to make sure each and every user has data.

Now I don't know if you're planning on making sure all tables have data with the user_id. But I would use INNER JOIN as it will ONLY show records of that user_id without nulls. If the other tables could POSSIBLY (Not always guaranteed) you should use a LEFT JOIN based on the tables that is it possible with.

Here is an SQLFiddle as an example. However, I recommend you name your ID fields as appropriate to your table's name so that way, there is no confusion!

To get your isFavorite I would use a subquery in order to validate and verify if the user has it selected as a favorite.

    SELECT
      u.userid, 
      u.gender,
      f.feedsid,
      f.feedvalue,
      (
        SELECT 
          COUNT(*) 
        FROM 
          tbl_favs a 
        WHERE 
          a.userid = u.userid AND 
          a.feedsid = f.feedsid
      ) as isFavorite
    FROM
      tbl_users u
    INNER JOIN
      tbl_feeds f
    ON
      u.userid = f.userid

~~~~EDIT 1~~~~

In response to your comment, I have updated the SQLFiddle and the query. I don't believe you really need a join now based on the information given. If you were to do a join you would get unexpected results since you would be trying to make a common link between two tables that you do not want. Instead you'll want to just combine the tables together and do a subquery to determine from the favs if it is a favorite of the user's.

SQLFiddle:

    SELECT
      u.userid,
      f.feedsid,
      u.name,
      u.gender,
      f.feedvalue,
      (
        SELECT 
          COUNT(*) 
        FROM 
          tbl_favs a 
        WHERE 
          a.userid = u.userid AND 
          a.feedsid = f.feedsid
      ) as isFavorite
    FROM
      tbl_users u, 
      tbl_feeds f
    ORDER BY 
      u.userid,
      f.feedsid

Upvotes: 1

Christof
Christof

Reputation: 3927

Something like this should work:

SELECT
  u.id AS user_id.
  fe.id AS feed_id,
  fe.feed_value,
  IFNULL(fa.is_favourite, 0),
  u.gender
FROM
  tbl_users u
JOIN
  tbl_feeds fe ON (fe.user_id = u.id)
LEFT JOIN
  tbl_favs fa ON (
    fa.user_id = u.id
  AND
    fa.feed_id = fe.id
  )

Upvotes: 1

Related Questions