[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 26
  • Last Modified:

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?
0
APD_Toronto
Asked:
APD_Toronto
1 Solution
 
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 ChongCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
APD_TorontoAuthor 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_TorontoAuthor 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_TorontoAuthor 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
 
APD_TorontoAuthor Commented:
Thank you!
0
 
Pawan KumarDatabase ExpertCommented:
welcome , glad to help as always :)
0

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now