MYSQL Inner Join on possible empty records

[joeydeluca]

Hi everyone,

 

I am using the following query to retrieve member profile data:

 

 

 

SELECT * FROM Members 
   INNER JOIN MemberMartialArts on MemberMartialArts.mma_memberId = '$memberId'
                                INNER JOIN MemberAbilities on MemberAbilities.ma_memberId = '$memberId'
                                 WHERE m_id = '$memberId'

   

My problem is the tables, MemberMartialArts and MemberAbilities may possibly have no records where memberId = '$memberId'.

 

I do not want to use three separate queries because as tested the speed was slower.

 

Can anyone comment how I can modify this query to to make it work even of one of the table do not contain a memberId..

 

Thanks,

Joe

[menwn]

Hello there,

 

The whole point of inner joins is to return rows that have a match in both tables. So in case of not a table having a matching record you will not get anything back for that id.

 

You say that you don't want to use three queries. I suppose you could nest a select count for the MemberAbilities table and check whether you get any result with an if.

 

However it is not clear what exactly is the problem. You might not have or you might have records. How would you know this beforehand?

And how much slower is your system using these two inner joins? Or is there any restrictions? For example if you find people in the first table are you required to search in the second?

All these decisions can lead you towards a better treatment (provided that this is not it, already)

 

Cheers,

menwn

[jdavidbakr]

Are you wanting to use LEFT JOIN instead?  That will find the match in the Members table and then join the other two tables, leaving their columns NULL if there is no match.  You should also join against the foreign keys instead of putting the constant in each join.  Assuming m_id is the Member table's primary key:

 

SELECT * FROM Members
   LEFT JOIN MemberMartialArts on MemberMartialArts.mma_memberId = Members.m_id
   LEFT JOIN MemberAbilities on MemberAbilities.ma_memberId = Members.m_id
   WHERE Members.m_id = '$memberId'