SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Outer Joins?

  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Outer Joins?

    In my application this SQL:

    Code:
    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:

    Code:
    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....

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •