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
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
SELECT * FROM t1t WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.t2_id=t2.t2_id )
