Combining Databases with Conditions

Ignition
, ,
1

I have two databases that I am trying to combine into a single table. I am trying to accomplish what is shown in table "dbA + dbB":

Basically, I would like "dbA + dbB" to populate based on the status in "dbB" equal to "Accepted". But also show when the time slots are empty.

image_2024-04-04_143628782
image_2024-04-04_143628782904×450 8.75 KB

The code I am using is:

SELECT *
FROM dbA
left join dbB on dbA.timeSlot = dbB.timeSlot
where dbB.status = "Accepted"

And my code outputs:

image

I'm sure that there is a fairly simple answer, but I just cannot figure this out... Any help would be greatly appreciated.

Thank you.

2

You have to put the status check into a subquery, and left join against that instead of directly on table B.

(Terminology note: In English, you use "DB" for a collection of tables, not just one table.)

3

So how would that be written? I understand the concept, just not sure how to code that. Also, thank you for the terminology tips.

4

Something like this:

SELECT tableA.TimeSlot, filteredB.Status, filteredB."First Name", filteredB.Last Name¨
FROM tableA
LEFT JOIN (
    SELECT *
    FROM tableB
    WHERE Status = 'Accepted'
) filteredB on tableA.TimeSlot = filteredB.TimeSlot

Note the explicit output columns. The general asterisk can cause problems when there are duplicate column names across a join.

2 Likes
5

That works perfect, thank you!

6

Alternatively, how could I structure a separate query to output available time slots? My plan is to then have a dropdown list that is bound to that query so that the user is able to schedule during an available time.

7

Add WHERE filteredB.TimeSlot IS NULL to the end of the query.

1 Like