| Author |
 Topic  |
|
|
jfm
Posting Yak Master
134 Posts |
 Posted - 09/18/2012 : 05:16:58
|
hi guys,
i did the following query:
USE db
SELECT f.number, r.number INTO f_r FROM f, r
WHERE f.number = r.number
Its working, know i want to use UNION function, crossing the data with another table, in order to know which rows are missing in my new table (f_r):
SELECT number FROM f_r
UNION
SELECT number FROM r
IS not working properly, the output that i have in this query is the full rows from 'r' ...
Any idea?
Thanks |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/18/2012 : 05:28:14
|
IT IS working properly!
Your inserted data into f_r is coming from an inner join and that means only rows that exist in both f AND R are inserted.
So your SELECT UNION can't give any other result...
Too old to Rock'n'Roll too young to die. |
 |
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 09/18/2012 : 10:09:53
|
Understand what you mean.
I have the same issue with:
SELECT * INTO FILE_R_WO_MOST_CURRENT_DATE
FROM(
SELECT F.Sif_F,F.NUMBER, F.IDAT FROM F_F AS F
WHERE F.Sif_F='R' AND F.IDAT = (SELECT MAX(IDAT) FROM F_F AS C WHERE F.NUMBER = C.NUMBER)
GROUP BY F.IDAT, F.NUMBER, F.Sif_F
UNION
SELECT A.Sif_F, A.NUMBER_R, A.IDAT FROM F_R AS A
WHERE A.IDAT = F.IDAT
) AS TEMP
The first part of the query is working but whenever i want to cross the tables to get the info that is not the same, I have all the rows...
What can i do?
Thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 09/18/2012 : 10:48:02
|
you should be using EXCEPT instead of UNION to get rows in one table which are not in other table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 09/18/2012 : 10:51:31
|
Is not working,
Just blanks:
SELECT F.Sif_F,F.NUMBER, F.IDAT FROM F_F AS F
WHERE F.Sif_F='R' AND F.IDAT = (SELECT MAX(IDAT) FROM F_F AS C WHERE F.NUMBER = C.NUMBER)
GROUP BY F.IDAT, F.NUMBER, F.Sif_F
EXCEPT
SELECT A.Sif_F, A.NUMBER_R, A.IDAT FROM F_R AS A
WHERE A.IDAT = F.IDAT |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
|
|
bitsmed
Posting Yak Master
Denmark
100 Posts |
Posted - 09/18/2012 : 15:00:09
|
Just as visakh16 suggested, use except.
The except filters out all records from the second select statement.
I have a feeling that it might work, if you switch the first and second select statement, like this:
SELECT A.Sif_F, A.NUMBER_R, A.IDAT FROM F_R AS A
WHERE A.IDAT = F.IDAT
EXCEPT
SELECT F.Sif_F,F.NUMBER, F.IDAT FROM F_F AS F
WHERE F.Sif_F='R' AND F.IDAT = (SELECT MAX(IDAT) FROM F_F AS C WHERE F.NUMBER = C.NUMBER)
GROUP BY F.IDAT, F.NUMBER, F.Sif_F
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 09/20/2012 : 06:10:22
|
Appreciate it guys.
Thanks a lot for your help |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 09/20/2012 : 11:20:27
|
did it work finally?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
selecting
