By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,612 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

HELP with MS-SQL

P: 3
What I need is an SQL query that joins three tables. TableA and TableB are joined on Column1 which is common to both. Rows are filtered by Column2 in TableA equalling a specific value SpecificValue1. TableB and TableC are joined by Column3 which exists in both tables. However, I need all rows from TableB regardless of whether or not there is a corresponding row in TableC.

Up to there has been no problem. However, the monkey wrench is that from TableC I only want values if Column4 in TableC is a specific value SpecificValue2, otherwise I want it to return a Null value for that row.

Here is what I have and it does not produce the Nulls I am looking for. It only produces rows if TableC Column3's specific value exists for that particular row.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT TableA.Column1, TableA.Column2, TableB.Column3, TableC.Column4, TableA Column5
  3. FROM ((TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1) LEFT OUTER JOIN TableC ON TableB.Column3 = TableC.Column3) WHERE (TableA.Column2 = SpecificValue1) AND (Table3.Column4 = SpecificValue2) ORDER BY TableA.Column5
  4.  
Any help would be appreciated.

By the way, here is the actual query if you are interested:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT PARTSBOOKPAGEDETAIL.PB_ItemNo, PARTSBOOKPAGEDETAIL.PB_ItemSuffix, PARTSBOOKPAGEDETAIL.PB_Qty, PARTSBOOKPAGEDETAIL.PB_SKU, PRODUCTS.PR_Description, USER_DEF.UD_Searchable, PRODUCTS.PR_ProductID FROM ((PARTSBOOKPAGEDETAIL INNER JOIN PRODUCTS ON PARTSBOOKPAGEDETAIL.PB_SKU = PRODUCTS.PR_SKU) LEFT OUTER JOIN USER_DEF ON PRODUCTS.PR_ProductID = USER_DEF.UD_ProdID) WHERE (PARTSBOOKPAGEDETAIL.PBPD_Page = ?) AND (USER_DEF.UD_Index = 6) ORDER BY PARTSBOOKPAGEDETAIL.PB_ItemNo, PARTSBOOKPAGEDETAIL.PB_ItemSuffix
  3.  
Apr 11 '06 #1
Share this Question
Share on Google+
3 Replies


Banfa
Expert Mod 5K+
P: 8,916
You are only retrieving the 1 column from TableC (Column4) and you want that column to be either SpecificValue2 or NULL.

Since you are not returning any other columns from TableC I can not see that it the condition you are trying to get for TableC.Column4 is required.

If you just remove the clause

Expand|Select|Wrap|Line Numbers
  1. (Table3.Column4 = SpecificValue2)
Then you will get all the rows you require and you can just treat Table3.Column4 as SpecificValue2 or NOT SpecificValue2. You code should be esily able to deal with this.

Alternitively you could use an IF replace

Expand|Select|Wrap|Line Numbers
  1. SELECT TableA.Column1, TableA.Column2, TableB.Column3, TableC.Column4, TableA Column5 FROM  ...
with

Expand|Select|Wrap|Line Numbers
  1. SELECT TableA.Column1, TableA.Column2, TableB.Column3, IF(TableC.Column4=SpecificValue2,SpecificValue2,NULL) AS TableC.Column4, TableA Column5 FROM ...
and drop the clause

Expand|Select|Wrap|Line Numbers
  1. (Table3.Column4 = SpecificValue2)

BTW I should point out that I have never worked with IFs before and I don't have a MySQL server present to practice on at the moment so that is completely untested.
Apr 11 '06 #2

P: 3
The problem is that from TableC I only want values if (Table3.Column4 = SpecificValue2) otherwise I want it to return NULL values since I still need a row with the values from TableA and TableB
Apr 11 '06 #3

Banfa
Expert Mod 5K+
P: 8,916
Like I said first you need to remove the WHERE clause
Expand|Select|Wrap|Line Numbers
  1. (Table3.Column4 = SpecificValue2)
Once you have done that then you will start getting all the rows you require.

My next question is why do you need the value to be NULL. If it is not SpecificValue2 then you can just ignore it.

If you really need it to be NULL then use the IF clause I gave above

Expand|Select|Wrap|Line Numbers
  1. SELECT ... IF(TableC.Column4=SpecificValue2,SpecificValue2,NU  LL) AS TableC.Column4 ... FROM ...
  2.  
Apr 11 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.