Checking table1 connects to table2

Duplicating data in a database table is a waste of space, so if table1 has some column information repeated frequently then transfer that to a second table and use an id to store in table1 – that’s called normalisation

As an example, I have a conference booking system where delegates where all booking in from one of 80 churches. So three tables to identify a ticket holder, ticket type and their church is a good plan…

ticket_type_id

ticket ticket_type church
forename ticket_description church_name
surname ticket_price church_town
church_id ticket_type_id church_country
ticket_type_id church_id
ticket_id

Because many of the churches were in the same country, I actually had a third table

country
country_name
country_id

And replaced church_country in the church table with country_id

That means that you can pull out data efficiently – how many delegates from each church

In that example COUNT() – counts how many records match the condition

CONCAT_WS is concatenate with separator – eg “The Gateway Church, King’s Lynn, United Kingdom”

GROUP BY ensures the data is grouped by individual Churches

and ORDER BY puts them in descending order

I could also tally by ticket type

Somehow the tallies of delegates grouped by church didn’t tally with delegates grouped by ticket type. What had gone wrong is that there was an error in church_id for a couple of tickets.

How to find the problem and the records that had bad church_id’s? How do you find out if ticket has a church_id that is not in the church table?

To make it transferable, here is the query using t1 and t2

Leave a Reply

Your email address will not be published. Required fields are marked *