CODEWITHSUNDEEP
sqlms-accesssubquery
AddyProg
AddyProg

Reputation: 3050

SQL Subquery not working properly

I have a table structure like

Files                     Latest_Update
-------------------      ------------------------------
Fid - Name - Type -      l-id  - c_name - c_val - Fid -
-------------------      ------------------------------
1   - D1   - xls  -       1    - text1  - hello -  1  -
-------------------      ------------------------------

  Revisions
------------------------------
r-id  - c_name - c_val - Fid -
------------------------------
 1    - text1  - bye   -  1  -
------------------------------

i am trying to get all values of text1 control either in Revisions or Latest_Update here is what i have tried

SELECT RS.c_name. RS.c_val from (SELECT Revisions.c_name, Revisions.c_val, Latest_Update.c_name,Latest_Update.c_val
From Revisions
INNER JOIN Latest_Update on Revisions.Fid = Latest_Updates.Fid) AS RS

but its not working , i need the result to be something like

Result
------------------------------
- c_name - c_val - Fid -
------------------------------
- text1  - Hello-  1   -
------------------------------
- text1  - bye   -  1  -
------------------------------

I am Using MS ACCESS 2010

Upvotes: 0

Views: 95

Answers (3)

MayOkba
MayOkba

Reputation: 99

Why not to use union instead of inner join ...

select * from (
select c_name,c_val,fid from Revisions
union 
select c_name,c_val,fid from Latest_updates)x
where fid = 1

Upvotes: 1

unniks
unniks

Reputation: 166

I think union is the solution. Something like..

SELECT *
FROM (
SELECT Revisions.c_name, Revisions.c_val, Revisions.Fid
  FROM Revisions
UNION ALL
SELECT Latest_Update.c_name,Latest_Update.c_val, Latest_Update.c_name,Latest_Update.Fid
FROM Latest_Update
) RS
<if needed add Where Condition>
ORDER BY RS.Fid

Upvotes: 2

Elvedin Hamzagic
Elvedin Hamzagic

Reputation: 855

SELECT c_name, c_val, Fid FROM Latest_Update
WHERE c_name = 'text1'
UNION ALL
SELECT c_name, c_val, Fid FROM Revisions
WHERE c_name = 'text1'

Upvotes: 1

Related Questions