Checking table1 connects to table2

  |   By  |  0 Comments

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 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

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


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

SELECT COUNT(ticket.ticket_id) AS delegates, CONCAT_WS(', '.church.church_name,church_town,country.country_name) AS church FROM church,country,ticket WHERE ticket.church_id=church.church_id AND church.country_id=country.country_id GROUP BY church.church_id ORDER BY delegates DESC

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

SELECT COUNT(ticket.ticket_id) AS delegates, ticket_type.ticket_description AS description FROM ticket,ticket_type WHERE ticket.ticket_type_id=ticket_type.ticket_type_id GROUP BY ticket_type.ticket_type_id ORDER BY delegates DESC

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?

SELECT * FROM ticket WHERE NOT EXISTS(SELECT * FROM church WHERE ticket.church_id=church.church_id )

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