Mysql – Remove Multiple Joins to Same Table with WHERE Clause

join;MySQLunion

I am stucked at below situation. Need expert advice.
I have 3 tables, login_details,deleted_login_details and custom_fields as below.
login_details structure = deleted_login_details
custom-fields have : user_id,custom_name,custom_value

I want following result.
I want only those users who fulfilled all where conditions with multiple joins on custom_fields table. Example

select user_id from login_details union deleted-login_details  as login_table
join
custom_fields as f1 on f1.user_id = login_table.user_id 
join
custom_fields as f2 on f2.user_id = login_table.user_id 
where
f1.custom_name = '1' and f1.custom_value in (1,2,3)
and 
f2.custom_name = '2' and f2.custom_value in (4,5,6)

So only those records should come who have custom_name =1 and value as above and custom_name=2 and value in above.
But I want this using only 1 join to custom_fields table.

Like as

So only 1 ,2 should get and no 3 as 3 dont have custom_name =1 record

Any advice?
Thanks in advance.

Best Answer

Append to your query GROUP BY user_id. This has the effect that multiple user_ids become one row entry.

Additionally append HAVING COUNT(user_id)=2. As you've GROUP BY user_id the count of them represents that the number of field matches.

Related Solutions

Mysql – Multiple left joins or subquery

This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`, 
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) user_runs
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID 
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);

Mysql – How to optimize multiple self-JOINs

I don't think there is a need for denormalizing the table. Self-joining will work fine if you have indexes that will be used effectively. For the specific query, I'd add an index on (year, dataID, countryID, data) and not use any derived tables:

SELECT 
    c.countryName AS Country,   
    em01.data AS data01,
    em02.data AS data02,
    ...
    emXX.data AS dataXX
FROM  
    Countries AS c
      JOIN CountryData AS em01
        ON  em01.year = 2017
        AND em01.dataID = 523 
        AND em01.countryID = c.countryID 
      JOIN CountryData AS em02
        ON  em02.year = 2017
        AND em02.dataID = 524
        AND em02.countryID = c.countryID
      ... 
      JOIN CountryData AS emXX
        ON  emXX.year = 2017
        AND emXX.dataID = YYY
        AND emXX.countryID = c.countryID 
 ;

The only issue you'll have is that MySQL has a hard limit of maximum 61 joins in a query. So, you won't be able to have 90 columns with the above query.

Another observation is that you don't seem to need any aggregated data but just a tiny subset of the (small or big table, doesn't matter). With the suggested index, you could just write a query like this:

SELECT 
    cd.countryID,
    c.countryName AS Country,   
    cd.dataID,
    cd.data
FROM  
    Countries AS c
      JOIN CountryData AS cd
        ON  cd.countryID = c.countryID 
WHERE 
      cd.year = 2017
  AND cd.dataID IN (522, 523, ..., YYY) 
ORDER BY
    cd.countryID,
    cd.dataID ;

and have the pivot transformation done in your application.

Best Answer

Related Solutions

Mysql – Multiple left joins or subquery

Mysql – How to optimize multiple self-JOINs

Related Question