SQL: A Case For CROSS JOIN
Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join. It is usually an INNER JOIN or [LEFT | RIGHT | FULL] OUTER JOIN, but SQL also provides a CROSS JOIN ... The CROSS JOIN takes all entries of one table and combine them with all entries of a second table; because of this, it does not allow for an ON clause. There are rare occasions when you would use it, so I decided to illustrate one.
Imagine a schema where you have the following tables: contentKeys, languages, and content. This db allows you to store content in various languages. contentKeys will store the unique keys for content pieces, which after combined with languages, will return a unique piece of content in a particular language. The schema looks as follows:
The content table has a unique contraint for FK_key and FK_language (FK denotes it's a forgeign key).
Now, what if you need to know which keys exist for one language and not for others, or even which keys exist and contain content in no languages at all? We'll build a query to show this information.
The first step is to find all combinations of keys and languages. To do this we need to combine all entries in they contentKeys table with all entries in the languages table.
The next step is understanding OUTER JOINs. An outer join selects all of the records from one database table and only those records in the second table that have matching values in the joined field. In a left outer join, the selected records will include all of the records in the first database table. In a right outer join, the selected records will include all records of the second database table.
That said, if you OUTER JOIN the combination of all possible keys in all possible languages with your content table, the resulting query will let you know which keys have been translated, and which ones have not.
ON helper.pk_language = dbo.content.fk_language AND helper.pk_key = dbo.content.fk_key
We called the cross-joined table 'helper', and this query will return all rows there, matching them to the content table. All the exiting content/language combinations will have data in the content table, and those what do not exist will have null values. You may enter an additional where clause to filter only null values, which will indicate exactly which content keys / language combination are missing.
So for the full query, we'll take advantage of the dynamic table aliasing capabilities of sql and it looks like this:
content.pk_content, helper.pk_key, helper.pk_language,
content.content
FROM (SELECT * FROM contentKeys CROSS JOIN languages) helper
LEFT OUTER JOIN dbo.content
ON helper.pk_language = dbo.content.fk_language AND helper.pk_key = dbo.content.fk_key
ORDER BY helper.content_key, helper.[language]
http://www.robgonda.com/blog/trackback.cfm?C5D08769-3048-7431-E4E7D659397C7F3F
There are no comments for this entry.
[Add Comment]