join problem

Anon

I'm trying to join two tables.

Table1 looks like:
id
name
state

Table2 looks like:
fk_id
book_name

I need to answer a question like: find everyone who lives in Virginia who has read 'Harry Potter' AND 'Gone With the Wind'.

select * from table1 where table1.id=table2.fk_id and book_name='Harry Potter' AND book_name='Gone With the Wind' does not work.

I'm sure there is a simple and obvious way to do this but I can't seem to figure it out myself or find anything in the forums or SQL tutorials, etc. Thanks in advance for any help you can provide.

Anon

Shaun wrote:

I'm trying to join two tables.

Table1 looks like:
id
name
state

Table2 looks like:
fk_id
book_name

select * from table1 left join table 2 on table1.id=tabl2.fk_id where table1.state='virginia' and table2.book_name='Harry Potter' or table2.book_name='Gone with the wind'

this might work better in a different order, depending what your indices are. I've been learning about this myself recently. You'll note that it is a 'or' instead of 'and'. I'm not sure you'll get anything with and, but you you can try it and see what happens. mySQL doesn't support union queries, but you didn't say which DB you're using. hench, You may have to do this with two queries - one into a temp table, then the second.

its a start anyway - not the answer, but a best guess for 5:30 am!

spxdcz

A basic solution is to alias the book table to different names. However, you'd then have to introduce a different alias for each 'book' you want to AND. For example, for 2 books (virginia AND harry potter AND gone with the wind):

SELECT DISTINCT person.id, person.name
FROM
table1 AS person,
table2 AS book1,
table2 AS book2
WHERE
person.state = 'virginia' AND
person.id = book1.fk_id AND
person.id = book2.fk_id AND
book1.book_name = 'Harry Potter' AND
book2.book_name = 'Gone with the Wind'

Not a pretty solution, and you'd have to introduce some LEFT JOINS if you didn't want only exact matches, but it's a start for you...

Anon

Thanks for the help. Using the multiple table aliases seems to give the correct result. Will this query be very innefficient with large numbers of books (like 15 for example)? If so, do you think there would be a better way to organize the information to answer such queries? I'm using MySQL by the way.

spxdcz

Yes, the query will start to get slow quite quickly as the books increase - MySQL isn't particularly good at joining a table to itself (which is what the aliasing solution does).

What may be better is for you to pass most of the 'processing' or 'matching' logic over to PHP, and just use MySQL to get the basic information. For example, you could probably get similar results through two, quick Selects. Firstly, you could select the id numbers of all the people in a certain state:

SELECT DISTINCT id FROM table1 WHERE state = 'virginia'

Then, get a list of fk_ids from table 2 that have rented out any of the books, e.g.

SELECT fk_id FROM table2 WHERE book_name IN ('Harry Potter', 'Gone with the wind', 'any other book title', 'etc')

You could then use PHP to:

find out how many times each fk_id appears in the second set of results. If an fk_id appears the same number of times as the number of books, then that person has read all the specified books.
You can then 'filter' or 'narrow down' this list by checking these people against the ids you have in the first set of results. If they are also in these results, they match all of the requirements (i.e. they have read all the books and live in the correct state).

Is that a valid solution, or do you need a more heavily MySQL-based answer?

[deleted]

Another method is to only join table2 to table1 once, and use grouping to find the people who have read the books.

The basic idea is that you do a join,
thus forcing every user to match with all of the 'books read' once.
Then you can use SUM() and CASE to get the data you want, like this:

SELECT id,name,
SUM(CASE book_name
WHEN 'harry potter'
THEN 1
ELSE 0
END) as harry,

SUM(CASE book_name
WHEN 'Gone with the wind'
THEN 1
ELSE 0
END) as gone,

FROM table1, table2
WHERE table1.id = table2.id
AND ((book_name = 'harry potter')
OR (book_name = 'Gone with the wind'))
GROUP BY id