SQL Server query problem when selecting data from child table based on column in parent table

Question

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.

Answer 1

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