Issue in the join
Hi I am working on 2 tables 

TABLE1

COL1
1
2
4
5
6
7
8


TABLE2

COL2 USERID
3     ADD
7     ADD
4     BBB
5     SSS
ALL   XYZ

I need to  select COL1 from TABLE1 where COL1.TABLE1 = COL2.TABLE2 and TABLE2.userid = @parameter 
Inner join will work fine  except one requirment

for a userid parameter[ ex XYZ] for which COL2 = ALL

I would like to select all COL2 values like 3,4,5 
in that case left join ,inner join is not helping
please suggest




September 14th, 2015 9:12pm

Hi I am working on 2 tables 

TABLE1

COL1
1
2
4
5
6
7
8


TABLE2

COL2 USERID
3     ADD
7     ADD
4     BBB
5     SSS
ALL   XYZ

I need to  select COL1 from TABLE1 where COL1.TABLE1 = COL2.TABLE2 and TABLE2.userid = @parameter 
Inner join will work fine  except one requirment

for a userid parameter[ ex XYZ] for which COL2 = ALL

I would like to select all COL2 values like 3,4,5 
in that case left join ,inner join is not helping
please suggest



I would NOT load your table with "ALL" "XYZ". Instead I would just load the combo box or SSRS Parameter with the ALL value and then dynamically change the query.

The JOIN is doing exactly what it is intended to do and matching ONLY what matches.

I'd sooner see you do a proc with either -1 or NULL as the default value and then dynamically change what you select. In this scenario, the user would select "XYZ" in your application and it would pass -1 or NULL to the proc.

Something like this (This proc uses NULL to return all values):

CREATE PROCEDURE uspAllorSome
	@myValue int = NULL
AS

IF @myValue is NULL
BEGIN
	SELECT COL2, USERID FROM TABLE2
END
ELSE
BEGIN
	SELECT COL2, USERID 
	FROM TABLE2 A 
	INNER JOIN TABLE1 B ON (A.Col1 = B.Col2) 
	WHERE A.USERID = @myValue
END

Free Windows Admin Tool Kit Click here and download it now
September 14th, 2015 9:35pm

Hi JOHN,

If "ALL" means all the col2 values in the table2(3,4,5,7 in this case), you can refer to below sample.

DECLARE @TABLE1 TABLE(COL1 INT)
INSERT INTO @TABLE1 VALUES(1),(2),(4),(5),(6),(7),(8)

DECLARE @TABLE2 TABLE(COL2 VARCHAR(3),USERID CHAR(3))
INSERT INTO @TABLE2 VALUES
('3',     'ADD'),
('7',     'ADD'),
('4',     'BBB'),
('5',     'SSS'),
('ALL'   ,'XYZ'); 

--for further steps, the table2 has to be expanded as below
SELECT T.COL2,cat.USERID FROM @TABLE2 T CROSS APPLY(SELECT USERID FROM @TABLE2 T2 WHERE COL2= CASE WHEN COL2<>'ALL' THEN T.COL2 ELSE T2.COL2 END) cat

DECLARE @parameter CHAR(3)='XYZ'
--wrap the expanded table2 in a cte
;WITH Cte AS(
SELECT T.COL2,cat.USERID FROM @TABLE2 T CROSS APPLY(SELECT USERID FROM @TABLE2 T2 WHERE COL2= CASE WHEN COL2<>'ALL' THEN T.COL2 ELSE T2.COL2 END) cat
) 
SELECT * FROM @TABLE1 t
WHERE EXISTS(
SELECT 1 FROM Cte c WHERE CAST(t.COL1 AS VARCHAR(3))= c.COL2 AND USERID=@parameter
) 

If you have any question, feel free to let me
September 14th, 2015 10:24pm

>> Hi I am working on 2 tables <<

really? Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY);

INSERT INTO Users
VALUES (1), (2), (3), (4), (5), (6), (7), (8);

They must be integers because they are not quoted. Why was this simple code so hard you could not do it? Why did you fail to read the forum rules? What did you try yourself? Why did you fail to post that code? 

More correcting and guessing: 

CREATE TABLE Foobar
(user_id INTEGER NOT NULL PRIMARY KEY
   REFERENCES Users(user_id), --- do you know what this means?
 foo_name CHAR(3) NOT NULL);

INSERT INTO Foobar
VALUES
(3, 'ADD'), 
(4, 'BBB'), 
(5, 'SSS'),
(7, 'ADD'); 

Since ALL is a reserve word or a string in SQL and not an integer this row is not valid. This is why we need DDL! 

('ALL', 'XYZ')

You got the syntax wrong in the query: 

SELECT user_id 
  FROM Users, Foobar 
 WHERE Users.user_id = Foobar.user_id 
   AND Foobar.user_id = @in_user_id;

>>  for a user_id parameter [ex XYZ] for which user_id = 'ALL' 
I would like to select all user_id values like 3, 4, 5 in that case LEFT OUTER JOIN, INNER JOIN is not helping. <<

This is makes no sense. Please read any book on SQL, so you will know that a column has one and only one data type; a tables needs a key; etc.   
Free Windows Admin Tool Kit Click here and download it now
September 14th, 2015 10:43pm