October 12, 2005 at 9:16 am
I have a simple union I'm trying to do. I want the results to return only 1 row. col_a & col_b exist in both tables. However, col_c exists only in TABLE_A. If I perform the UNION w/ col_a & col_b I get 1 row. However, when I add col_c (see below) I get 2 rows. What am I doing wrong?
SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c
FROM TABLE_A
UNION
SELECT TABLE_B.col_a, TABLE_B.col_b, '' col_c
FROM TABLE_B
October 12, 2005 at 9:21 am
Because the values of col_c in table a is different than your derived value of '' in col_c in table_b?
your using the union statement which removes duplicates from your 2 queries. and the records are unique because of the difference in col_c. Please post your sample data so we can better see what your issue is.
Why would you want to union values from two differnt tables that have a different # of columns and expect to only get one row?
create table #A (col_A int, col_B int, Col_C varchar(10))
create table #B (col_A int, col_B int)
Insert into #a values (1, 2, 'A')
Insert into #b values (1, 2)
select col_a, col_b, col_C
from #a
union
select col_a, col_b, '' as col_C
from #b
col_a,col_b,col_C
1,2,
1,2,A
October 12, 2005 at 9:31 am
col_c does not exist in TABLE_B - I'm just using it as a placeholder for the UNION. What can I do to get 1 row?
October 12, 2005 at 9:39 am
The first UNION that you did filtered out the second row because it was identical to the first. Your second UNION has rows which are not identical - so they are both output.
If you are receiving this as the output:
1,2,
1,2,A
which row should not be output? First thing we need is a rule about which rows get excluded from the output..
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
October 12, 2005 at 9:46 am
Since I am using the 'computed' columns as just placeholders to make the UNION work, I always want to use the values from the 'REAL' columns. So I woulkd want 1,2,A
October 12, 2005 at 9:51 am
OK - but there must be some circumstances under which you want the rows from TABLE_b, otherwise why bother with the UNION in the first place?
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
October 12, 2005 at 9:58 am
TABLE_B has a column (order_no) which is not contained on TABLE_A. Or, maybe it's better for me to...
SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c,
col_d = (SELECT col_d FROM _B ...)
FROM TABLE_A
October 12, 2005 at 10:08 am
I think this is maybe what you want:
SELECT TABLE_A.col_a, TABLE_A.col_b, TABLE_A.col_c, TABLE_B.col_d
FROM TABLE_A Left join TABLE_B on TABLE_A.key = TABLE_B.key
* Noel
October 12, 2005 at 10:09 am
Yes, this doesn't really sound to me like a UNION query after all.
Can you provide some sample data - just the relevant fields - and show also your desired output? Just two or three rows should be enough to clear this up.
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
October 12, 2005 at 10:20 am
Hope this helps - thank you...
BL_TRUCK_BOL_DEST_COMM_ORDER specifies how much of a commodity has been order by a specific order. BL_TRUCK_BOL_DEST_COMM specifies the total amount of product the destination plans to receive. There could be multiple orders (BL_TRUCK_BOL_DEST_COMM_ORDER) to one BL_TRUCK_BOL_DEST_COMM.
SELECT BL_TRUCK_BOL_DEST_COMM.dest_bol_no,
BL_TRUCK_BOL_DEST_COMM.rev_no,
BL_TRUCK_BOL_DEST_COMM.comm_code,
BL_TRUCK_BOL_DEST_COMM.quantity,
BL_TRUCK_BOL_DEST_COMM.bol_no,
BL_TRUCK_BOL_DEST_COMM.trailer_id,
BL_TRUCK_BOL_DEST_COMM.pup_trailer_id,
'' order_no
FROM BL_TRUCK_BOL_DEST_COMM
WHERE BL_TRUCK_BOL_DEST_COMM.dest_bol_no = '00000001'
AND BL_TRUCK_BOL_DEST_COMM.rev_no = 0
AND BL_TRUCK_BOL_DEST_COMM.comm_code = '32410'
UNION
SELECT BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no,
BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no,
BL_TRUCK_BOL_DEST_COMM_ORDER.comm_code,
BL_TRUCK_BOL_DEST_COMM_ORDER.quantity,
BL_TRUCK_BOL_DEST_COMM_ORDER.bol_no,
'' trailer_id,
'' pup_trailer_id,
BL_TRUCK_BOL_DEST_COMM_ORDER.order_no
FROM BL_TRUCK_BOL_DEST_COMM_ORDER
WHERE BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no = '00000001'
AND BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no = 0
AND BL_TRUCK_BOL_DEST_COMM_ORDER.comm_code = '32410'
dest_bol_no...rev_no...comm_code...quantity...bol_no......trailer_id...pup_trailer_id...order_no 00000001......0...........32410..........1000.00....000515AC....................................00000001
00000001......0...........32410..........1000.00....000515AC...22..........NULL..........................
October 12, 2005 at 10:33 am
OK - getting there. I think you want something like this:
SELECT t1.dest_bol_no,
t1.rev_no,
t1.comm_code,
t1.quantity,
t1.bol_no,
t1.trailer_id,
t1.pup_trailer_id, t2.order_no
FROM BL_TRUCK_BOL_DEST_COMM t1 join BL_TRUCK_BOL_DEST_COMM_ORDER t2 on t1.dest_bol_no = t2.dest_bol_no and
t1.rev_no = t2.rev_no and t1.comm_code = t2.comm_code
WHERE t1.dest_bol_no = '00000001'
AND t1.rev_no = 0
AND t1.comm_code = '32410'
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