Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    May 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query on multiple tables

    First post on this forum, so hello!

    I'm still fresh in MySQL and have some problems with complex queries. Below is one of the task I have to do with tables and. It's part of big exercise, I hope to leanr a lot from it, but I'm stuck on this point
    Task is: write a query that return amount of invoices for january with the division of the size of the cities for medium and small companies.

    Table Companies:
    Code:
    use exercise;
    
    create table Companies 
    (
        ID  int,
        Name    varchar(100),
        City    varchar(100),
        c_size varchar(100),
        PRIMARY KEY (ID)
    );
    
    insert into Companies(ID, Name, city, c_size) 
    values
    (222, 'Karma LLC','CITY2', 'big'),
    (223, 'Manny Ind.','CITY1', 'medium'),
    (224, 'Random PLC','CITY1', 'medium'),
    (225, 'Hijack LLC','CITY1', 'medium'),
    (226, 'Travels LLC','CITY1', 'small'),
    (227, 'Mirana Ind.','CITY2', 'small'),
    (228, 'Polla Ind.','CITY3', 'small'),
    (229, 'Americano LLC','CITY3', 'small'),
    (230, 'Macaroni LLC','CITY4', 'small');
    Table INVOICES:
    Code:
    CREATE TABLE INVOICES
    (
        ID INT auto_increment,
        `DATE` DATE,
        Company INT,
        Amount DECIMAL(7,2),
        PRIMARY KEY (ID)
    );
    
    INSERT INTO INVOICES(`DATE`,Company,Amount)
    VALUES
    ('2014-01-02', 222, 19500.00),
    ('2014-01-02', 222, 29205),
    ('2014-01-07', 222, 152.50),
    ('2014-01-07', 223, 6590),
    ('2014-01-07', 223, 4999),
    ('2014-01-09', 224, 720.60),
    ('2014-01-09', 225, 1100),
    ('2014-02-05', 226, 885),
    ('2014-02-05', 222, 9678.7);
    Table cities:
    Code:
    CREATE TABLE cities
    (
        Name VARCHAR(10),
        population_range_id int,
        PRIMARY KEY (Name)
    );
    
    CREATE TABLE population_range
    (
        ID INT auto_increment,
        lower int,
        upper int,
        PRIMARY KEY (ID)
    );
    insert into cities(Name, population_range_id)
    values
    ('CITY1', 2),
    ('CITY4', 1),
    ('CITY2', 2),
    ('CITY3', 3);
    
    insert into population_range(lower,upper)
    values
    (0,200),
    (200,500),
    (500,1000);
    There is query I've written, but it returns only 1 record which containt (lower,upper,TOTAL_INV_FOR_CITY_SIZE): (200,500,72830.80) which is correct BUT what about rest of cities and how to set condition for display only small and medium companies for january?

    Code:
    select
      pr.lower
    , pr.upper
    , sum(i.Amount) as TOTAL_INV_FOR_CITY_SIZE
    from
      INVOICES i
      join Companies co on i.Company=co.ID
      join cities cit on cit.Name=co.City
      join population_range pr on cit.population_range_id=pr.ID
    group by pr.lower,pr.upper;
    I know it's a lot of text but I want to be well understood.
    Sorry for poor english and plz halp

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,679
    Thanks
    25
    Thanked 655 Times in 654 Posts
    Thank you so much for the information you provided. It allows anyone to create the tables and populate them. Now we have the same DB you have and can run your query with having to guess at the results.
    And you command of English is very good.

    Your query gave you the correct answer.
    Your first JOIN ON will yield => 222(4 times) 223(2 times) and 224 225 226 (once each)
    The Next yields => city2 city1
    The third drops it to the number 2.
    2 gives you the lower and upper values you got and the 72830.80 is the sum of all in invoices.

    --------------------------

    I think your tables are set up wrong.
    In companies you have Karma LLC in CITY2 and have c_size as big. I take it c_size means size of the city.
    You also have Mirana Ind. in the same city CITY2 but c_size is now small????????
    Same table has CITY1 as small and medium

    I think dropping the CITYstuff and just using small med large would work better for you and would eliminate one table.
    Last edited by sunfighter; 05-30-2014 at 06:56 PM.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    New to the CF scene
    Join Date
    May 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's ok. My bad I did not explained what may be incomprehensible. c_size means company_size, not city_size. That's part of the problem, query should not include big companies (actually only one company here). It looks kinda tricky.
    I think the outcome result should look something like that:

    size_of_the_city(=population_range.id)-----------------sum(Amount)_for_january
    |
    -----1(small city)------------------------------------|------0 or NULL (^1)-----|
    -----2(medium city)----------------------------------|-----------N (^2)-------|
    -----3(big city)--------------------------------------|------0 or NULL(^3)-----|

    Explanations:
    ^1 - 0 or NULL (I don't know which), because there is only one company (230 - Macaroni LLC), who did not pay any invoice;
    ^2 - N means amount of invoices paid by companies: 223 (they paid twice), 224, 225 - only they paid in JANUARY and are companies from city which has population between 200 and 500 (CITY1 and CITY2). Company 222 is not involved, because they are big company (c_size='big') and I want only medium and small companies;
    ^3 - 0 or NULL, because companies from big cities (population between 500 and 1000) did not pay invoices in january (they did not pay any invoices);

    I've got it on my paper, but it's so hard to translate it for MySQL

    I've updated my query aswell, so it takes invoices only from january:
    Code:
    select
      pr.lower
    , pr.upper
    , sum(i.Amount) as TOTAL_INV_FOR_CITY_SIZE
    from
      INVOICES i
      join Companies co on i.Company=co.ID
      join cities cit on cit.Name=co.City
      join population_range pr on cit.population_range_id=pr.ID
      and MONTH(i.`DATE`)=1 
    group by pr.lower,pr.upper
    ;

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,679
    Thanks
    25
    Thanked 655 Times in 654 Posts
    Try this and take out what you don't need or rearrange them to suit yourself.
    PHP Code:
    SELECT  i.dateco.namepop.lowerpop.upperi.amount
      FROM invoices 
    AS icompanies AS cocities AS cpopulation_range AS pop
      WHERE co
    .c_size <> 'big'
      
    AND co.id i.company
      
    AND i.date LIKE '2014-01%'
      
    AND c.name co.city
      
    AND pop.id c.population_range_id 
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •