July 1, 2011 at 6:25 pm
I would like to build a select statement to join two tables which both have a composite PK comprised of two columns but my select statement is failing.
EG.
Table 1: SERVICE (fiscalyear PK1, service PK2, offering, salesregion)
Table 2: SERVICE_ATTRIBUTE(fiscalyear PK1, service PK2, status, create_date)
select s.offering, s.salesregions, sa.status, sa.create_date
from SERVICE S
join SERVICE_ATTRIBUTE sa on s.fiscalyear = sa.fiscalyear
and s.service = sa.service;
this is not working.
I have been asked to join these tables in this way, yet I do not see how it is possible since the two tables do not share values in common.
July 1, 2011 at 8:33 pm
Based solely on the table definitions provided your looks code. As you noted, however, there is a difference in the data. Since we don't have access to your data we can't really help much more. You will need to analyze the data to determine how to join the data together in the two tables.
July 1, 2011 at 10:34 pm
Lynn, thanks for replying. I think you meant 'the code looks good'. Yes, and it seems very straight forward yet it's not working. Other than the composite PKs in each table, the columns in the tables have nothing in common with each other. Can such a join still work? It seems more like a union...
July 1, 2011 at 10:47 pm
hxkresl (7/1/2011)
Lynn, thanks for replying. I think you meant 'the code looks good'. Yes, and it seems very straight forward yet it's not working. Other than the composite PKs in each table, the columns in the tables have nothing in common with each other. Can such a join still work? It seems more like a union...
Can't tell from here as we can't see the data. You will have to look at the data and do the analysis. Based on the table definitions, yes, your code should work. As you said, the data doesn't match between the two tables. Questions you need to ask and answer are basically about the data. Where does it come from, why doesn't it match between the two tables, etc.
July 1, 2011 at 11:32 pm
Lynn, how does 'where does the data come from' impact the join? The column names are different, the data types could be different, and the data is there. Where it comes from? Please give me an example of how that would affect a properly syntaxed join on two tables that share a composite PK.
July 2, 2011 at 12:10 am
well, I'll confess what the problem was. I hadn't specified the schema names, which in this case was important. ie.Tables belonged to different schemas.
Thank you for steering me in the right direction.
July 2, 2011 at 12:18 am
Different applications, different screens in the same application, I don't know in your particular case. You need to do some data analysis of your data to figure out how to join the data together to get the results that your customers (users) are expecting. You may need to involve them in this analysis.
As I said, based on what you profided initially, your SQL Statement is syntactically correct.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply