Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    47

    Red face Unanswered: Help with LEFT JOIN function

    I am trying to use the Left join command and having all sorts of problem.
    I have 16 queries and each query has two columns in it the first column in each query is the "Index" which has similar vlaues but they are not in the same order and are not present in each query like "Ind-1" might be in query 1 but not in query 5 and so on and so forth
    I wanna join the second column of all queries based on the index and where the particualt index value is not present I want it to display blank. So the output would look ilike

    INDEX--Q1--Q2--Q3--Q4--Q5.................
    Ind-1 -- 6 -- 5 -- 1 -- 2 -- 3 .................
    Ind-2 -- blk-- 3 --blk--5 -- 6 ..............
    blk=blanks for values not found for the particular index in the Query

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a table with all the index values in them? that would be your left table in the LEFT OUTER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    47

    further explaination

    Yes i have the table with all the index values in it.
    I s there a limit to how many columns you can put in horizontal arrangment
    this is the code i wrote I am using Access 2003
    Concat_Id is the Table with all indexes
    CONCAT is the Index column which is named similar in all 16 tables
    SRHWND_X are the tables from which columns have to retrieved
    the join criteria is CONCAT=CONCAT
    [0_3] and [2_3] are the columns that are to be pulled out and put alongside the Index (look at my orig question fo Description)
    this is the code I have form some reason it does not go over two tables is there a Synatax error somewhere.

    SELECT Concat_ID.CONCAT, SRHWND_0.[0_2], SRHWND_1.[2_3]
    FROM (Concat_ID LEFT JOIN SRHWND_0 ON Concat_ID.CONCAT = SRHWND_0.CONCAT) LEFT JOIN SRHWND_1 ON Concat_ID.CONCAT = SRHWND_1.CONCAT;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure that Concat_ID is the name of the table and CONCAT is the name of the column?

    i would've guessed it would be the other way around

    does this work?
    Code:
    SELECT CONCAT.Concat_ID
         , SRHWND_0.[0_2]
         , SRHWND_1.[2_3]
      FROM (
           CONCAT 
    LEFT 
      JOIN SRHWND_0 
        ON SRHWND_0.CONCAT = CONCAT.Concat_ID 
           ) 
    LEFT 
      JOIN SRHWND_1 
        ON SRHWND_1.CONCAT = CONCAT.Concat_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    47

    Works only for 2 columns

    I had the code i wrote working but it works only for two columns
    as sson as i add a third column to the code
    SRHWND_2 and try to use the same code it messe up
    missing operator, FROM cluase error alll sorts of errors come up.

    First Try:
    SELECT Concat_ID.CONCAT, SRHWND_0.[0_2], SRHWND_1.[2_3],
    SRHWND_2.[3_4]
    FROM (Concat_ID LEFT JOIN SRHWND_0 ON Concat_ID.CONCAT = SRHWND_0.CONCAT) LEFT JOIN SRHWND_1 ON Concat_ID.CONCAT = SRHWND_1.CONCAT LEFT JOIN SRHWND_2 ON Concat_ID.CONCAT = SRHWND_2.CONCAT;

    Second Try:
    SELECT Concat_ID.CONCAT,SRHWND_0.[0_2],SRHWND_1.[2_3],
    SRHWND_2.[3_4]
    FROM (Concat_ID LEFT JOIN SRHWND_0 ON Concat_ID.CONCAT = SRHWND_0.CONCAT) LEFT JOIN SRHWND_1 ON Concat_ID.CONCAT = SRHWND_1.CONCAT, Concat_ID LEFT JOIN SRHWND_2 ON Concat_ID.CONCAT = SRHWND_2.CONCAT;

    May be tis the way iam adding the third column to it I dont if i need to put in parenthesis or comma or " ' just woldnt work

  6. #6
    Join Date
    Feb 2009
    Posts
    47

    CONCAT exp

    sorry forgot to mention Concat_ID is the query and CONCAT is the column in the query that has all the indexes and same name is given to the column in all the other 16 queries.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wait a sec... Concat_ID is a query?

    i asked if you had a table that had all the values

    what does the SQL for Concat_ID actually look like?

    also, the reason your second try didn't work is because you forgot another level of nested parentheses

    watch carefully --
    Code:
    SELECT CONCAT.Concat_ID
         , SRHWND_0.[0_2]
         , SRHWND_1.[2_3]
         , SRHWND_2.[3_4]
      FROM ((
           CONCAT 
    LEFT 
      JOIN SRHWND_0 
        ON SRHWND_0.CONCAT = CONCAT.Concat_ID 
           ) 
    LEFT 
      JOIN SRHWND_1 
        ON SRHWND_1.CONCAT = CONCAT.Concat_ID
           ) 
    LEFT 
      JOIN SRHWND_2 
        ON SRHWND_2.CONCAT = CONCAT.Concat_ID
    please let me know if this works
    Last edited by r937; 03-10-09 at 16:09.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2009
    Posts
    47

    New Error..just not my day i guess

    Now Access is sayin that "System Resource Exceeded"
    I do have a table but the coding I have doe up till now was very complex the data had to be broken down using differernt queries and firleds had to be deleted. This is what the SQl for "Concat_ID" looks like

    SELECT DISTINCT PSMM_NPhase.CONCAT
    FROM PSMM_NPhase
    ORDER BY PSMM_NPhase.CONCAT;

    It looks at a different table all lists all possible Indexes(distinct Indexes nothing id repeated)

Posting Permissions

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