Returning duplicate values from where clause

Larry Groves
Larry Groves used Ask the Experts™
on
I have an xls file with a bunch of rows. The rows include columns order number and dollar amount. I need to add 1 more column (address ID) to the XLS. The problem I'm having is that in the XLS, a given order number can be in there 2 or more times because the order may have been split-shipped. So, in my select's where clause, I will be passing a given order number 2 or more times and I need my result set to return the same number of times that I'm passing it.

For example:

ORDER . AMOUNT
12345 . 100.00
12345 . 95.98
12346 . 10.22

Open in new window


select order,address from order where order in (12345,12345,12346,...) order by order;

Thanks,
Larry

when I run this, I only get back 12345 1 time. Is there anyway I can get back a result with 12345 in there twice
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I see no reason why the second or subsequent rows would be returned for each order. It's not the SQL that is stopping this.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
What is the relevance of Oracle to this question? (please don't use non-relevant topics)

Where are you executing the SQL? (In Excel?)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Oh, sorry for the multiple posts

 "order" is a reserved SQL word (i.e it is used as a command to control the sorting of the output)

I suggest changing the table name and column name to something other than this word. e.g. table = order_detail and column = order_num

select order_num,address from order_detail where order_num in (12345,12345,12346,...) order by order_num;
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Apparently you didn't read my entire question. How exactly do you run a query in Excel using:

select order,address from order where order in (12345,12345,12346,...) order by order;

Yes, Oracle IS the relevant topic.

As for your first reply, if I run that select statement, Oracle will only return:
12345,<address>
12346,<address>

because there is only 1 row for order 12345 in the DB. The thing is, I need 12345 returned twice (based on my where clause) because there are 2 rows for that 1 order in the Excel file.

Thanks,
Larry

Author

Commented:
The table name is actually ORDERS. I was just using it as an example in my example select statement.

Thanks,
Larry
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Larry, I did read the question - what you failed to mention is that the address table is in Oracle.

You cannot get more rows returned from the table by using an IN list, you have to use a join e.g.

select
   x."order", x.amount, o.address
from excel_table x
left join ora.table_name o on x."order" = o."order"

Author

Commented:
I'm totally confused. What exactly is "excel_table" and I'm not running any queries against an address table. Maybe this example query is better...

select order_id,address_id
from orders
where order_id in (12345,12345,12346,)
order by order_id;

Open in new window


The list of order_id values is in an actual Excel spreadsheet. I'm not linking the spreadsheet to the DB in any way.

Thanks,
Larry
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Using an IN list will NOT force multiple rows of output.  You need a join, e.g.

select order_id,address_id
from orders 
inner join (
 select  12345 as order_id from dual union all
 select  12345 as order_id from dual union all
 select  12346 as order_id from dual
  ) x on orders.order_id = x.orders_id
order by orders.order_id;

Open in new window


[+edit]
for reference this where clause

         where order_id in (12345,12345,12346,)

is just an abbreviation for this:

         where (order_id  = 12345 or order_id  = 12345 or order_id  = 12346)

Author

Commented:
Thank you PortletPaul. This worked perfectly! I appreciate your help.

Thanks,
Larry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial