SQL doubt

[bevarse]

hello sql gurus,

naaku oka simple select statement ki help kaavali, 

2 tables ni join cheyyali. Example ki order table and order status table anukundam. 

I need to join on orderId but should only select the orders that are not delivered. So nenu ee kinda 2 tables join chesi query chesthe I should see only orderId 2 and 3 in the query result.

any ideas on the best way to do this?

OrderId	ItemID	OrderDate
1	11650	8/8/2017
2	11660	8/8/2017
3	11670	8/8/2017

 

StatusId	OrderId	Status
1	1	Placed
2	1	Received
3	1	Shipped
4	1	Delivered
5	2	Placed
6	2	Received
7	2	Shipped
8	3	Placed
9	3	Received

[BostonBullodu]

but order id 1 ki multiple results unnayi kada how can you skip those 

[BostonBullodu]

Select O.ItemId, O.OrderDate,S.Status

FROM dbo.Orders O 

JOIN dbo.Status S  ON O.orderid = S.orderid

where S.Status != 'Delivered'

[bevarse]

Just now, BostonBullodu said:

Select O.ItemId, O.OrderDate,S.Status

FROM dbo.Orders O 

JOIN dbo.Status S  ON O.orderid = S.orderid

where S.Status != 'Delivered'

Ila cHesthe I will still get order ID 1 in the result kada bro.

[BostonBullodu]

2 minutes ago, bevarse said:

Ila cHesthe I will still get order ID 1 in the result kada bro.

requirement enti bro

not delivered ante migathavi ravali kada 

[bevarse]

Just now, BostonBullodu said:

requirement enti bro

not delivered ante migathavi ravali kada 

requirement yenti ante, I want to show the orders that are not yet delivered. 

so only order id 2 and 3 kanapadali query result lo and order 1 is already delivered kaabatti it should not be displayed

[khabardar]

Select O.orderID ,O.ItemId, O.OrderDate,S.Status

FROM dbo.Orderr O 

JOIN dbo.Status S  ON O.orderid = S.orderid

where O.orderID not in ( select OrderId from  Status where Status = 'Delivered')
 

[Suhaas]

SELECT O.OrderId
FROM orders O
WHERE NOT EXISTS (SELECT S.OrderId FROM status S WHERE O.OrderId = S.OrderId and S.status = 'delivered')

[bevarse]

5 minutes ago, khabardar said:

Select O.ItemId, O.OrderDate,S.Status

FROM dbo.Orders O 

JOIN dbo.Status S  ON O.orderid = S.orderid

where orderID in ( select OrderId from  Status where S.Status <> 'Delivered')

Status table lo orderid 1 ki other entried vunnai kada bro so this also returns orderid 1 in the result set. 

[bevarse]

4 minutes ago, Suhaas said:

SELECT O.OrderId
FROM orders O
WHERE NOT EXISTS (SELECT S.OrderId FROM status S WHERE O.OrderId = S.OrderId and S.status = 'delivered')

Suhas bhayya you rock again. 

neeku stackoverflow lo account vunda, I will post this and also the jquery help you did and you can answer it there

[Suhaas]

Just now, bevarse said:

Suhas bhayya you rock again. 

neeku stackoverflow lo account vunda, I will post this and also the jquery help you did and you can answer it there

No worries Bro. Happy to help. 

StackOverflow la undi kani lite bro. Take it easy

[khabardar]

Select O.orderID ,O.ItemId, O.OrderDate,S.Status

FROM dbo.Orderr O 

JOIN dbo.Status S  ON O.orderid = S.orderid

where O.orderID not in ( select OrderId from  Status where Status = 'Delivered')
 

-- I tested bro.. edi pakka pani chestadi.. chudu.. 100% sure

[bevarse]

@Suhaas asalu nee daily work activity yenti bhayya how come you are so smart ? yemi tintav yela practice chestav ?

@khabardar and @BostonBullodu not to offend you, you were the first ones to offer help. 

 

[Suhaas]

Just now, bevarse said:

@Suhaas asalu nee daily work activity yenti bhayya how come you are so smart ? yemi tintav yela practice chestav ?

@khabardar and @BostonBullodu not to offend you, you were the first ones to offer help. 

 

daily work activity yenti - AFDB 

how come you are so smart ? - Naak antha scene ledu bro. 

[BostonBullodu]

4 minutes ago, bevarse said:

@Suhaas asalu nee daily work activity yenti bhayya how come you are so smart ? yemi tintav yela practice chestav ?

@khabardar and @BostonBullodu not to offend you, you were the first ones to offer help. 

 

braboyii eh chinna query ke antha happy ayyava way to go brother now you will understand diff between in and not exists especially if there are nulls check it out....