Pulling Data From Multiple Tables

Hi Experts,

I know how to pull data from multiple tables, but I need to pull from 5 tables, 4 of which I know will return data, but how do I do the fifth table so that it will return the top one record, but if there is no record, just to return an empty string?
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you please provide sample data from 5 tables and the expected output?
0
zephyr_hex (Megan)DeveloperCommented:
Use a LEFT JOIN with a CASE STATEMENT.

SELECT t1.Field1, t2.Field2, t3.Field3, t4.Field4, CASE WHEN t5.Field5 IS NULL THEN '' ELSE t5.Field5 END) AS  Field5
FROM table1 t1
INNER JOIN table2 t2 ON t1.Id = t2.Id
INNER JOIN table3 t3 ON t1.Id = t3.Id
INNER JOIN table4 t4 ON t1.Id = t4.Id
LEFT OUTER JOIN table5 t5 ON t1.Id = t5.Id

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
but how do I do the fifth table so that it will return the top one record, but if there is no record, just to return an empty string?

it depends on how you defined the top one record, whether it's maximum or minimum value or based on certain sequence.

you probably can do it by using Max or Min function with Group By clause to get the "top record", like by applying to Megan's example:
SELECT t1.Field1, t2.Field2, t3.Field3, t4.Field4, ifnull(max(t5.Field5),'') Field5
FROM table1 t1
INNER JOIN table2 t2 ON t1.Id = t2.Id
INNER JOIN table3 t3 ON t1.Id = t3.Id
INNER JOIN table4 t4 ON t1.Id = t4.Id
LEFT OUTER JOIN table5 t5 ON t1.Id = t5.Id
Group By t1.Field1, t2.Field2, t3.Field3, t4.Field4

Open in new window

or use:
ifnull(min(t5.Field5),'') Field5

Open in new window


ifnull function can be used to return a char in case the value is null.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

APD TorontoSoftware DeveloperAuthor Commented:
With a little bit of research,  I came up with the bellow. Everything else works, but the issue is with the appointments table.  Some clients have multiple appointments and others have none.

For those that have none I just want to get "" for appts.date, appts.time and gmail_id;  but for those that have one or more appointments I want to get the latest.

SELECT clients.id, clients.last_name, clients.first_name, home_phone, cell_phone, pref_phone, clients.email_address, intake.date, statuses.name, CONCAT(users.first_name, ' ', users.last_name) AS os_name 

, appts.date, appts.time, gmail_id


FROM clients INNER JOIN intake ON intake.client_id = clients.id INNER JOIN statuses ON statuses.id = clients.status_id INNER JOIN users ON users.id = clients.specialist_id

/*LEFT JOIN appointments AS appts ON clients.id = appts.client_id*/

LEFT JOIN (SELECT date, time, gmail_id
           FROM appointments AS appts
           LEFT JOIN clients ON clients.id = appts.client_id
WHERE client_id = clients.id
ORDER BY date DESC, time DESC 
LIMIT 1) AS appts ON clients.id = appts.client_id

Open in new window

0
Pawan KumarDatabase ExpertCommented:
PLease try this -

SELECT clients.id, clients.last_name, clients.first_name, home_phone, cell_phone, pref_phone, clients.email_address, intake.date, statuses.name
, CONCAT(users.first_name, ' ', users.last_name) AS os_name 
, appts.date, appts.time, gmail_id
FROM clients INNER JOIN intake ON intake.client_id = clients.id INNER JOIN statuses ON statuses.id = clients.status_id INNER JOIN users ON 
users.id = clients.specialist_id
LEFT JOIN 
( 
			SELECT b.date,b.Time,b.client_id,b.gmail_id FROM 
			(
				SELECT MAX(date) date, MAX(time) Time, client_id
				FROM appointments AS appts    
				GROUP BY client_id       			
			)k INNER JOIN appointments b ON k.date = b.date and k.Time and k.client_id = b.client_id
) AS appts ON clients.id = appts.client_id

Open in new window

0
APD TorontoSoftware DeveloperAuthor Commented:
Doesn't max give you the maximum value across all records?

If I have
2017-12-08 at 14:00 and
2017-12-19 at 09:00

would max give you 2017-12-19 at 14:00, which is combination?
0
Pawan KumarDatabase ExpertCommented:
You need show your data .. then we can test the query against ,,, I dont know which table has which data.
help us to help u.
0
APD TorontoSoftware DeveloperAuthor Commented:
Actually, your code works perfectly, I just wanted a little explanation.
0
Pawan KumarDatabase ExpertCommented:
oh... cool. :)

So I am taking latest record from appointments table using MAX(date) date, MAX(time) Time for each client id.

Then we have to fetch the gmail_id from appointments table , we joined again with appointments table on clientid,MAX(date) and MAX(time), so that we will get the gmail id of latest record.

I also select clientid from inner query so that we can join it to the outer query..  So once the inner query joined with outer on client id we shall get the records

I hope this explanation help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
Thank you!
0
Pawan KumarDatabase ExpertCommented:
welcome , glad to help as always :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.