CODEWITHSUNDEEP
sqlsql-server
user125264
user125264

Reputation: 1827

SQL Query to get only 1 instance of a record where one to many relationship exists

Ive got an SQL Query trying to get 1 record back when a 1 to many relationship exists.

SELECT     dbo.BlogEntries.ID AS blog_entries_id, dbo.BlogEntries.BlogTitle, dbo.BlogEntries.BlogEntry, dbo.BlogEntries.BlogName, 
                      dbo.BlogEntries.DateCreated AS blog_entries_datecreated, dbo.BlogEntries.inActive AS blog_entries_in_active, 
                      dbo.BlogEntries.HtmlMetaDescription AS blog_entries_html_meta_description, dbo.BlogEntries.HtmlMetaKeywords AS blog_entries_html_meta_keywords, 
                      dbo.BlogEntries.image1, dbo.BlogEntries.image2, dbo.BlogEntries.image3, dbo.BlogEntries.formSelector, dbo.BlogEntries.image1Alignment, 
                      dbo.BlogEntries.image2Alignment, dbo.BlogEntries.image3Alignment, dbo.BlogEntries.blogEntryDisplayName, dbo.BlogEntries.published AS blog_entries_published, 
                      dbo.BlogEntries.entered_by, dbo.BlogEntries.dateApproved, dbo.BlogEntries.approved_by, dbo.blog_entry_tracking.id AS blog_entry_tracking_id, 
                      dbo.blog_entry_tracking.blog, dbo.blog_entry_tracking.blog_entry, dbo.BlogCategories.ID, dbo.BlogCategories.BlogCategoryName, 
                      dbo.BlogCategories.BlogCategoryComments, dbo.BlogCategories.DateCreated, dbo.BlogCategories.BlogCategoryTitle, dbo.BlogCategories.BlogCategoryTemplate, 
                      dbo.BlogCategories.inActive, dbo.BlogCategories.HtmlMetaDescription, dbo.BlogCategories.HtmlMetaKeywords, dbo.BlogCategories.entry_sort_order, 
                      dbo.BlogCategories.per_page, dbo.BlogCategories.shorten_page_content, dbo.BlogCategories.BlogCategoryDisplayName, dbo.BlogCategories.published, 
                      dbo.BlogCategories.blogParent
FROM         dbo.BlogEntries LEFT OUTER JOIN
                      dbo.blog_entry_tracking ON dbo.BlogEntries.ID = dbo.blog_entry_tracking.blog_entry LEFT OUTER JOIN
                      dbo.BlogCategories ON dbo.blog_entry_tracking.blog = dbo.BlogCategories.ID

i have some records assigned to 2 different blogcategories, and when i query everything it returns duplicate records.

How do i only return 1 instance of a blog?

Upvotes: 0

Views: 1749

Answers (3)

Devart
Devart

Reputation: 121902

Try this one -

SELECT  blog_entries_id = be.Id
    ,   be.BlogTitle
    ,   be.BlogEntry
    ,   be.BlogName
    ,   blog_entries_datecreated = be.DateCreated
    ,   blog_entries_in_active = be.inActive
    ,   blog_entries_html_meta_description = be.HtmlMetaDescription
    ,   blog_entries_html_meta_keywords = be.HtmlMetaKeywords
    ,   be.image1
    ,   be.image2
    ,   be.image3
    ,   be.formSelector
    ,   be.image1Alignment
    ,   be.image2Alignment
    ,   be.image3Alignment
    ,   be.blogEntryDisplayName
    ,   blog_entries_published = be.published
    ,   be.entered_by
    ,   be.dateApproved
    ,   be.approved_by
    ,   blog_entry_tracking_id = bet.Id
    ,   bet.blog
    ,   bet.blog_entry
    ,   bc2.Id
    ,   bc2.BlogCategoryName
    ,   bc2.BlogCategoryComments
    ,   bc2.DateCreated
    ,   bc2.BlogCategoryTitle
    ,   bc2.BlogCategoryTemplate
    ,   bc2.inActive
    ,   bc2.HtmlMetaDescription
    ,   bc2.HtmlMetaKeywords
    ,   bc2.entry_sort_order
    ,   bc2.per_page
    ,   bc2.shorten_page_content
    ,   bc2.BlogCategoryDisplayName
    ,   bc2.published
    ,   bc2.blogParent
FROM dbo.BlogEntries be
LEFT JOIN dbo.blog_entry_tracking bet ON be.Id = bet.blog_entry
OUTER APPLY (
    SELECT TOP 1 *
    FROM dbo.BlogCategories bc
    WHERE bet.blog = bc.Id
) bc2

Also, I would like to mention that in this case, using of aliases in the column names decreases the size of your query and makes it more convenient for understanding.

Upvotes: 1

granadaCoder
granadaCoder

Reputation: 27852

Here is a Northwind Example.

It will return only 1 row in the Order Detail table for each Order.

Use Northwind
GO

Select COUNT(*) from dbo.Orders
select COUNT(*) from dbo.[Order Details] 


select * from dbo.Orders ord
join
(select ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY UnitPrice DESC) AS "MyRowID" , * from dbo.[Order Details] innerOD) derived1
on ord.OrderID = derived1.OrderID
Where
derived1.MyRowID = 1
Order by ord.OrderID

Upvotes: 0

urlreader
urlreader

Reputation: 6605

if you just need one record back, you can use

SELECT TOP 1 dbo.BlogEntries.ID AS blog_entries_id, dbo.Bl.... (same as you have now).

it is more efficient than SELECT DISTINCT

Upvotes: 0

Related Questions