473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inner Join experts out there??

The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it looks
like an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,
UNLESS they've ordered product #14 in which case I don't want to know about
that customer at all.

Any help would be greatly appreciated! I'll watch the newsgroup for the
answer - hopefully your response can help someone else too. However, if you
prefer to email me directly, you can send it to me at bunchah at yahoo dot
com.

Thanks in advance!

(if it'll help, I'll buy the person offering the correct solution a beer -
pending age verification of course) ;)

-Al


Jul 20 '05 #1
11 2874
On Sat, 22 Nov 2003 00:52:35 GMT, "news-east.earthlink.net"
<ab******************@yahoo.nospam.com> wrote:
The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it looks
like an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,
UNLESS they've ordered product #14 in which case I don't want to know about
that customer at all.


A literal translation could be:

SELECT DISTINCT CustomerID
FROM CustomerTable
WHERE CustomerID IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 1)
AND CustomerID NOT IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 14)

Or:

SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 1
MINUS
SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 14

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 20 '05 #2
select ct.customerid
from CustomerTable ct
join OrderTable ot
on ct.orderid = ot.orderid
group by ct.customerid
having (count(case ot.productid when 1 then 1 else null end) > 0)
and (count(case ot.productid when 14 then 1 else null end) = 0)
order by ct.customerid
HTH,
Dave

"news-east.earthlink.net" <ab******************@yahoo.nospam.com> wrote in
message news:nr******************@newsread2.news.atl.earth link.net...
The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it looks like an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1, UNLESS they've ordered product #14 in which case I don't want to know about that customer at all.

Any help would be greatly appreciated! I'll watch the newsgroup for the
answer - hopefully your response can help someone else too. However, if you prefer to email me directly, you can send it to me at bunchah at yahoo dot
com.

Thanks in advance!

(if it'll help, I'll buy the person offering the correct solution a beer -
pending age verification of course) ;)

-Al

Jul 20 '05 #3
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:t5********************************@4ax.com...
On Sat, 22 Nov 2003 00:52:35 GMT, "news-east.earthlink.net"
<ab******************@yahoo.nospam.com> wrote:
The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it lookslike an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,UNLESS they've ordered product #14 in which case I don't want to know aboutthat customer at all.
A literal translation could be:

SELECT DISTINCT CustomerID
FROM CustomerTable
WHERE CustomerID IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 1)
AND CustomerID NOT IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 14)

Or:

SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 1
MINUS
SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 14


Andy, I don't think the second solution will work. First of all, MINUS is
not supported on SQL Server. Second, even if this is run on Oracle, the two
result sets you're performing the minus on are the joined tables, not the
single table CustomerTable. So the two sets are disjoint because ProductID
cannot be 1 and 14 simultaneously. So you'll end up with all the customers
who have ordered product #1, regardless of whether they have ordered product
#14 or not.

Since there's a beer involved here, I have to be a bit particular... ;-)

- Dave


--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Jul 20 '05 #4
"Dave Hau" <nospam_dave_nospam_123@nospam_netscape_nospam.net _nospam> wrote
in message news:r%*******************@newssvr27.news.prodigy. com...
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:t5********************************@4ax.com...
On Sat, 22 Nov 2003 00:52:35 GMT, "news-east.earthlink.net"
<ab******************@yahoo.nospam.com> wrote:
The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it lookslike an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,UNLESS they've ordered product #14 in which case I don't want to know aboutthat customer at all.
A literal translation could be:

SELECT DISTINCT CustomerID
FROM CustomerTable
WHERE CustomerID IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 1)
AND CustomerID NOT IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 14)

Or:

SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 1
MINUS
SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 14


Andy, I don't think the second solution will work. First of all, MINUS is
not supported on SQL Server. Second, even if this is run on Oracle, the

two result sets you're performing the minus on are the joined tables, not the
single table CustomerTable. So the two sets are disjoint because ProductID cannot be 1 and 14 simultaneously. So you'll end up with all the customers who have ordered product #1, regardless of whether they have ordered product #14 or not.
Sorry didn't notice you're selecting only CustomerID instead of *. It does
work.

My bad. You won the beer. :)

- Dave

Since there's a beer involved here, I have to be a bit particular... ;-)

- Dave


--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)


Jul 20 '05 #5
SELECT DISTINCT cart_id
FROM OrderTable
WHERE cart_id
IN (
SELECT cart_id
FROM CartTable
WHERE product_index =1 ) AND cart_id NOT
IN (
SELECT cart_id
FROM CartTable
WHERE product_index =14 )

After I switched the fields and table names out to match the real table
names ( they were confusing the issue a bit) I tried the quer(y/ies) and I
get errors on both.

Maybe I should clarify - this is running in a MySQL database server - though
with straight SQL I would think this wouldn't matter much, no?

I moved the structure of the actual tables to a database on my home server
and punched a hole in my firewall to allow you to look at this first hand -
mind you, there are only a few records and they're made up, but they should
be sufficient enough to let you tinker...

surf to http://maple.homelinux.com/phpmyadmin/index.php
account : aaron
pass: pass123

I'll leave it up and running this evening - the account has limited rights
(to that database only)...

take a gander?

BTW - for the speedy response, I'll buy both of you guys a beer...name your
poison!
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:t5********************************@4ax.com...
On Sat, 22 Nov 2003 00:52:35 GMT, "news-east.earthlink.net"
<ab******************@yahoo.nospam.com> wrote:
The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it lookslike an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,UNLESS they've ordered product #14 in which case I don't want to know aboutthat customer at all.


A literal translation could be:

SELECT DISTINCT CustomerID
FROM CustomerTable
WHERE CustomerID IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 1)
AND CustomerID NOT IN (SELECT CustomerID
FROM OrderTable
WHERE ProductID = 14)

Or:

SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 1
MINUS
SELECT CustomerID
FROM CustomerTable
INNER JOIN OrderTable USING (OrderID)
WHERE ProductID = 14

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Jul 20 '05 #6
On Sat, 22 Nov 2003 01:50:17 GMT, "news-east.earthlink.net"
<ab******************@yahoo.nospam.com> wrote:
Newsgroups: alt.php.sql,comp.databases.ms-sqlserver,microsoft.public.sqlserver.datamining

SELECT DISTINCT cart_id
FROM OrderTable
WHERE cart_id
IN (
SELECT cart_id
FROM CartTable
WHERE product_index =1 ) AND cart_id NOT
IN (
SELECT cart_id
FROM CartTable
WHERE product_index =14 )

After I switched the fields and table names out to match the real table
names ( they were confusing the issue a bit) I tried the quer(y/ies) and I
get errors on both.
What errors?
Maybe I should clarify - this is running in a MySQL database server - though
with straight SQL I would think this wouldn't matter much, no?
It makes a big difference - MySQL has large gaps in its SQL syntax - in
particular, no subqueries or set operations (i.e. MINUS).

(Subqueries are going into the alpha 4.1 version, I think UNION went into 4.0
at some point, don't know about MINUS).
I moved the structure of the actual tables to a database on my home server
and punched a hole in my firewall to allow you to look at this first hand -
mind you, there are only a few records and they're made up, but they should
be sufficient enough to let you tinker...

surf to http://maple.homelinux.com/phpmyadmin/index.php
account : aaron
pass: pass123


404 Object not found.

A variation on Dave's query to account for MySQL's limitations and quirks
(identifiers case-sensitive, cannot reference aggregates in HAVING clauses only
their aliases, JOIN must be INNER JOIN) comes up with:

select ct.CustomerID,
count(case ot.ProductID when 1 then 1 else null end) num_1,
count(case ot.ProductID when 14 then 1 else null end) num_14
from CustomerTable ct
inner join OrderTable ot
on (ct.OrderID = ot.OrderID)
group by ct.CustomerID
having num_1 > 0
and num_14 = 0
order by ct.CustomerID

This works against MySQL 3.x, 'cos I just ran it.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 20 '05 #7
On Sat, 22 Nov 2003 02:06:15 +0000, Andy Hassall <an**@andyh.co.uk> wrote:
A variation on Dave's query to account for MySQL's limitations and quirks
[...] cannot reference aggregates in HAVING clauses only
their aliases, [...]


OK, that's wrong, looks like I was thinking of something else. So going closer
back to Dave's query:

select DISTINCT ct.CustomerID
from CustomerTable ct
inner join OrderTable ot
on (ct.OrderID = ot.OrderID)
group by ct.CustomerID
having count(case ot.ProductID when 1 then 1 else null end) > 0
and count(case ot.ProductID when 14 then 1 else null end) = 0
order by ct.CustomerID;

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 20 '05 #8
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:du********************************@4ax.com...
On Sat, 22 Nov 2003 02:06:15 +0000, Andy Hassall <an**@andyh.co.uk> wrote:
A variation on Dave's query to account for MySQL's limitations and quirks[...] cannot reference aggregates in HAVING clauses only
their aliases, [...]
OK, that's wrong, looks like I was thinking of something else. So going

closer back to Dave's query:

select DISTINCT ct.CustomerID
from CustomerTable ct
inner join OrderTable ot
on (ct.OrderID = ot.OrderID)
group by ct.CustomerID
having count(case ot.ProductID when 1 then 1 else null end) > 0
and count(case ot.ProductID when 14 then 1 else null end) = 0
order by ct.CustomerID;
IMHO, no need for the DISTINCT. The "group by ct.CustomerID" will always
give you distinct values of CustomerID.

- Dave


--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Jul 20 '05 #9
On Sat, 22 Nov 2003 02:23:14 GMT, "Dave Hau"
<nospam_dave_nospam_123@nospam_netscape_nospam.net _nospam> wrote:
select DISTINCT ct.CustomerID [snip] group by ct.CustomerID
[snip]
IMHO, no need for the DISTINCT. The "group by ct.CustomerID" will always
give you distinct values of CustomerID.


Ah, yes - that's true.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 20 '05 #10
select DISTINCT ct.cart_id
from OrderTable ct
inner join CartTable ot
on (ct.cart_id = ot.cart_id)
group by ct.cart_id
having count(case ot.product_index when 1 then 1 else null end) > 0
and count(case ot.product_index when 14 then 1 else null end) = 0
order by ct.cart_id;

Thanks Andy, that worked PERFECTLY!
Ok, now what kind of beer do you drink? (or wine?)

Dave - thanks to you too - it's nice to know I'm not the only one working on
a Friday night. :)

-Al

"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:du********************************@4ax.com...
On Sat, 22 Nov 2003 02:06:15 +0000, Andy Hassall <an**@andyh.co.uk> wrote:
A variation on Dave's query to account for MySQL's limitations and quirks[...] cannot reference aggregates in HAVING clauses only
their aliases, [...]
OK, that's wrong, looks like I was thinking of something else. So going

closer back to Dave's query:

select DISTINCT ct.CustomerID
from CustomerTable ct
inner join OrderTable ot
on (ct.OrderID = ot.OrderID)
group by ct.CustomerID
having count(case ot.ProductID when 1 then 1 else null end) > 0
and count(case ot.ProductID when 14 then 1 else null end) = 0
order by ct.CustomerID;

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Jul 20 '05 #11
"news-east.earthlink.net" <ab******************@yahoo.nospam.com> wrote in message news:<nr******************@newsread2.news.atl.eart hlink.net>...
The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it looks
like an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,
UNLESS they've ordered product #14 in which case I don't want to know about
that customer at all.

Any help would be greatly appreciated! I'll watch the newsgroup for the
answer - hopefully your response can help someone else too. However, if you
prefer to email me directly, you can send it to me at bunchah at yahoo dot
com.

Thanks in advance!

(if it'll help, I'll buy the person offering the correct solution a beer -
pending age verification of course) ;)

-Al


Hi ,
Here is my reply

select customertable.* c1 from customertable inner join ordertable o1
on c1.orderid = o1.orderid where c1.orderid = 1 and o1.orderid <> 14

With Thanks
Raghuraman
Jul 20 '05 #12

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

Similar topics

3
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set indexes in my tables. My query requires four...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
3
by: mheydman | last post by:
I apologize if this has been asked before- I searched google but could not find a concrete answer. I recently inherited a database whose t-sql code is written in a format that I find difficult...
4
by: Nathan | last post by:
I have an application that uses an Access database to gather information on students' test scores. In the database there are three tables which are joined by one- to-many relationships: ...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
4
exoskeleton
by: exoskeleton | last post by:
hi dear experts im here once again...i have a problem on showing the result when im using inner join...here's my code.. $sql_trans_pro_tbl="SELECT...
12
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
3
by: Anila | last post by:
Hi Friends, My problem with Inner join is ... first i joined two tables and i got the result. after that iam trying to join one more table its giving syn tax error in JOIN condition. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.