Boolean query from several tables?

[Anon]

Hi,
I'm trying to create boolean query that would be easiest to explain with an example:
I have 3 tables as following:
table songs ( int pri_key song_id , int artist_id, varchar name )
table artists ( int pri_key artist_id, varchar name )
table albums ( int pri_key album_id, int artist_id, varchar name )

(This is a simplified version of the tables, primary keys marked with pri_key)

Query "alice cooper AND hey stoopid" should then search for that boolean query from ALL TABLES, comparing to "name" keys, in this case it would find "alice cooper" from artists.name, and hey stoopid from songs.name. Mysql_query should then return the song_id.

Searching that boolean query from a single table at a time is not a problem, but this multiple tables is.

this seems to me mostly MySQL / SQL problem, not php problem, but as i'm writing the code in PHP I was hoping someone would be kind enough to give me some pointers.

And I tried to read (and understand) about Joins from SQL tutorial at http://w3.one.net/~jhoffman/sqltut.htm, but i really didn't get it.. tried also www.sqlcourse2.com with no luck.

Has anyone a good example on this one?

[[deleted]]

in MYSQL, Joins are frightningly simple, and yet amazingly complex.

A normal select would be
SELECT * FROM table WHERE this=that

If you want to select from two tables, you can simply put:

SELECT * FROM table1,table2 WHERE this=that

But, what if "this" is a column in both tables?
So you have to specify which table you want "this" from:

SELECT * FROM table1,table2 WHERE table1.this=that

Now there is just one more problem;
when you JOIN tables, they are merged.
That means that if you only specify one WHERE clause for one column in one table, all the records from the other table are a match by default, just like SELECT * FROM table without any WHERE clause.

So this:
SELECT * FROM table1,table2 WHERE table1.this=that

will give all records in table2, for each record in table1 where this=that.

This is why tables in JOIN statements are usually "linked" like this:
SELECT *
FROM table1,table2
WHERE table1.id=table2.id
AND table1.this=that

Now the 'id' fields in both tables must have the same values AND table1.this must be 'that'
taht means that if there is only one record in table1 where this=that, the resultset will only contain those records of table2 that have the same id as table1 in the record where this=that.

hope you understand is a bit better now.

[Anon]

Just an exampe how I would have done it. The first, I would have added one more column to songs table - album_id, otherwise it is impossible to understand, to which album song belongs.
So, you will have:
table songs ( int pri_key song_id , int artist_id, varchar name, album_id )
table artists ( int pri_key artist_id, varchar name )
table albums ( int pri_key album_id, int artist_id, varchar name )

The select statement is:

select s.song_id from songs as s
left join artists on artists.name like "alice cooper"
and s.artist_id=artist_id
left join albums on albums.name like "hey stoopid"
and s.album_id=albums.album_id
left join songs on songs.name like "hey stoopid";

Or have I forgotten something? The only things I have douts about is the last "LEFT JOIN" - should not you also write "and s.songs_id=songs.songs_id"? I have already done such queries but don't remember.
This query will return all song_id's for albums with certain name and for artist with secratin name and also with certain song names.

[Anon]

Yeah, thanks. these helped quite a bit. it seems that I need outer Joins? (Haven't been able to find much info about them)

I made it work with 2 tables, but adding more is a pain in the a**...

One thing to add to your example, Annchen, it seems for me that that would only check "alice cooper" from artists, and "hey stoopid" from albums and songs that have alice cooper as artist, right?
-- what I need is that the query would check if "alice cooper" is found in one of the (artists,songs,albums) AND "hey stoopid" is found in those of the (artists, songs, albums) where "alice cooper" is found..

but thanks for this help, I've got some ideas now :o)