How to run multiple SQL queries whose values change?

Question

Let's say I have a database of Amazon customers who made purchases in the last year. It is pretty detailed and has columns like name, age, zip code, income level, favorite color, food, music, etc. Now, let's say I run a query such that I return all Amazon customers who bought Book X.

SELECT NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
FROM [Amazon].[dbo].[Customers]
WHERE BOOK = "X"

This query will return a bunch of customers who bought Book X. Now, I want to iterate through each of those results (iterate through each customer) and create a query based on each customer's individual age, zipcode, and income.

So if the first result is Bob, age 32, lives in zipcode 90210, makes $45,000 annually, create a query to find all others like Bob who share the same age, zipcode, and income. If the second result is Mary, age 41, lives in zipcode 10004, makes $55,000 annually, create a query to find all others like Mary who share the same age, zipcode, and income.

How do I iterate through customers who bought Book X and run multiple queries whose values (age, zipcode, income) are changing? In terms of viewing the results, it'd be great if I could see Bob, followed by all customers who are like Bob, then Mary, and all customers who are like Mary.

Is this even possible in SQL? I know how to do this in C# (for/next loops with if/then statements inside) but am new to SQL, and the data is in SQL.

I use SQL Server 2008.

Sounds like you want a CTE. You can do recursive operations without looping or cursors. Performance may be an issue. — HABO
– HABO, Commented Jun 17, 2013 at 15:21
While many people may vote for cursors here, I suggest against it as cursors can cause query execution time to be affected. I think you'll want to use a CTE on this one. — Kris Gruttemeyer
– Kris Gruttemeyer, Commented Jun 17, 2013 at 15:22
Do you want a separate result set for each one of them? If yes then sql can't achieve it as one sql will return single result set. PL/sql should be used then. — Lokesh
– Lokesh, Commented Jun 17, 2013 at 15:24
On databases, "iterate"=="bad". Use sets instead, you use the results of your first [sub]query as one of the sources to your second [sub]query. — RBarryYoung
– RBarryYoung, Commented Jun 17, 2013 at 15:49

Lokesh · Accepted Answer · 2013-06-17 15:44:42Z

1

If i understood your requirement correctly then a nested quesry should do the job. SOmething like this:

SELECT distinct NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
FROM [Amazon].[dbo].[Customers] a, (SELECT NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
FROM [Amazon].[dbo].[Customers]
WHERE BOOK = "X" and name = 'Bob') b
WHERE BOOK = "X" and a.age=b.age and a.zipcode= b.zipcode and a.income=b.income

EDIT: A generic query will be [This will have list of all users]:

SELECT distinct NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
    FROM [Amazon].[dbo].[Customers] a, (SELECT distinct NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
    FROM [Amazon].[dbo].[Customers]
    WHERE BOOK = "X" ) b
    WHERE a.BOOK = b.book and a.age=b.age and a.zipcode= b.zipcode and a.income=b.income 
order by name

edited Jun 17, 2013 at 15:44

answered Jun 17, 2013 at 15:35

Lokesh

7,9487 gold badges50 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrPatterns Over a year ago

Hi Loki, it looks like you are referencing 'Bob' explicitly. How does the query know to look up Mary, and all of the other customers who bought Book X, without knowing their names in advance?

Lokesh Over a year ago

see my edit. This query will return a combined resultset of all users 'Bob', 'Mary' etc. Thats why i earlier asked if you are ok with combined resulset.

RBarryYoung · Accepted Answer · 2013-06-17 16:19:52Z

0

Something like this can do it in one query:

;WITH cteSource as
(
    SELECT NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
    FROM [Amazon].[dbo].[Customers]
    WHERE BOOK = "X"
)
SELECT sr.NAME AS SrcName, cu.NAME AS LikeName
FROM [Amazon].[dbo].[Customers] AS cu
JOIN cteSource As sr
    ON  cu.AGE      = sr.AGE
    And cu.ZIPCODE  = sr.ZIPCODE
    And cu.INCOME   = sr.INCOME

edited Jun 17, 2013 at 16:19

answered Jun 17, 2013 at 15:58

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

1 Comment

RBarryYoung Over a year ago

New version: (there were typos in the original).

HABO · Accepted Answer · 2013-06-17 16:37:35Z

Something like this will let you chase related customers to an arbitrary, e.g. 5 here, degree of separation. By constructing the JOINs correctly you can do things like match income within a range, ... .

with Book as (
  select Id, Name, Age, ZIPCode, Income -- ...
    from Amazon.dbo.Customers
    where Book = 'X' ),
  RelatedCustomers as (
  select C.Id, C.Name, C.Age, C.ZIPCode, C.Income, 1 as Depth -- ...
    from Amazon.dbo.Customers as C inner join
      Book as B on B.Id <> C.Id and Abs( B.Income - C.Income ) < 2000 -- and ...
  union all
  select C.Id, C.Name, C.Age, C.ZIPCode, C.Income, RC.Depth + 1-- ...
    from Amazon.dbo.Customers as C inner join
      RelatedCustomers as RC on RC.Id <> C.Id and Abs( RC.Income - C.Income ) < 2000 -- and ...
      where Depth < 5 )
  select *
    from RelatedCustomers

Tobsey · Accepted Answer · 2013-06-17 17:15:51Z

0

I think you need two separate queries. First one to bring back the customers, once a customer such as Bob is selected a second query is performed based on Bob's attributes.

A simple example would be a forms application that has two grids. The first displays a list of the users. When you select one of the users the second grid is populated with the results of the second query.

The second query would be something like:

SELECT NAME, AGE, ZIPCODE, INCOME, FAVECOLOR, FAVEFOOD, FAVEMUSIC
FROM [Amazon].[dbo].[Customers]
WHERE Age = @BobsAge AND ZipCode = @BobsZipCode AND Income = @BobsIncome

It sounds like you want a simple self-join:

SELECT 
    MatchingCustomers.NAME, 
    MatchingCustomers.AGE, 
    MatchingCustomers.ZIPCODE, 
    MatchingCustomers.INCOME, 
    MatchingCustomers.FAVECOLOR, 
    MatchingCustomers.FAVEFOOD, 
    MatchingCustomers.FAVEMUSIC
FROM 
    [Amazon].[dbo].[Customers] SourceCustomer
    LEFT JOIN [Amazon].[dbo].[Customers] MatchingCustomers
        ON  SourceCustomer.Age = MatchingCustomer.Age
        AND SourceCustomer.ZipCode = MatchingCustomer.ZipCode 
        AND SourceCustomer.Income = MatchingCustomer.Income 
WHERE
    SourceCustomer.Book = 'X'

If you want to see the all source customers and all of their matches in a single result set you can remove the where clause and select data SourceCustomer also:

SELECT 
    SourceCustomer.Name SourceName,
    SourceCustomer.Age SourceAge 
    SourceCustomer.ZipCode SourceZipCode,
    SourceCustomer.Income SourceIncome,
    MatchingCustomers.NAME, 
    MatchingCustomers.AGE, 
    MatchingCustomers.ZIPCODE, 
    MatchingCustomers.INCOME, 
    MatchingCustomers.FAVECOLOR, 
    MatchingCustomers.FAVEFOOD, 
    MatchingCustomers.FAVEMUSIC
FROM 
    [Amazon].[dbo].[Customers] SourceCustomer
    LEFT JOIN [Amazon].[dbo].[Customers] MatchingCustomers
        ON  SourceCustomer.Age = MatchingCustomer.Age
        AND SourceCustomer.ZipCode = MatchingCustomer.ZipCode 
        AND SourceCustomer.Income = MatchingCustomer.Income 
WHERE
    SourceCustomer.Book = 'X'

edited Jun 17, 2013 at 17:15

answered Jun 17, 2013 at 15:36

Tobsey

3,40016 silver badges24 bronze badges

6 Comments

MrPatterns Over a year ago

Hi, I thought about that, but it seems rather manual. I think there might be hundreds of customers and doing it your way sounds like it would take a very, very long time to compile the results. Do you know if there is a way to automate iterating through each customer (Bob, Mary, Customer #99, Customer #100, etc.)?

Tobsey Over a year ago

@phan How are you going to interact with the results? If you are writing a .net application to retrieve them and process them in some way then the same theory applies, foreach customer selected { select and process matching customers }

MrPatterns Over a year ago

I want to eyeball the results within SQL Server. There is no desire to write a .net application.

MrPatterns Over a year ago

Hi, I looked at all of your examples, but none seem to focus only on customers that bought Book X as described in my example. You are looking at every customer, whereas I only care to drill down on those that bought Book X.

Tobsey Over a year ago

@phan Sorry I've edited the Where clauses. Is the Book column really in the Customers table though or are you able to join Customers to Orders already?

|

Collectives™ on Stack Overflow

How to run multiple SQL queries whose values change?

4 Answers 4

2 Comments

1 Comment

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related