Is it possible to circumvent this and force the other tables display their content?

Wappler General How To
#1

Here’s my novice inquiry regarding this query:

SELECT * FROM itinerary, pastor, church, service, expense
WHERE itinerary.itinerary_id = pastor.itinerary_id
AND pastor.pastor_id = church.pastor_id
AND church.church_id = service.church_id
AND service.service_id = expense.service_id

My question is:

Can all records be shown from several tables although one table has no match on (service.service_id = expense.service_id) in the above query?

Currently the query fails, and none of the tables that have content show at all if the last table (expense) has no id match (service.service_id = expense.service_id).

Is it possible to circumvent this and force the other tables to display their content and ignore the table with empty rows?

#2

Hey J. R.,

Assuming your select statement is already correct, and you just want to deal with null values you can do things like OR statements to check for null.

Starting with your original:

SELECT * FROM itinerary, pastor, church, service, expense
WHERE itinerary.itinerary_id = pastor.itinerary_id
AND pastor.pastor_id = church.pastor_id
AND church.church_id = service.church_id
AND service.service_id = expense.service_id

Suppose we wanted to include records if the expense.service_id is null we can change the last line:

SELECT * FROM itinerary, pastor, church, service, expense
WHERE itinerary.itinerary_id = pastor.itinerary_id
AND pastor.pastor_id = church.pastor_id
AND church.church_id = service.church_id
AND (service.service_id = expense.service_id OR expense.service_id is null)

You of course could then add others, or more appropriate checks.

–Ken

#3

Thanks Ken!

I never thought about throwing an OR & IS NULL on the end. So this principle can be added to each line in the WHERE clause.

Brilliant! Let me give it a try.

#4

The only way I could get all rows displayed from all tables was with:

SELECT * FROM itinerary, pastor, church, service, expense
WHERE (itinerary.itinerary_id = pastor.itinerary_id
OR pastor.itinerary_id = NULL)
AND (pastor.pastor_id = church.pastor_id
OR church.pastor_id = NULL)

Nope that doesn’t work… it just duplicates and connects the wrong suffix records.

#5

You changed “is null” to “= NULL”, is that because your db version require this? mysql for example uses “is null”

#6

Nope that doesn’t work… I tried both ways, i.e., = NULL , IS NULL; it just duplicates and connects the wrong suffix records. I had to drop back to this:

SELECT *
FROM itinerary, pastor, church
WHERE itinerary.itinerary_id = pastor.itinerary_id
AND pastor.pastor_id = church.pastor_id
ORDER BY church.service_date AND church.am_pm AND church.service_time

And dump the last incomplete table: Expense. With the query above, the result was everything listed except the incomplete Expense table data.

#7

I have no idea what results you are looking for, and in what structure, but you might look into JOINS for your query. I’ve never used a FROM clause with multiple tables listed, when connecting tables, so that seems odd to me. If it is working, great!

#8

I too, use JOINs. That fails too. However, I’m looking for whatever works. Ha! I thought I would throw it out there to see if anyone had other suggestions. I appreciate your assistance!

#9

It just doesn’t appear it’s going to work.

#10

Happy to find a solution for you J. R., but I could use a different explanation of what you are trying to achieve. In other words, what should these results look like; not in SQL syntax, but in terms of what the rows and columns should look like once retrieved – like an excel sheet.

#11

The query that I had referenced earlier retrieves data from five tables. When one table is not have data relative to the linked IDs, it shows no row.

I have created a form that will now retrieve the available data and insert it to the new table. I just query one ID and the available data from the five tables will populate the form… press Enter and the available data can now be seen in the form…

The ‘finished’ product is what you see below.

District%20%26%20Office%20Report%20-%20Louisiana
District & Office Report - Louisiana.png1650×1275 227 KB

I would like to automate the insertion of the dates and days, but I haven’t got that down yet. The form the client has has each week listed as seen in the image.