Problem with SQL Statment

Posted on 2009-12-28
Last Modified: 2013-11-05
Hi, in the following SQL statenment if the [key] and [value] columns are null  the sql statenment will not retrieve its corresponding row(s), how can I modify it to make it retrieves the row even if these valus are null.
SELECT IEI.[Key], IEI.Value ,II.CSS_Class, II.Control_Type, II.ID as ICF_ID

	FROM Item_Extra_Info AS IEI

	LEFT JOIN Item_Category_Form AS II ON II.ID = IEI.Item_Category_Form_FK 

	WHERE IEI.Item_Category_Form_FK  



		FROM Item_Category_Form AS ICF

		LEFT JOIN Item_Category AS IC ON ICF.Item_Category_FK = IC.ID

		LEFT JOIN Item AS I ON I.Item_Category_FK = IC.ID

		WHERE  I.ID = IEI.Item_FK AND I.ID = 2)

Open in new window

Question by:Abdu_Allah
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    as the "key" and "value" information are not used anywhere in the conditions, the fact that those values are NULL will not influence the results.

    so, either you are missing something in the query/data, or your application/code that you use to display/export the records "skips" those because it "fails" on the null values.

    please double-check
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    it seems fine to me too..
    LVL 3

    Author Comment

    Well, in another words, can I retreive the the other corresponding rows with with [value] and [key] null?
    LVL 31

    Assisted Solution

    Can you post some sample data and the expected output?
    LVL 59

    Accepted Solution

    Since the "key" and "value" columns are the only ones coming from the first table in the query, I would suspect what you are seeing is not just that they are NULL but there is no row for that table.  If you truly want to see the other data even when there is no match in the first table then you need to either switch the order of the tables or use a RIGHT JOIN.

    SELECT IEI.[Key], IEI.Value ,II.CSS_Class, II.Control_Type, II.ID as ICF_ID
    FROM Item_Extra_Info AS IEI
    RIGHT JOIN Item_Category_Form AS II ON II.ID = IEI.Item_Category_Form_FK
       FROM Item_Category_Form AS ICF
       INNER JOIN Item_Category AS IC ON ICF.Item_Category_FK = IC.ID   INNER JOIN Item AS I ON I.Item_Category_FK = IC.ID   WHERE  I.ID = 2

    I made some alterations also as follows:
    +II.ID in the WHERE clause other-wise you are in essence doing an INNER JOIN as IEI row would have to exist to fit the WHERE...IN criteria.  Since JOIN shows that II.ID = IEI.Item_Category_Form_FK, my change should be equivalent.
    +LEFT JOIN's in the sub query made no sense as you are ultimately filtering on the Item (I) table and so the result is an INNER JOIN.  
    +Additionally, since IEI is no longer the focal point I took a chance removing the correlation to IEI.Item_FK hoping since there is an explicit ID = 2 that the proper results are met anyway.

    Hope this helps at least give you an idea what kinds of things would need to change if this is not the final query you need as it stands.

    Best regards and happy coding,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Microsoft sql server 17 30
    Not selecting duplicate data 6 22
    Output query to a text file 20 68
    SQL Round a percentage 2 15
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now