[Last Call] Learn how to a build a cloud-first strategyRegister Now


Problem with SQL Statment

Posted on 2009-12-28
Medium Priority
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 26132629
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
ID: 26132661
it seems fine to me too..

Author Comment

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

Assisted Solution

awking00 earned 200 total points
ID: 26133137
Can you post some sample data and the expected output?
LVL 60

Accepted Solution

Kevin Cross earned 1200 total points
ID: 26135103
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,


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 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