CODEWITHSUNDEEP
mysqldatabase
akhileshsooji028
akhileshsooji028

Reputation: 39

query to get data from multiple table only if available else get as null

The below query satisfies the requirement only if there is data in all the mentioned tables because the "AND" is used and "AND" truth table specifies if all condition are true the it provides the output else it does not give the output.

But if there is no data in one of the depended table then the query returns no output only because of the "AND" function.

Need help to use "OR" instead of "AND" so that if any of the depended table does not have any data it should return as null/NA.

   SELECT a.id, 
       a.userId, 
       a.trainerName, 
       a.bioDescription, 
       a.trainingCategories, 
       a.preferredWorkouts, 
       a.gymAccess, 
       a.gymLocation, 
       a.equipments, 
       a.perSession, 
       a.groupSession, 
       a.virtualSession, 
       a.sessionPacks,
       b.id, 
       b.username, 
       b.profileImg, 
       c.trainerId, 
       AVG(c.ratings) as avgRatings, 
       count(d.trainerId) as totalSessions
FROM trainerProfile a, users b, trainerFeedback c, allEvents d
WHERE a.id = 1 AND a.userId = b.id AND a.id = c.trainerId;

Upvotes: 0

Views: 290

Answers (1)

user18098820
user18098820

Reputation:

You need to use left join and group by

   SELECT 
       a.id, 
       a.userId, 
       a.trainerName, 
       a.bioDescription, 
       a.trainingCategories, 
       a.preferredWorkouts, 
       a.gymAccess, 
       a.gymLocation, 
       a.equipments, 
       a.perSession, 
       a.groupSession, 
       a.virtualSession, 
       a.sessionPacks,
       b.id, 
       b.username, 
       b.profileImg, 
       c.trainerId, 
       AVG(c.ratings) as avgRatings, 
        as totalSessions,
       d.tid AS trainerId
FROM trainerProfile a
LEFT JOIN users b ON a.userId = b.id
LEFT JOIN trainerFeedback c ON a.id = c.trainerId
CROSS JOIN (SELECT COUNT(trainerId) tid FROM allEvents) d
WHERE a.id = 1 
GROUP BY
       a.userId, 
       a.trainerName, 
       a.bioDescription, 
       a.trainingCategories, 
       a.preferredWorkouts, 
       a.gymAccess, 
       a.gymLocation, 
       a.equipments, 
       a.perSession, 
       a.groupSession, 
       a.virtualSession, 
       a.sessionPacks,
       b.id, 
       b.username, 
       b.profileImg, 
       c.trainerId, 
       d.tid 
ORDER BY
       a.id,
       a.userId;

Upvotes: 1

Related Questions