SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    question on join in sql

    Not sure if this is specifically a join question or maybe it's a sub query. Here's the query I'm currently using:

    SELECT
    tbl45.col1,
    tbl45.col4
    FROM tbl45 LEFT JOIN tbl3 ON tbl45.col3 = tbl3.col1
    WHERE ((tbl45.col1)<>31) AND (tbl45.col2)=(SELECT col2 FROM tbl45 WHERE col1=31)) AND (tbl3.col22)=0) AND (tbl45.col41)=0));

    When there are no matching records in tbl3 I'd like it to still show the rest of the data. But with the tbl3.col22=0 it won't. Is there a way around this?

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try changing

    Code:
     WHERE ((tbl45.col1)<>31) AND (tbl45.col2)=(SELECT col2 FROM tbl45 WHERE col1=31)) AND (tbl3.col22)=0) AND (tbl45.col41)=0));
    into
    Code:
     WHERE 
    (
        tbl45.col1<>31
       AND 
     	tbl45.col2= (SELECT col2 FROM tbl45 WHERE col1=31)
    
     
     
        AND 
     	 tbl45.col41=0
     )
    If you don't want the data limited by "Where tbl3.col22=0" then just remove that expression...

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do want it to limit the data using tbl3.col22=0 but only when there is a linked record in this table. Problem is for example, if tbl3 was completely empty of records, nothing shows up. I need data to show up for records from tbl45 even if there's no records in tbl3. This isn't happening though.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that can't possibly be your current query because it has several unbalanced parentheses

    in order to get the full effect of a LEFT OUTER JOIN, you cannot have any conditions on columns from the right table in the WHERE clause

    these conditions must be moved into the ON condition of the join
    Code:
    select tbl45.col1
         , tbl45.col4
      from tbl45 
    left outer
      join tbl3 
        on tbl45.col3 
         = tbl3.col1
       and tbl3.col22 = 0
     where tbl45.col1 <> 31
       and tbl45.col2 =
           ( select col2 
               from tbl45 
              where col1=31 )  
       and tbl45.col41 = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •