Databases
StackOverflow2013
Postsdbo
POSQL: Select Customer from Table1 who purchased material in table2 and find out what else did that customer purchase in the same month?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: Select Customer from Table1 who purchased material in table2 and find out what else did that customer purchase in the same month?
    primarykey
    data
    text
    <p>I have 2 tables that have a common column Material:</p> <p>Table1</p> <pre><code>MaterialGroup | Material | MaterialDescription | Revenue | Customer | Month MG1 | DEF | Desc1 | 12 | Customer A| Nov MG2 | ABC | Desc2 | 13 | Customer A| Nov MG3 | XYZ | Desc3 | 9 | Customer B| Dec MG3 | LMN | Desc3 | 9 | Customer B| Jan MG4 | IJK | Desc4 | 5 | Customer C| Jan </code></pre> <p>Table2</p> <pre><code>Vendor | VendorSubgroup| Material| Category KM1 | DPPF | ABC | Cat1 KM2 | DPPL | XYZ | Cat2 </code></pre> <p>There are two parts of the problem:</p> <p>Part1 is fairly straight forward</p> <p>I want to select all records from table1 where Material in table1 matches Material in table2</p> <p>In the above scenario, I would want this result because the Material "ABC" and "XYZ" are present in table2:</p> <pre><code>MG2| ABC| Desc2| 13 | Customer A| Nov MG3| XYZ| Desc3| 9 | Customer B| Dec </code></pre> <p>I used the following query to get the result and it worked:</p> <pre><code>SELECT T1.* FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.MATERIAL = T2.MATERIAL </code></pre> <p>Part 2 is a bit complicated and I need help for this now:</p> <p>After fetching all records from Table1 where material in table1 matches material in table2, I need to go to that Customer in table1 who purchased material from table2 and find out what else did that customer buy (which materials did he buy) in that same Month?</p> <p>So, in this example: I would want the following result-</p> <pre><code>MG1| DEF| Desc1| 12 | Customer A| Nov </code></pre> <p>because Customer A purchased Material from table2 - they also purchased some other material in the same month.</p> <p>Any help is appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNickyvV
    1. USuser3033933
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL: Select Customer from Table1 who purchased material in table2 and find out what else did that customer purchase in the same month?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CODo you want this for all customers and for all months, or for a specific customer in a given month?
      singulars
      1. POSQL: Select Customer from Table1 who purchased material in table2 and find out what else did that customer purchase in the same month?
      1. USChristos
    2. COI want to fetch all records from table1 for all customers who purchased any material from table 2. Now I need to go to those customers (all such customers) and find out what else did they buy (any material that might not be in table2) during the same month during which they purchase material from table1.
      singulars
      1. POSQL: Select Customer from Table1 who purchased material in table2 and find out what else did that customer purchase in the same month?
      1. USuser3033933
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload