Kyle.Belanger Kyle.Belanger -4 years ago 164
MySQL Question

Combining 3 select queries

I am working with a MySQL database. I am suppose to combine three select queries, to "improve performance". Each select below is dependent on the previous ID retrieved.

Here is the workflow to get data references between the tables.

There are three tables:

pUserMatch | pGroupMatch | ProfileData
----------- | ----------- | ----------
* username
* GroupID .... GroupID
ProfileID .... ID

My three select queries...

# get the group ID for the specific username
group_id = select GroupID from Profiles.pUserMatch where username = "<username>";

# now, get the group match ID for the group ID
profile_id = select ProfileID from Profiles.pGroupMatch where GroupID = "<group_id>"

# now, get the profile data for this ID
profile = select * from Profiles.ProfileData where ID = "<profile_id>"

I know a little bit about using
, but I thought that they were only used when multiple tables have matching data.

So far, I've tried the following...

# multiple select from tables
select usermatch.username, groupmatch.GroupID
from pUserMatch as usermatch, pGroupMatch as groupmatch
where usermatch.username = <"username">

# inner join...
select pUserMatch.GroupID, pGroupMatch.GroupID,
from pUserMatch
inner join pGroupMatch
on pUserMatch.GroupID = pGroupMatch.GroupID

I need to select the pGroupMatch.GroupID and ProfileData.ID based on a username param. Is there a way to query this data in a single statement?

Answer Source

I don't know if I understand your need, lets try:

Try to use this query:

select pGroupMatch.GroupID, ProfileData.ID
from pUserMatch
inner join pGroupMatch on pGroupMatch.GroupID = pUserMatch.GroupID 
inner join ProfileData on = pGroupMatch.ProfileID 
where pUserMatch.username = "<username>";

Check if you can create indexes for improve your query, if you can try it:

CREATE INDEX idx_pUserMatch_01 ON pUserMatch (GroupID);
CREATE INDEX idx_pGroupMatch_01 ON pGroupMatch (ProfileID);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download