Home » RDBMS Server » Server Administration » Returning two rows
Returning two rows [message #370815] Thu, 10 February 2000 15:10 Go to next message
Rajeah patil
Messages: 1
Registered: February 2000
Junior Member
I have a table 'cust' with customer_id,ship_id
having 2 rows foe e.g.
customer_id ship_id
----------- -------
3 null
3 5
3 6

If my sql is
select customer_id,ship_id
from cust
where customer_id=3
and ship_id=6

If ship_id=6 row does't exist then i should get
customer_id with 3 and ship_id with null;

If i try to use
select customer_id,ship_id
from cust
where (customer_id=3 and ship_id is null)
or (customer_id=3 and ship_id=6);

it return 2 rows. I want 1 row if bith cust/ship
matches else match ship_id with null cust_id with value
Re: Returning two rows [message #370821 is a reply to message #370815] Fri, 11 February 2000 02:17 Go to previous messageGo to next message
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
Junior Member
Hello,

If U don't mine, I need a proper output for ur Query with example that what you require...

Thanks in Advance..

Atavur Rahaman S.A
Re: Returning two rows [message #370822 is a reply to message #370815] Fri, 11 February 2000 04:37 Go to previous messageGo to next message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
Hallo,

You can do this on following way :

SELECT a.cust,a.ship
FROM tab_cust a
WHERE a.cust = 3
AND NVL(a.ship,-1) = (SELECT MAX(NVL(b.ship,-1))
FROM tab_cust b
WHERE a.cust = b.cust);

This will return : 3 6
When 6 not exists : 3 5
When 5 not exists : 3

If only want for ship to allow the 6 and the NULL then execute following sql:

SELECT a.cust,a.ship
FROM temp_bbb a
WHERE a.cust = 3
AND NVL(a.ship,-1) = (SELECT MAX(NVL(b.ship,-1))
FROM temp_bbb b
WHERE a.cust = b.cust
AND NVL(b.ship,-1) IN (6,-1));

Hope this solves your problem.
(Let me know if it does)

Greetings,

Thierry.
Re: Returning two rows [message #371099 is a reply to message #370815] Fri, 25 August 2000 07:55 Go to previous message
Jaya Kumar NM
Messages: 11
Registered: August 2000
Junior Member
You can try this
select customer_id,ship_id
from cust
where customer_id=3
and ship_id=any(6,null)
Previous Topic: Students in need; Sending email from database through forms.
Next Topic: Difference between unique-not null and primary key
Goto Forum:
  


Current Time: Wed Oct 21 11:51:45 CDT 2020