Outer Joins?

[webkuh]

In my application this SQL:

SELECT Tags.postId, Tags.term as term FROM Tags
LEFT OUTER JOIN Posts ON Tags.postId=Posts.id 
WHERE Tags.userId=30 AND Tags.term LIKE "label/%"

Produces 97 rows... but this:

SELECT * FROM (SELECT Tags.postId, Tags.term as term 
FROM Tags LEFT OUTER JOIN Posts ON Tags.postId=Posts.id 
WHERE Tags.userId=30 AND Tags.term LIKE "label/%") as allTags 
LEFT OUTER JOIN Tags as newTags ON (newTags.term=allTags.term)

Produces 1935 rows.

I guess I don't understand the Left Outer Join well enough as I thought that it just appends the joining table to the table without adding new rows like some of the other JOIN types. So in the second example I've created a dynamic table aliased as allTags and I then wish to join the original Tags table to my dynamic table and then filter it.

If someone can let me know how to achieve this without adding the extra rows that'd be great!

BTW how does one add multiple OUTER JOINS that all are all based on the data from the first table? ie I don't want Table 3 to be using Table 2 data to make the join I just want it to be joining on Table 1 as well. Am confusing myself now....

[r937]

the best way to understand query behaviour is to restrict the queries to only a few rows, and inspect the query results carefully

97 is maybe a bit too many for careful inspection, but it can be done

add an ORDER BY to your second query

here's what i think is happeneing --

your first query retrieves rows only for userid=30 tags which have a label

in your second query, you are joining each row to every other tag with the same term, whether it is for userid=30 or not

[webkuh]

So LEFT OUTER JOIN does add new rows? :-( I thought the WHERE clause in brackets would run /filter the results BEFORE the JOIN on the dynamic table occurs?

[r937]

every join has the potential to add new rows

it's all about da one-to-many relationships

would you agree that a tag called 'label/foo' belonging to one user will join to all tags called 'label/foo' belonging to other users?

[webkuh]

Tags can be associated with Posts and Feeds. When a tag like 'label/foo' is associated with a Feed in the DB, all posts belonging to that Feed can also have the tag 'label/foo' - but I want to ignore these tags. That's really what I'm trying to achieve but don't know the best way...

If a tag is associated with a feed then there will be a row in the Tags table that has the label/foo value for 'term' column but an empty value for Tags.postId.

If any of the above makes sense can you suggest how to achieve this with 1 SQL statement?

Many thanks

Nick

[r937]

no, sorry, doesn't make sense to me, i don't understand your tables or how you're using them

why don't we go back to the first query and analyze what it's doing, why it works, and where you want to go next

[webkuh]

Ok, here's a sample screengrab which may help me to explain:




This is some sample data from the Tags table. You see how each row in the table represents a tag. All items are tagged to 'Air Blogs'. In the sample I can tell that the LAST 3 tags/rows are actual Feeds that have been tagged to appear in the Air Blogs folder - I know that they are feeds because they have no postId. You see how the first 4 items have postIds? Those are posts that have been tagged to appear in the Air Blogs folder too.

BUT

in my SQL statement I want to ignore rows 4,5,6,7. Rows 5,6,7 are feeds so that's easy enough. But the reason I want to ignore row 4 (which is a post) is because it's feedId - feed/http%3A//feeds.feedburner.com/parkerkhoyt - matches the feedId of a Feed in the 'Air Blogs' folder.

So my SQL should just return rows 1,2,3 from this sample.

Hope that makes more sense?

Thanks again

Nick

However, I want my SQL statement to discard


But when I display them I know that each of those 4 posts belongs to an RSS feed within the 'Air Blogs' folder. So in fact they should be igno

[r937]

i still don't understand your data -- that diagram didn't help, you say that's the tag table, but i am more used to a tag table consisting of only an id and a name

let's start with changing your joins from LEFT OUTER JOINs to INNER JOINs

any improvement?

p.s. please always show your latest query if you want me to analyze it

[webkuh]

I solved what I was trying to do eventually with the following SQL - am sure this could be optimized further but it does what I'm after:

SELECT COUNT(*) as unread, term FROM 
(SELECT labelTags.feedId as feedId, labelTags.postId as postId, 
labelTags.term as term, labelTags.label as label, readTags.term as readTerm 
FROM (SELECT allTags.feedId as feedId, allTags.postId as postId, 
allTags.term as term, allTags.label as label FROM (SELECT * FROM Tags 
WHERE userId=30 AND term LIKE "label/%" AND postId!="") as allTags 
LEFT OUTER JOIN (SELECT * FROM Tags WHERE userId=30 AND 
postId="" AND term LIKE "label/%") as folderTags ON 
(allTags.feedId=folderTags.feedId AND allTags.term=folderTags.term) 
WHERE folderTags.id IS NULL ORDER BY label) as labelTags
LEFT OUTER JOIN Tags as readTags ON labelTags.postId=readTags.postId 
AND (readTags.term="state/read" OR readTags.term IS NULL) 
WHERE readTerm IS NULL) GROUP BY term