SQL DB View and Joins

[dubious]

Hi all,

I'm trying to create a db view in SQL for a web app to use in building a report that I need. The problem is that the query for creating this view is somewhat beyond my current skill level with SQL so I'm having some issues getting it to work.

I need to link a geographical region in a table (geo_regions) to financial data in another table (ls). A colleague helped me out a bit with it so I have all the columns I need in the view the only thing is that data is being duplicated. There should 41 records but I'm getting over 280. There are several tables between geo_region and ls that need to be included and with all the joins being used it's way over my head. The below is the script that was used to create the view

CREATE VIEW [afm].[za_geo_regn_ls]
AS
SELECT     a.geo_region_id, a.geo_region_name, b.ctry_id, b.name AS ctry_name, c.regn_id, c.name AS regn_name, d.state_id, d.name AS state_name, e.city_id, e.name AS city_name, f.site_id, f.name AS site_name, g.pr_id AS pr_pr_id, h.pr_id AS ls_pr_id, g.name AS pr_name_name, h.ls_id, h.amount_base_rent                     
FROM        afm.geo_region AS a LEFT OUTER JOIN
				afm.ctry AS b ON a.geo_region_id = b.geo_region_id LEFT OUTER JOIN
				afm.regn AS c ON b.ctry_id = c.ctry_id LEFT OUTER JOIN
				afm.state AS d ON c.regn_id = d.regn_id LEFT OUTER JOIN
				afm.city AS e ON d.state_id = e.state_id LEFT OUTER JOIN
				afm.site AS f ON e.city_id = f.city_id LEFT OUTER JOIN
				afm.property AS g ON f.site_id = g.site_id LEFT OUTER JOIN
				afm.ls AS h ON g.pr_id = h.pr_id

Any help would be greatly appreciated

 

[diabolus]

Well the quickfix is to put a

SELECT DISTINCT in there. (Just add the word DISTINCT after SELECT) i.e. "SELECT DISTINCT a.geo_region_id, a...."

The longer fix is to look at those joins, you most likely need to do INNER joins (instead of LEFT joins) to get unique results. But do the distinct part first as it can usually solve the issue right there.

 

[Nerfherder]

Why are you using outer join.

Try using just "join".

Use the Left join if any of the tables don't have a state or city etc

 

[envo]

Hi all,

I'm trying to create a db view in SQL for a web app to use in building a report that I need. The problem is that the query for creating this view is somewhat beyond my current skill level with SQL so I'm having some issues getting it to work.

I need to link a geographical region in a table (geo_regions) to financial data in another table (ls). A colleague helped me out a bit with it so I have all the columns I need in the view the only thing is that data is being duplicated. There should 41 records but I'm getting over 280. There are several tables between geo_region and ls that need to be included and with all the joins being used it's way over my head. The below is the script that was used to create the view

CREATE VIEW [afm].[za_geo_regn_ls]
AS
SELECT     a.geo_region_id, a.geo_region_name, b.ctry_id, b.name AS ctry_name, c.regn_id, c.name AS regn_name, d.state_id, d.name AS state_name, e.city_id, e.name AS city_name, f.site_id, f.name AS site_name, g.pr_id AS pr_pr_id, h.pr_id AS ls_pr_id, g.name AS pr_name_name, h.ls_id, h.amount_base_rent                     
FROM        afm.geo_region AS a LEFT OUTER JOIN
				afm.ctry AS b ON a.geo_region_id = b.geo_region_id LEFT OUTER JOIN
				afm.regn AS c ON b.ctry_id = c.ctry_id LEFT OUTER JOIN
				afm.state AS d ON c.regn_id = d.regn_id LEFT OUTER JOIN
				afm.city AS e ON d.state_id = e.state_id LEFT OUTER JOIN
				afm.site AS f ON e.city_id = f.city_id LEFT OUTER JOIN
				afm.property AS g ON f.site_id = g.site_id LEFT OUTER JOIN
				afm.ls AS h ON g.pr_id = h.pr_id

Any help would be greatly appreciated

Your joins are wrong. afm.city is joined on afm.site. At no point does it tie back to afm.geo_region, so if you have 1 city (JHB) and 5 "site" within that city, geo_region will show 5 records instead of 1.

Without the table structure of geo_region, I can't really tell how you'd be doing the join, but you're definitely doing it wrong if you want 1 record per geo_region record. Because 1 country has many regions has many state's has many cities has many sites has many properties, and if none of those tie directly into the geo_region table, linking the geo_region to just a country will get you multiple records

 

[dubious]

Your joins are wrong. afm.city is joined on afm.site. At no point does it tie back to afm.geo_region, so if you have 1 city (JHB) and 5 "site" within that city, geo_region will show 5 records instead of 1.

Without the table structure of geo_region, I can't really tell how you'd be doing the join, but you're definitely doing it wrong if you want 1 record per geo_region record. Because 1 country has many regions has many state's has many cities has many sites has many properties, and if none of those tie directly into the geo_region table, linking the geo_region to just a country will get you multiple records

The geo_region table contains several columns but for this particular scenario the only two are populated with data: geo_region_id (the PK) and geo_region_name. When implementing data the data is entered into the following tables in the order:

geo_region
ctry
regn
state
city
site

The ls table doesn't have any direct link to those tables. It's linked to the property table which then links to cntry regn state city and site

 

[@udiS3]

Check your join syntax i.e. you only want results where there are matches from both the tables participating in the join.
The following links will help explain this:

http://www.w3schools.com/sql/sql_join_left.asp
http://www.w3schools.com/sql/sql_join_inner.asp

 

[envo]

The geo_region table contains several columns but for this particular scenario the only two are populated with data: geo_region_id (the PK) and geo_region_name. When implementing data the data is entered into the following tables in the order:

geo_region
ctry
regn
state
city
site

The ls table doesn't have any direct link to those tables. It's linked to the property table which then links to cntry regn state city and site

Okay, well it will give you multiple records then since your database design doesn't accommodate for 1 record per geo region.

IE, joining the way you do, assuming you have 1 ctry and 1 regn and 1 state under that and 1 city under that and 1 site under that, you will get 1 record back for that geo region:

Geo Region 1 | Country 1 | Region 1 | State 1 | City 1 | Site 1

So now you decide, hey, this city in this region in this country in this geo region has more than 1 site in that particular region/state/city combo! When you run the script the next time, it will output:

Geo Region 1 | Country 1 | Region 1 | State 1 | City 1 | Site 1
Geo Region 1 | Country 1 | Region 1 | State 1 | City 1 | Site 2

There's more than 1 city in a region, let's assume they all have just 1 site (except for city 1). Run your query again, and you'll get:

Geo Region 1 | Country 1 | Region 1 | State 1 | City 1 | Site 1
Geo Region 1 | Country 1 | Region 1 | State 1 | City 1 | Site 2
Geo Region 1 | Country 1 | Region 1 | State 1 | City 2 | Site 1
Geo Region 1 | Country 1 | Region 1 | State 1 | City 3 | Site 1
Geo Region 1 | Country 1 | Region 1 | State 1 | City 4 | Site 1
Geo Region 1 | Country 1 | Region 1 | State 1 | City 5 | Site 1


Add another state, with multiple cities, or regions to the country, you can see how this 1 record in your geo_region table will suddenly show over 280 records for just 42 records. A distinct won't help in this case (as suggested)

However, if a geo_region is linked to a country/region/state/city/site combination to make it 1 record, it would mean that the keys referencing the other tables need to exist in your geo_region table, and your join will then look like this:

select * from afm.geo_region a
inner join afm.ctry AS b ON a.geo_region_id = b.geo_region_id 
inner join afm.regn AS c ON a.ctry_id = c.ctry_id 
inner join afm.state AS d ON a.regn_id = d.regn_id 
inner join afm.city AS e ON a.state_id = e.state_id
inner join afm.site AS f ON a.city_id = f.city_id 
inner join afm.property AS g ON a.site_id = g.site_id 
inner join afm.ls AS h ON a.pr_id = h.pr_id

See, all of the joins joins to the key in geo_region table which will give you 1 record per geo_region. Else, see my above scenario. You will find each time a new site/city/region/state gets added, your end result will grow almost exponentially. Which is correct if 1 geo_region covers 1 whole country. But again, I'm not the one over there so I can't really tell what the requirements are or not to really answer you.

also, I'd suggest not using "a b c d e f" as your aliases. if your table names are short enough (which it is) then there's no point in confusing the next person trying to figure out which table is "a" in a complex query. This kind of reeks of MS ACCESS

 

[dubious]

Thanks envo. Appreciate the explanation. I'll have to talks to one of the developers then it seems for some extra assistance with this.

 

[envo]

Thanks envo. Appreciate the explanation. I'll have to talks to one of the developers then it seems for some extra assistance with this.

If this is for a report, the data output is fine. you can then have the report show 1 geo region, then the country etc under a tree-like view

 

[dubious]

If this is for a report, the data output is fine. you can then have the report show 1 geo region, then the country etc under a tree-like view

I'm trying to replicate a pie chart style report from an excel document that a client provided us with. On the spreadsheet it's annual rent by geographical region. With the rent being stored on the ls table and no geo region data on the table it was suggested that creating the db view would be the best idea. Fortunately the report isn't necessary right now

 

[envo]

I'm trying to replicate a pie chart style report from an excel document that a client provided us with. On the spreadsheet it's annual rent by geographical region. With the rent being stored on the ls table and no geo region data on the table it was suggested that creating the db view would be the best idea. Fortunately the report isn't necessary right now

Yea then the output should be fine because it would report rent per site. You'd be able to do the pie chart fine with the data you're getting back since it's probably going to aggregate the rent per region (or city or state or even site depending on the drill down)

Think you were worried for nothing