Sql-server – SQL Server query problem when selecting data from child table based on column in parent table

clustered-primary-keyjoin;primary-keyselectsql server

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and dates (child) within a date range.

When I run the query, I get no values returned, but when I use primary key (param_id) from the parent or child table, I get the values returned that I want. However, I would like to use the column "ticker" in the parent table as the reference in the where clause.

I have 2 tables.

  1. Parent Table
    PK – param_id – int
    FK – fed_id – int (references different fed reserve ids from other table)
    description – varchar(50)
    ticker – varchar(50)

  2. Child Table – holds the data
    (PK, FK) param_id – int
    (PK, FK) attrib_id – int (references different attributes from attribute table)
    (PK) [update] – datetime
    value – decimal(19, 6)

SQL Query below: 

  SELECT [update], [value]   
  FROM m_econ_fred_source 
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id   
  WHERE m_econ_fred_source.ticker = 'PHILINFLEXP3M' AND
        attrib_id = 7 AND   
        [update] BETWEEN '2014-01-01' AND '2016-03-01'  
  ORDER BY [update] ASC

If I do this, it works: 

  SELECT [update], [value]  
  FROM m_econ_fred_source 
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id   
  WHERE m_econ_fred_source.param_id = 2 AND
        attrib_id = 7 AND   
        [update] BETWEEN '2014-01-01' AND '2016-03-01'  
  ORDER BY [update] ASC

I have also made the ticker column unique as well to see if that would help to no avail. I also created the tables using the wizard instead of create statements.

Would anyone be able to tell me what may be wrong with my query or probable causes/solutions to my problem? Thank you. I have looked everywhere for an answer to this problem. I was unable to find a problem similar to this.

Best Answer

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id.

The complete solution is: 

SELECT [update], [value] 
FROM m_econ_fred_source JOIN s_econ_fred 
ON m_econ_fred_source.param_id = s_econ_fred.param_id   
WHERE m_econ_fred_source.param_id = 2 AND
      m_econ_fred_source.attrib_id = 7 AND   
      [update] BETWEEN '2014-01-01' AND '2016-03-01'  
ORDER BY [update] ASC
Related Question