Selecting unique books

  • Thread starter Martin A. Weinberger
  • Start date
M

Martin A. Weinberger

Hi all,

I have the following table.

Table: MyTable

IDPrimary IDBook IDPurchaser
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 2
7 2 3
8 3 1
9 3 2
10 3 4

I'd like to perform a query, whereby I find all the book IDs that a specific
purchaser did not make. That is if we are talking about purchaser '4', then
the result should list 2. I'm a bit stumped. I tried several thoughts, but
nothing worked.

Thanks in advance,
 
M

MGFoster

Martin said:
Hi all,

I have the following table.

Table: MyTable

IDPrimary IDBook IDPurchaser
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 2
7 2 3
8 3 1
9 3 2
10 3 4

I'd like to perform a query, whereby I find all the book IDs that a specific
purchaser did not make. That is if we are talking about purchaser '4', then
the result should list 2. I'm a bit stumped. I tried several thoughts, but
nothing worked.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT DISTINCT IDBook
FROM Books
WHERE IDPurchaser <> 4

If the data type of IDPurchaser is Text then should be

WHERE IDPurchaser <> '4'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw/jK4echKqOuFEgEQJciACbBER1IvWgYJt5ghs/IF7TQkCM6OcAmgMc
Ls9kHbF1Uqv7oKanUOtDzBn2
=olar
-----END PGP SIGNATURE-----
 
M

Martin A. Weinberger

Hi,

First off thank you for the response. Unfortunately, it doesn't work. The
code fragment returns all the books, which kind of makes sense. The result
of the query

SELECT * FROM Table Where IDPurchaser <> 4

returns rows 1, 2, 3, 5, 6, 7, 8, 9. From that you added the DISTINCT and
reduced the columns to IDBook, which then returned 1, 2, 3.

Any other thoughts? Thanks in advance,


--
Martin A. Weinberger
ButterflyVista
http://www.butterflyvista.com/
 
J

John Spencer (MVP)

First you need a list of all the books that can be purchased. If you don't have
that in a separate table - which you should then this is more difficult.

Select IDBook
FROM BookTable
WHERE BookTable.IDBook NOT IN
(SELECT IDBook
FROM MyTable
Where IDPurchaser = 4)

Post back if you have to do this all from the one table. It can be done, you
just need to build the "BookTable" as a separate query
SELECT Distinct IDBook
FROM MyTable

And then use that in place of the query
 
M

Martin A. Weinberger

Hi John,

Awesome! It works! Your solution John is really slick. :) I have a lot to
learn. Thanks. I do have 2 tables. I was just simplifying the design for
this post. Thanks again! :)
 
M

Martin A. Weinberger

Hi John/Guys Again,

The code
Select IDBook
FROM BookTable
WHERE BookTable.IDBook NOT IN
(SELECT IDBook
FROM MyTable
Where IDPurchaser = 4)
Click to expand...

works perfectly on Microsoft Access, but fails when I try to execute from
MySQL. I get the following error:


SQL-query :

SELECT *
FROM tblBooks
WHERE tblBooks.IDPrimary NOT
IN (

SELECT IDBook
FROM tblBookData
WHERE IDProfile =1
)
LIMIT 0 , 30

MySQL said:


#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'SELECT IDBook
FROM tblBookData
WHERE IDProfile =1 ) LIMIT

It seems to not like the embedded SELECT. Do any of you have any ideas.
Thanks in advance.
 
D

Douglas J. Steele

What's the "LIMIT 0, 30" stuff? That's nothing I've ever seen in valid SQL.
 
J

John Spencer (MVP)

I don't have any idea. I don't know MYSQL syntax and what it supports/doesn't
support.

You might try dropping the LIMIT 0,30 and see what that does for you.

Also try the subquery by itself and see if it runs or if it generates an error.
 
M

Martin A. Weinberger

Hi Douglas,

That limit stuff got added by the MySQL engine processor, not me. The only
reason that you saw it was that I copied and pasted the resultant code from
the error page. I typed in the code without the limit stuff.
 
Top