SQL Return Column values only when >1 and

  • Hi everyone,


    I am hoping someone can help me with this request. I am a novice SQL user and could really use some help.


    I am writing a Select statement that retrieved data from multiple tables (we’ll use 2 tables for the example) using joins . Now I would like to add a column and only have the data appear if criteria matches and if the count is greater than 1.


    Select Table1 customers ONLY IF Count of records for Purchase ID >1 AND Match ID, Purchase ID, and start date from both tables to also pull Quantity


    RESULTS I am seeking:
    ABC Customer| 1234| 55213| 9/1/19| 10/1/19| 500
    ABC Customer| 1234| 55213| 9/1/19| 10/1/19| 300


    Table1
    Customer Name| ID| Purchase ID| Start Date| End Date| Price
    ABC Customer| 1234| 55213| 9/1/19| 10/1/19
    Crisp LTD| 7089| 34750| 11/1/19| 12/1/19
    ABC Customer| 1234| 55213| 9/1/19| 10/1/19
    Tiff Springs| 1407| 77589| 7/1/19| 8/1/19
    ABC Customer| 1234| 55213| 10/1/19| 11/1/19


    Table2
    Customer Name| ID| Date| Quantity
    ABC Customer| 1234| 9/1/19| 500
    Crisp LTD| 7089| 34750| 7/1/19| 100
    ABC Customer| 1234| 5/1/19| 200
    Tiff Springs| 1407| 77589| 600
    ABC Customer| 1234| 9/1/19| 300


    Thank you!!!

  • Table two seems to have some values in the Date Column that do not appear to be dates or numerical values for dates. Please clarify. ie. Crisp LTD 34750 and Tiff Springs 77589


    If I remove those numbers and replace with a date, then


    PHP
    1. SELECT tbl1.[Customer Name], tbl1.Id, tbl1.[Purchase ID], tbl1.[Start Date], tbl1.[End Date], tbl2.Quantity
    2. FROM tbl1 INNER JOIN tbl2 ON (tbl1.[Start Date] = tbl2.Date) AND (tbl1.Id = tbl2.Id)
    3. GROUP BY tbl1.[Customer Name], tbl1.Id, tbl1.[Purchase ID], tbl1.[Start Date], tbl1.[End Date], tbl2.Quantity;


    [TABLE="border: 1, cellspacing: 0"]

    [tr]


    [/tr]


    [tr]


    [td]

    ABC Customer

    [/td]


    [td]

    1234

    [/td]


    [td]

    55213

    [/td]


    [td]

    9/1/19

    [/td]


    [td]

    10/1/19

    [/td]


    [td]

    300

    [/td]


    [/tr]


    [tr]


    [td]

    ABC Customer

    [/td]


    [td]

    1234

    [/td]


    [td]

    55213

    [/td]


    [td]

    9/1/19

    [/td]


    [td]

    10/1/19

    [/td]


    [td]

    500

    [/td]


    [/tr]


    [/TABLE]