January 3, 2017 at 6:26 am
I have 2 tables
1-Web_Orders
Reference_No int
Released int
Data :
Reference_No , Released
123 ,0
456,1
789,1
002,1
2-tblOptionalRefNo
Reference_No int
Data:
Reference_No
123
Table 2 is an optional . Sometime it can have data and sometimes not. so If it has data, we need to fetch data on the basis of table 2 else fetch data from table1. Table2 will have Reference_No those exist in table1 , so the purpose is to filter data only.
e.g. in above sample data, it should return 123
If tblOptionalRefNo is empty , it should return
123
456
789
002
I wrote following query , it is returning results if table2 has data, but when table2 is empty ,it don't display results. what could be problem ?
SELECT wo.Reference_No
FROM Web_Orders wo
INNER JOIN dbo.tblOptionalRefNo OO
ON WO.Reference_No = CASE WHEN OO.Reference_No is null THEN WO.Reference_No
ELSE OO.Reference_No
END
WHERE wo.Released IN (0,1)
GROUP BY wo.Reference_No
HAVING COUNT(1) = 1
January 3, 2017 at 6:29 am
Why don't you use left outer join?
😎
January 3, 2017 at 6:31 am
because if table2 has Reference_No, i want to display only these. Left join will display table1 data all the time
January 3, 2017 at 6:35 am
thbaig (1/3/2017)
because if table2 has Reference_No, i want to display only these. Left join will display table1 data all the time
Can you write a better description of what you are trying to do? So far your description is erratic and ambiguous.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 3, 2017 at 6:36 am
So do the left join and then use COALESCE to pick up t2 data if it exists:
select ColName = coalesce(t2.col,t1.col)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2017 at 6:41 am
thbaig (1/3/2017)
because if table2 has Reference_No, i want to display only these. Left join will display table1 data all the time
Do you mean this?
SELECT Reference_No
FROM (
SELECT Reference_No
FROM dbo.tblOptionalRefNo
UNION ALL
SELECT wo.Reference_No
FROM Web_Orders wo
WHERE wo.Released IN (0,1)
AND NOT EXISTS(SELECT 1 FROM dbo.tblOptionalRefNo OO WHERE OO.Reference_No = WO.Reference_No)
) d
GROUP BY Reference_No
HAVING COUNT(*) = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 3, 2017 at 6:50 am
No. It return all rows all time.
I have updated description
January 3, 2017 at 6:58 am
thbaig (1/3/2017)
No. It return all rows all time.I have updated description
If you want results from either one table or the other, then use IF EXISTS()
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 3, 2017 at 7:00 am
Phil Parkin (1/3/2017)
So do the left join and then use COALESCE to pick up t2 data if it exists:select ColName = coalesce(t2.col,t1.col)
If following is as per suggestion, it return all data and not filter if table2 has Reference_No
SELECT coalesce(oo.Reference_No,wo.Reference_No) as Reference_No
FROM Web_Orders wo
left JOIN dbo.tblOptionalRefNo OO
ON WO.Reference_No = OO.Reference_No
Released IN (0,1)
GROUP BY coalesce(oo.Reference_No,wo.Reference_No)
HAVING COUNT(1) = 1
January 3, 2017 at 7:10 am
thbaig (1/3/2017)
I have 2 tables1-Web_Orders
Reference_No int
Released int
Data :
Reference_No , Released
123 ,0
456,1
789,1
002,1
2-tblOptionalRefNo
Reference_No int
Data:
Reference_No
123
Table 2 is an optional . Sometime it can have data and sometimes not. so If it has data, we need to fetch data on the basis of table 2 else fetch data from table1. Table2 will have Reference_No those exist in table1 , so the purpose is to filter data only.
e.g. in above sample data, it should return 123
If tblOptionalRefNo is empty , it should return
123
456
789
002
I wrote following query , it is returning results if table2 has data, but when table2 is empty ,it don't display results. what could be problem ?
SELECT wo.Reference_NoFROM Web_Orders wo
INNER JOIN dbo.tblOptionalRefNo OO
ON WO.Reference_No = CASE WHEN OO.Reference_No is null THEN WO.Reference_No
ELSE OO.Reference_No
END
WHERE wo.Released IN (0,1)
GROUP BY wo.Reference_No
HAVING COUNT(1) = 1
I think you need a slightly different approach:
SELECT WO.*
FROM Web_Orders AS WO
INNER JOIN tblOptionalRefNo AS ORN
ON WO.Reference_No = ORN.Reference_No
UNION ALL
SELECT W.*
FROM Web_Orders AS W
WHERE NOT EXISTS (
SELECT 1
FROM Web_Orders AS W2
INNER JOIN tblOptionalRefNo AS O2
ON W2.Reference_No = O2.Reference_No
);
Let me know if that works...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 3, 2017 at 7:15 am
thbaig (1/3/2017)
Phil Parkin (1/3/2017)
So do the left join and then use COALESCE to pick up t2 data if it exists:select ColName = coalesce(t2.col,t1.col)
If following is as per suggestion, it return all data and not filter if table2 has Reference_No
SELECT coalesce(oo.Reference_No,wo.Reference_No) as Reference_NoFROM Web_Orders wo
left JOIN dbo.tblOptionalRefNo OO
ON WO.Reference_No = OO.Reference_No
Released IN (0,1)
GROUP BY coalesce(oo.Reference_No,wo.Reference_No)
HAVING COUNT(1) = 1
I was assuming that your 'tblOptionalRefNo' table contained other data, not just reference numbers.
So let's say we have Web_Orders(Reference_No, Col1) and tblOptionalRefNo(Reference_No, Col1).
My assumption was that you wanted to display Col1 from tblOptionalRefNo, if a match on Reference_No was found. Otherwise display Col1 from Web_Orders.
select Col1 = coalesce(orn.Col1, wo.Col1)
from Web_Orders wo
left join tblOptionalRefNo orn on wo.Reference_No = orn.Reference_No
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply