SQL Create unique list of values from table with multiple columns

[Caleeco]

Hello,

I am struggling with a SQL statement (im fairly new to it). I have a table that records a seating plan in an office. So each record represents a day, and each column represents a seat. Users then populate the table to book seats on certain days.

PK_Date	Seat1	Seat2	Seat3	Seat4	Seat5
25/10/2020
26/10/2020	HS	PH
27/10/2020	PH			YT
28/10/2020			KK
29/10/2020					EM

So I wish to return a list

Names
HS
PH
KK
YT
EM

SELECT DISTINCT * FROM (
SELECT S1
FROM SeatingT
union
SELECT S2
FROM SeatingT
union
SELECT  S3
FROM SeatingT
union
SELECT  S4
FROM SeatingT
union
SELECT  S5
FROM SeatingT);

I have tried the following, but feel there may be a more elegant solution? Also I need to add a WHERE <> Null clause somewhere as I have a NULL value returned in my table.

Any help is appreciated
Many Thanks
Caleeco

 

[Joe4]

There is a more elegant way, if your data table structure is normalized (which it isn't).

Your data table should be structured with three fields:
- PK Date
- Seat Number
- Seat Value

So, you would never have multiple seats on a single record (you would have multiple records for each day).
If you structure your table like this, your query becomes very simple, as you only have to look at a single column (and not 5).
And it makes it very easy to add more seats in the future (without having to edit your data table structure, and all your queries, forms, reports, etc).

Designing normalized tables is essential in database design. If you do so, most of your tasks can be done without too much trouble.
However, if your tables are not normalized, they can make what should be very simple tasks very complicated and difficult to do.

For example, I once had a table with 32 different mutual fund columns, as companies could have up to 32 different investements. Every now and then, a mutual fund would be discontinued and we would have to find all of them and replace them. Under the original structure, we had to search 32 different columns explicitly! What a nightmare! After normalizing it, it was simply a matter of searching one column.

See here for more details: Database normalization description - Office

 

[Caleeco]

Hi Joe,

Thanks for the response! Ah ok, that makes sense. Thanks for the link I'll read more into normalization.

In reality my actual table has about 40 columns! Like you say, any changes will be a massive headache to implement.

My original solution was in an attempt to visualize the table on floorplan map. So each column, can be linked to a text box (that the user can type their name into to update the back-end table).

I see if I can recreate this using the table structure you have suggested (will need to put my thinking cap on lol).

Cheers
Caleeco

 

[Joe4]

I see if I can recreate this using the table structure you have suggested (will need to put my thinking cap on lol).

The nice thing with the normalized structure is that it doesn't matter if there are 5 seats of 40 seats. A table of those 3 fields handles both the same.
This kind of table design allows for this sort of "expansion" without requiring you to have to change the stuctures of your database objects.

 

[Caleeco]

The nice thing with the normalized structure is that it doesn't matter if there are 5 seats of 40 seats. A table of those 3 fields handles both the same.
This kind of table design allows for this sort of "expansion" without requiring you to have to change the stuctures of your database objects.

Yes, that sounds good. I already had to do a lot of work today to add 15 new seats... It was pretty tedious!

Am i right in saying that because this is a booking system. The normalised table will need to have date placeholders (so if I have 5 seats, i will need each date repeated 5 times) so that a user can select a date & Select a Seat & then add their name to the table through a field on a form?

 

[Joe4]

Am i right in saying that because this is a booking system. The normalised table will need to have date placeholders (so if I have 5 seats, i will need each date repeated 5 times) so that a user can select a date & Select a Seat & then add their name to the table through a field on a form?

That is one way, though you may also be able to do it on-the-fly with some coding.
A lot of it depends on your needs and how you want to present it to users.