combine three tables in a join

[hbla]

Hi all,

 

I got a nasty query that is unwilling to work. I want to combine info from three tables and set a WHERE. I need a list with results that appear in table1 and table 2. The third table should give a condition.

 

I combined table1 and table2 with an INNER JOIN. Works fine. But when I add the third table with an INNER JOIN also and set the condition (WHERE RESULT != $somestring), I get duplicate results except for the condition.

 

It looks like this

$sql = "SELECT table1.title
FROM table1 INNER JOIN table2
ON table1.title = table2.title
INNER JOIN table3
ON table3.title = table2.title
WHERE (table3.user != $someuser)
ORDER BY title ASC
";

 

Seems to simple... When I add a DISTINCT to the query, I get single results but also the rows that should be excluded by the condition.

 

I use MYSQL 5.0.32. Tried about any kind of JOIN-query I can think of. Must be a silly mistake.

 

[kickstart]

Hi

 

Are the JOINed fields unique in all three tables?

 

All the best

 

Keith

[hbla]

Keith

thnx for reply

The tables 1 and 2 contain unique values for "title".

Some background: my site (www.infoclan.nl) is being build for users who want to upload their rss-feeds from a reader (in an opml-file). These seperate feeds with a "title" are stored in a main table (which is table3). From this main table I construct two other tables: table1 has all rss-feeds unique, table2 has the most popular rss-feeds.

What I try to code is a query that selects the feeds (by title) that are the most popular but are not mentioned by a certain user (the one who is logged in on the site).

Does this makes sense?

 

ciao from the netherlands

[kickstart]

Hi

 

Not sure I quite understand. Does table 3 list all of each users favourite feeds? So giving duplicates feeds on table 3?

 

If so WHERE (table3.user != $someuser) would still result in duplicates. If you had 10 users all with the same feeds (for example) there would be 10 duplicates, and that where clause would only exclude one of them.

 

Not sure you need table 1 at all (unless there is info on table 1 that isn't on table 2).

 

Think I would try:-

 

SELECT table2.*

FROM table2

LEFT OUTER JOIN table3

ON table2.title = table3.title

AND table3.user = '$loggedInUser'

WHERE table3.title IS NULL

 

All the best

 

Keith

[hbla]

Keith

thnx again

I can see your point: I'm not sure that I need table1 (the unique feeds) either. I created the table just to have it available, but actually I think it should be possible to create it on the fly from table3 (all the feeds).

I tried your code, but it doesn't give any results. I might have to setup the table structure again, and simplify it a little, which is a pain :-(

Any other ideas?

Henk

[hbla]

Keith

made a mistake. It works. I'll have to manually check the result array, but hey, you helped me a lot it seems.

What by the way would be the right way to avoid that extra table and create a table with unique feeds on the fly. Point is that this mastertable (all the feeds) holds a lot more info right now about thousands of feeds.

Many thanks

Henk

[kickstart]

Hi

 

Think I would have one table of feeds, with an auto number unique key. They another table of favourites, with a column for the user whose favourite it is and a column for the unique key of the first table.

 

All the best

 

Keith