CODEWITHSUNDEEP
phpmysqlinner-join
Malixxl
Malixxl

Reputation: 525

What should I use: inner join or 3 different queries?

I have 3 tables

  1. member
  2. member videos
  3. member photos

For member profile page, should I use one query with inner join like

SELECT member.*,member_photo.*,member_video.* 
FROM (member 
    INNER JOIN member_photo ON member.member_id = member_photo.member_id
) 
INNER JOIN member_video ON member.member_id = member_video.member_id

then push videos to a video array and photos to a photo array, but in here query finds same photos for every video so I have to check if this item added to array before.

Or should I use 3 queries: first find member information, then one more query for member pictures, and one more query for member videos? Which way should I use?

Upvotes: 3

Views: 218

Answers (5)

Jonathan Leffler
Jonathan Leffler

Reputation: 755054

I think you need to use three queries.

Consider one person who has 6 photos and 3 videos. Your query will generate 18 rows of data. This is not helpful, especially if the actual videos are (a) large and (b) selected in the query.

Consider another person who has 100 photos but doesn't post videos. That person won't be represented at all with a query using inner joins.

Consider the person who signed up yesterday and has not yet uploaded anything? That person won't be represented either.

It is good to combine queries when you can. It is bad to forcibly combine unrelated queries like these ones. Yes, it looks like they're related, but the list of photos by a member is actually unrelated to the list of videos by the same member. If you could get a query operation to generate:

member1     photo1-of-member1   video1-of-member1
member1     photo2-of-member1   video2-of-member1
member1     photo3-of-member1   ...empty...

then you'd be happy. There isn't a standard query operator that does that, AFAIK.

You could make do with two inner-joined queries, one to collect member information plus the photos, one to collect member information plus the videos. However, this potentially collects multiple copies of the member information (and there's no obvious benefit from the repetition), and also means that you won't see any data for members without photos or videos (or without either).

Clearly, you could use outer joins instead of inner joins with the two queries so that you always see the member data, but...well, I don't see any obvious advantage over three separate queries. I suppose that if the communications delay is truly astronomical (for example, your application-database connection is running over a satellite link, or the application is in Argentina and the database server is in India), then maybe the overhead is sufficient to make two queries preferable to three. But it has to be something unusual like that, I think, to warrant using two queries instead of three.

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

In my opinion here photos and videos are two different things especially they go to two different structures on application side for further processing so I would select them separately, i.e.:

SELECT member.*, member_photo.*
FROM member 
INNER JOIN member_photo ON member.member_id = member_photo.member_id

SELECT member.*, member_video.* 
FROM member 
INNER JOIN member_video ON member.member_id = member_video.member_id

Upvotes: 3

Tejas Patil
Tejas Patil

Reputation: 6169

This will depend on what your end application is going to do. If say when a member does log-in and you need the info of that member at that moment, then it would be better to have 3 different queries. If say you have perform some operation involving all members at one go (eg. report for all members), then go for inner join.

Upvotes: 0

Devin Ceartas
Devin Ceartas

Reputation: 4829

I think you could probably get what you are looking for by introducing a "group by" clause, so that the returned answers are grouped by video.

Upvotes: 0

ron tornambe
ron tornambe

Reputation: 10780

Try:

SELECT DISTINCT member.*,member_photo.*,member_video.* FROM (member INNER JOIN member_photo ON member.member_id = member_photo.member_id) INNER JOIN member_video ON member.member_id = member_video.member_id

Upvotes: 0

Related Questions