tbl_Records_News and tbl_Records_News_Details has a One-to-one relationship.
tbl_Records_News table has a Primary key named: NAutoID_Header_Forward
tbl_Records_News_Details has a Primary key named NAutoID_Header_Forward (same name)
Here the SQL Statement:
m_sSQL_Base = _ "Select B.*, C.* From tbl_Records_News AS B LEFT JOIN tbl_Records_News_Details AS C ON B.NAutoID_Header_Forward = C.NAutoID_Header_Forward Where B.NAutoID_Header_Forward = " & CInt(sRecord_AutoID) & " Group BY B.NAutoID_Header_Forward;"
I'm getting double results when I run the SQL statement (without) the GROUP BY clause so I'm trying to add Group By to avoid double results; however' when I do add GROUP BY I get the following error: The column name is not valid. [ Node name (if any) = B,Column name = *
Any Ideas to avoid double results or get the GROUP BY working?
As you declare B.*, C.* in your select query, you need to add all those columns to the GROUP BY list to make it work, like the following:Select B.NAutoID_Header_Forward, C.NAutoID_Header_Forward
From tbl_Records_News AS B
LEFT JOIN tbl_Records_News_Details AS C ON B.NAutoID_Header_Forward = C.NAutoID_Header_Forward
Where B.NAutoID_Header_Forward = 2
Group BY B.NAutoID_Header_Forward, C.NAutoID_Header_Forward
With that, as the field "NAutoID_Header_Forward" is a common one, why would you want to list it in the select list twice? So you may want to take it out. As a best practice, do not use ALL (*) in your statements and list the fields one by one and list only the ones you need.
http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy
That is not a valid GROUP BY construct.
How about DISTINCT?
How about expanding the JOIN and/or WHERE clause?
Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016