CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MrPatterns
MrPatterns

Reputation: 4434

How to loop through SQL table and find rows that match your IF conditions?

Here's what I would like to do. Let's say I have a table of customers (Table X) who have entered an amusement park like Disneyland. This customer table is extremely detailed, with columns like name, age, race, income level, zip code where they are from and a time stamp of when they entered the park.

For each row in Table X, I would like to query another customer-info table (Table Y) to identify all other customers who share common characteristics with the row in question, and who have entered the Disneyland premises at an earlier point in time.

For example, from Table X I'm looking at Joe who is a 49 year old guy from Texas who makes $70,000 who visited Disneyland on June 10th, 2013. While my loop is on Joe's row, I want to search Table Y for all 49 years old males from Texas who also makes $70,000 but visited Disneyland BEFORE Joe. I'd like to group the Table X row, and the results from Table Y together (bunched together). What's the best way of doing this in SQL?

If it were VB I know how to do this using a For Next loop with If statements inside. But the data is in a SQL database, and I want to learn how to do the equivalent.

(Using SQL Server Management Studio)

Upvotes: 1

Views: 7904

Answers (2)

squillman
squillman

Reputation: 13641

You should be able to use an INNER JOIN.

Something like:

SELECT
 -- [whatever columns you need]
FROM TableZ t1
INNER JOIN TableZ t2 on t1.Salary=t2.Salary
    AND t1.Gender=t2.Gender
    AND t1.ResidenceState=t2.ResidenceState
    AND t1.VisitDate > t2.VisitDate
WHERE t1.Salary=70000
AND t1.Gender='Male'
AND t1.ResidenceState='TX'

Obviously that's assuming column names and datatypes in your table. The JOIN condition will vary based on the information you want to match on.

Upvotes: 3

Guillermo Gutiérrez
Guillermo Gutiérrez

Reputation: 17849

I guess you could do this:

Method 1: JOIN

SELECT Name, Age, Salary, VisitDate, Gender, State
FROM
(
    SELECT TY.Name, TY.Age, TY.Salary,
        TY.VisitDate, TY.Gender, TY.State
    FROM TableX TX
        INNER JOIN TableY TY
            ON TY.Age=TX.Age AND TY.Salary=TX.Salary
              AND TY.Gender=TX.Gender AND TY.State=TX.State
              AND TY.VisitDate<TX.VisitDate
    WHERE TX.Name = 'Joe' AND ...

    UNION ALL

    SELECT TX.Name, TX.Age, TX.Salary, TX.VisitDate, TX.Gender, TX.State
    FROM TableX TX
    WHERE TX.Name = 'Joe' AND ...
) A

Method 2: CROSS APPLY (More complex)

SELECT Name, Age, Salary, VisitDate, Gender, State
FROM
(
    SELECT TYCA.Name, TYCA.Age, TYCA.Salary,
        TYCA.VisitDate, TYCA.Gender, TYCA.State
    FROM TableX TX
        CROSS APPLY
        (
            SELECT TY.Name, TY.Age, TY.Salary,
              TY.VisitDate, TY.Gender, TY.State
            FROM TableY
            WHERE TY.Age=TX.Age AND TY.Salary=TX.Salary
              AND TY.Gender=TX.Gender AND TY.State=TX.State
              AND TY.VisitDate<TX.VisitDate
        ) TYCA
    WHERE TX.Name = 'Joe' AND ...

    UNION ALL

    SELECT TX.Name, TX.Age, TX.Salary, TX.VisitDate, TX.Gender, TX.State
    FROM TableX TX
    WHERE TX.Name = 'Joe' AND ...
) A

If you take out WHERE TX.Name = 'Joe' AND ..., the query will get you all TableX's rows with all TableY's rows that comply the requirements, without the need of a loop.

Upvotes: 1

Related Questions