Double Rows


  • Hello,

    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?





    Tuesday, December 27, 2016 4:56 PM

All replies

  • Hello there,

    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. | | @EkremOnsoy

    Tuesday, December 27, 2016 5:06 PM
  • 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

    Tuesday, December 27, 2016 5:12 PM
  • Given the names of the tables, I would expect a 1:M relationship between the tables rather than the claimed 1:1 relationship. Are you certain about this?
    Tuesday, December 27, 2016 5:36 PM