473,695 Members | 1,860 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with SELECT Query

I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.

I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:

(User Table)
UserID
Other user data

(Transaction Table)
TransactionID
UserID
Other transaction data such as the date and the transaction result

(TransactionIte m Table)
TransactionItem ID
TransactionID
ProductID

(Product Table)
ProductID
Other product data

If I try to nest a SELECT query to give me the list of UserIDs for all
users who purchased a given ProductID then SQL Server gets very upset
as Nested Querys are only supposed to return a single value.

So, how do I do this? Build the first list of UserIDs and then select
all other ProductIDs for the users in the list excluding the original
ProductID?

I'm certain this must be a fairly straight forward thing for a SQL
server guru so any help would be appreciated...

Thanks

Jim

Jul 23 '05 #1
4 2857
Hi

See how to help use by posting DDL
http://www.aspfaq.com/etiquett*e.asp?id=5006 and example data.

This will get them for products X abd Y if they bought them in the same
transaction and you want details from both products:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItem ID],
P.[ProductID],P.[Other product data], J.[TransactionItem ID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
JOIN [TransactionItem Table] J ON T.[TransactionID] = J.[TransactionID]
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'

Alternatively:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItem ID],
P.[ProductID],P.[Other product data], J.[TransactionItem ID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
WHERE EXISTS ( SELECT * FROM
JOIN [TransactionItem Table] J
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'
WHERE T.[TransactionID] = J.[TransactionID])

Or if you want it at user level and not necessarily in the same transaction:

SELECT U.[UserID], U.[Other user data], T.[TransactionID],
I.[TransactionItem ID],
P.[ProductID],P.[Other product data], J.[TransactionItem ID],
Q.[ProductID],Q.[Other product data]
FROM [User Table] U
JOIN [Transaction Table] T ON U.[UserID] = T.[UserID]
JOIN [TransactionItem Table] I ON T.[TransactionID] = I.[TransactionID]
JOIN [Product Table] P ON I.[ProductID] = P.[ProductID] AND P.[Other product
data] = 'X'
WHERE EXISTS ( SELECT * FROM
JOIN [Transaction Table] S
JOIN [TransactionItem Table] J ON S.[TransactionID] = J.[TransactionID]
JOIN [Product Table] Q ON J.[ProductID] = Q.[ProductID] AND Q.[Other product
data] = 'Y'
WHERE U.[UserID] = S.[UserID] )

John

<ji**@netwasp.c om> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...
I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.

I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:

(User Table)
UserID
Other user data

(Transaction Table)
TransactionID
UserID
Other transaction data such as the date and the transaction result

(TransactionIte m Table)
TransactionItem ID
TransactionID
ProductID

(Product Table)
ProductID
Other product data

If I try to nest a SELECT query to give me the list of UserIDs for all
users who purchased a given ProductID then SQL Server gets very upset
as Nested Querys are only supposed to return a single value.

So, how do I do this? Build the first list of UserIDs and then select
all other ProductIDs for the users in the list excluding the original
ProductID?

I'm certain this must be a fairly straight forward thing for a SQL
server guru so any help would be appreciated...

Thanks

Jim

Jul 23 '05 #2
This is called a relational division. Here is my usual "cut & paste"
about it. You might want to get a copy of SQL FOR SMARTIES for your
desk:

Relational division is one of the eight basic operations in Codd's
relational algebra. The idea is that a divisor table is used to
partition a dividend table and produce a quotient or results table.
The quotient table is made up of those values of one column for which a
second column had all of the values in the divisor.

This is easier to explain with an example. We have a table of pilots
and the planes they can fly (dividend); we have a table of planes in
the hangar (divisor); we want the names of the pilots who can fly every
plane (quotient) in the hangar. To get this result, we divide the
PilotSkills table by the planes in the hangar.

CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane));

PilotSkills
pilot plane
=============== ==========
'Celko' 'Piper Cub'
'Higgins' 'B-52 Bomber'
'Higgins' 'F-14 Fighter'
'Higgins' 'Piper Cub'
'Jones' 'B-52 Bomber'
'Jones' 'F-14 Fighter'
'Smith' 'B-1 Bomber'
'Smith' 'B-52 Bomber'
'Smith' 'F-14 Fighter'
'Wilson' 'B-1 Bomber'
'Wilson' 'B-52 Bomber'
'Wilson' 'F-14 Fighter'
'Wilson' 'F-17 Fighter'

CREATE TABLE Hangar
(plane CHAR(15) NOT NULL PRIMARY KEY);

Hangar
plane
=============
'B-1 Bomber'
'B-52 Bomber'
'F-14 Fighter'

PilotSkills DIVIDED BY Hangar
pilot
=============== ==============
'Smith'
'Wilson'

In this example, Smith and Wilson are the two pilots who can fly
everything in the hangar. Notice that Higgins and Celko know how to
fly a Piper Cub, but we don't have one right now. In Codd's original
definition of relational division, having more rows than are called for
is not a problem.

The important characteristic of a relational division is that the CROSS
JOIN (Cartesian product) of the divisor and the quotient produces a
valid subset of rows from the dividend. This is where the name comes
from, since the CROSS JOIN acts like a multiplication operator.

Division with a Remainder

There are two kinds of relational division. Division with a remainder
allows the dividend table to have more values than the divisor, which
was Codd's original definition. For example, if a pilot can fly more
planes than just those we have in the hangar, this is fine with us.
The query can be written in SQL-89 as

SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot = PS2.pilot)
AND (PS2.plane = Hangar.plane))) ;

The quickest way to explain what is happening in this query is to
imagine an old World War II movie where a cocky pilot has just walked
into the hangar, looked over the fleet, and announced, "There ain't no
plane in this hangar that I can't fly!" We are finding the pilots for
whom there does not exist a plane in the hangar for which they have no
skills. The use of the NOT EXISTS() predicates is for speed. Most SQL
systems will look up a value in an index rather than scan the whole
table. The SELECT * clause lets the query optimizer choose the column
to use when looking for the index.

This query for relational division was made popular by Chris Date in
his textbooks, but it is not the only method nor always the fastest.
Another version of the division can be written so as to avoid three
levels of nesting. While it is not original with me, I have made it
popular in my books.

SELECT PS1.pilot
FROM PilotSkills AS PS1, Hangar AS H1
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane ) = (SELECT COUNT(plane) FROM Hangar);

There is a serious difference in the two methods. Burn down the
hangar, so that the divisor is empty. Because of the NOT EXISTS()
predicates in Date's query, all pilots are returned from a division by
an empty set. Because of the COUNT() functions in my query, no pilots
are returned from a division by an empty set.

In the sixth edition of his book, INTRODUCTION TO DATABASE SYSTEMS
(Addison-Wesley; 1995 ;ISBN 0-201-82458-2), Chris Date defined another
operator (DIVIDEBY ... PER) which produces the same results as my
query, but with more complexity.

Exact Division

The second kind of relational division is exact relational division.
The dividend table must match exactly to the values of the divisor
without any extra values.

SELECT PS1.pilot
FROM PilotSkills AS PS1
LEFT OUTER JOIN
Hangar AS H1
ON PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane ) = (SELECT COUNT(plane) FROM Hangar)
AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);

This says that a pilot must have the same number of certificates as
there planes in the hangar and these certificates all match to a plane
in the hangar, not something else. The "something else" is shown by a
created NULL from the LEFT OUTER JOIN.

Please do not make the mistake of trying to reduce the HAVING clause
with a little algebra to:

HAVING COUNT(PS1.plane ) = COUNT(H1.plane)

because it does not work; it will tell you that the hangar has (n)
planes in it and the pilot is certified for (n) planes, but not that
those two sets of planes are equal to each other.

Note on Performance

The nested EXISTS() predicates version of relational division was made
popular by Chris Date's textbooks, while the author is associated with
popularizing the COUNT(*) version of relational division. The Winter
1996 edition of DB2 ON-LINE MAGAZINE
(http://www.db2mag.com/96011ar:htm) had an article entitled "Powerful
SQL:Beyond the Basics" by Sheryl Larsen which gave the results of
testing both methods. Her conclusion for DB2 was that the nested
EXISTS() version is better when the quotient has less than 25% of the
dividend table's rows and the COUNT(*) version is better when the
quotient is more than 25% of the dividend table.

Jul 23 '05 #3
John

Thanks very much for your response. I think I may not have been quite
clear enough in my question. What I want to query the database for is:
'for every person who bought product 'X' give me a list of all the
other products they have bought?' This is very similar to the Amazon
site when you are about to purchase a book - underneath it says "people
who bought this book also bought these ones...."

Jim

Jul 23 '05 #4
>> 'for every person who bought product 'X' give me a list of all the
other products they have bought? <<

SELECT DISTINCT T1.user_id, T1.product_id
FROM Transactions AS T1
WHERE EXISTS
(SELECT *
FROM Transactions AS T2
WHERE T1.user_id = T2.user_id
AND product_id = 'X');

But I would do it this way to get more meaningful data in the
aggregate:

SELECT T1.product_id, COUNT(*) AS pairing_tally, COUNT(DISTINCT
user_id) AS buyer_tally
FROM Transactions AS T1
WHERE EXISTS
(SELECT *
FROM Transactions AS T2
WHERE T1.user_id = T2.user_id
AND product_id = 'X')
AND T1.product_id <> 'X'
GROUP BY T1.product_id
HAVING COUNT(*) > @my_threshold;

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10230
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline View Query: Select Sq from ( Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
9
3130
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use SUBSTRING(ProductName, 1, CHARINDEX('(', ProductName)-2). I can get this result, but I had to use several views (totally inefficient). I think this can be done in one efficient/fast query, but I can't think of one. In the case that one query is not...
2
17753
by: Amanda | last post by:
From a guy in Microsoft newsgroups: | In *comp.databases.ibm-db2* there are always IBM guys | from the Toronto labs on line.Post with the | -for the love of god please help- | line and I'm sure you'll get their attention. | Their usually very good:) So here's my transplanted post ==========================================
9
4351
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user for any changes of ratings on that day. The resulting data table grows by approx 600 records per...
8
19592
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two fields from a *single* record from a table called 'Locations' and then apply one of these field's values...
1
2765
by: Melissa Kay Beeline | last post by:
OK, here's the sitch : we have an access control system at work that registers ever entry/exit of every employee. I recently made some queries in Access so the ppl in HR could make reports (who came in late, how many ppl were undertimed, etc etc) Now they are not satisfied (of course) and they want a list of ABSENT employees. I have the following tables : PERSONNEL CARD
9
2409
by: hope | last post by:
Hi Access 97 I'm lost on this code please can you help ================================= Below is some simple code that will concatenate a single field's value from multiple records into a single string separated by a user defined character. There is no error trapping (by design), USE AT YOUR OWN RISK.
4
2856
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table query. I'm having trouble doing it. Help! Here is my code so far: Sub OldRegionQuery()
47
2867
by: Jo | last post by:
Hi there, I'm Jo and it's the first time I've posted here. I'm in process of creating a database at work and have come a little unstuck.....I'm a bit of a novice and wondered if anyone could help. I work in a library and send out dual language books to babies of dual or other nationality. The db is to be used for logging a range of book titles and numbers ordered and books sent out to individuals. I am trying to work out a way of...
0
8656
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8845
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7693
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6512
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5848
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4351
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4603
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2304
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1988
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.