I've inherited a "task" because someone left, and I'm trying to figure out the best way to get the information I need.

Because the database itself has some really sensitive data, I'm going to use fake data, but the structure is the same.

firstTable

id | store | list | regionNumber

1 | Safeway | Broadway, Polk, Divisadero | 345
2 | Vons | Clay, Van Ness, Kearny, Market | 345
3 | Ralph's | Army, 25th Ave, 32nd Street, 5th Avenue, 10th Avenue | 345

4 | Quik Stop | Crespi, Holloway | 390

secondTable

id | store | location | regionNumber

1 | Safeway | Broadway | 345
2 | Safeway | Broadway | 345
3 | Safeway | Polk | 345
4 | Ralph's | 32nd Street | 345
5 | Ralph's | Army | 345
6 | Vons | Clay | 345
7 | Vons | Clay | 345
8 | Vons | Clay | 345

9 | Vons | Market | 345

Ultimately, I want to output the following:

Safeway
- Broadway 2
- Polk 1
- Divisadero 0

Vons
- Clay 3
- Van Ness 0
- Kearny 0
- Market 1

Ralph's
- Army 1
- 25th Ave 0
- 32nd Street 1
- 5th Avenue 0
- 10th Avenue 0

Quik Stop
- Crespi 0
- Holloway 0

Where the numbers above are tallies of what's in the secondTable. I'm trying to figure out if there's a way to do a join here in order to get the results I want, but the statements that aren't generating syntax errors, really aren't coming out the way I expect. :/ I should mention that I'm using postgres.

    When I am writing "Report Summary" type pages, one of the things I do is retrieve the data, but do some of the sorting in an array. One example might be for location counting

    $store[$store_name][store_count]++;

    And then foreach though them, or honestly I do one call to summarize the data, and then do a second call using DISTINCT to individual the data. SOrt of depend if you are in a resource scarce server environment

    So an array is created for each store_name that is turning up results, and being autoincremented, so when I go through the display, I can just output

    echo $store[$store_name][store_count];

    Now, in some of the new versions of MYSQL some of this can be done using multiple selects, with counts involved, but using random hosting machines in the wild I have no idea what I am likely to run into, plus I don't do posgres

    HERE IS MYSQL NOT POSGRESS version of what is might look like

    SELECT

    firstTable.id,
    firstTable.store,
    firstTable.list,
    firstTable.regionNumber,

    secondTable.id,
    secondTable.store,
    secondTable.location,
    secondTable.regionNumber

    FROM

    firstTable

    LEFT JOIN

    secondTable
ON

first_Table.store = secondTable.store
AND 
first_Table.regionNumber = secondTable.regionNumer

    Good luck

      Write a Reply...