Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm working with a database that has 2 tables for each company with 4 companies (8 total DBs for thisquery) and for reasons outside of my control that can't be changed. This is also an sqlite DB.

My app currently has to do 8 round trips to get all the data. I want to consolidate that down to one table view query but I can't figure out how to combined the data in a way that would make it work. Here is an example of the tables.

Table 1 (Type A)

name    zone
ABCD    ABC1
DBAA    CBA1

Table 2 (Type A)

name   zone
ABCD   1234
DBAA   4321

Table 1 (Type B)

zone   weight   rate  
ABC1      1     0.50  
CBA1      2     0.88

Table 2 (Type B)

zone   weight   rate
1234      1     0.52
4321      2     0.80

Finally I want the view to look like this:

name   weight   Table 1 rate  Table 2 rate
CABA      1         0.52          0.50
AEAS      2         0.80          0.88

I tried this for my SQL statement:

SELECT 1A.name, 1B.weight, 1B.rate as A from 1A, 1B WHERE 1A.zone = 1B.zone
UNION ALL
SELECT 2A.name, 2B.weight, 2B.rate as B from 2A, 2B WHERE 2A.zone = 2B.zone

I have also tried a couple joins statements after reading unions must have matching column counts but I can't seem to hit the right query. Any ideas what I'm doing wrong or how I can achieve this with a query?

Any help is greatly appreciated!

Updated with Fiddle example here: http://sqlfiddle.com/#!5/37c19/3/0

share|improve this question
Your SQL seems right; what error(s) do you encounter? – Pieter Geerkens Mar 13 at 3:12
I don't get the Table 1 rate and Table 2 rate as separate columns, instead I only get Table 1 rate when I run the query. If I run both queries on their own I do get the proper data back. – James Jones Mar 13 at 3:19
Where do the names CABA and AEAS come from then? – Pieter Geerkens Mar 13 at 3:22
They are just text values in that table. There is another table that has a list of all of those names, the app gets that list and allows the user to pick one, the app queries those 4 tables with the name field to get the zones to then get the rates. It's crazy I know, that's why I'm hoping to simplify it with a view. – James Jones Mar 13 at 3:29
So show us all the tables that supply fields in the result set, not just some of them. – Pieter Geerkens Mar 13 at 3:36
show 3 more comments

1 Answer

up vote 0 down vote accepted

Here is a query that will produce something similar to your example:

SELECT 
  ZonesOne.name
  , RatesOne.weight
  , RatesOne.rate as Table1Rate
  , RatesTwo.Rate AS Table2Rate

FROM ZonesOne, RatesOne, RatesTwo 

WHERE 
  RatesOne.zone = ZonesOne.zone
  AND RatesOne.Weight = RatesTwo.weight

UNION ALL

SELECT 
  ZonesTwo.name
  , RatesOne.weight
  , RatesOne.rate as Table1Rate
  , RatesTwo.Rate AS Table2Rate

FROM ZonesTwo, RatesOne, RatesTwo     

WHERE 
  RatesOne.zone = ZonesTwo.zone
  AND RatesOne.Weight = RatesTwo.weight

However, your Table 1 Rate and Table 2 Rate seem to be switched around. Also, your data from ZonesTwo has two entries for "DBAA".

share|improve this answer
ah, yes there was an error in my data on Fiddle, but this query does what I was looking for! I've update the original question with proper data if anyone else runs into this. Thanks a ton! – James Jones Mar 20 at 20:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.