Let's say that you have two tables that have columns of the datatype TEXT...
master: tblmaincourse child: tblcourselessonobjects...and you need all DISTINCT records that are linked or not linked to child tables. What do you do? Using a left join with the distinct clause from master to child table will give an error: "The text, ntext, or image data type cannot be selected as DISTINCT."
To solve the problem, use "UNION ALL". I used this from a VB program to populate a list view. It shows all the courses that are linked/not linked to a lesson object.
select mc.*, 'Linked' as status from tblmaincourse mc where mc.course_id in ( select distinct cl.course_id from tblcourselessonobjects cl) Union All select mc.*, 'Not Linked' as status from tblmaincourse MC where mc.course_id not in ( select distinct cl.course_id from tblcourselessonobjects cl)
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, SQL Server, DB2, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
This was first published in February 2005