| SQL query (merged 3) [message #635294] |
Thu, 26 March 2015 07:40  |
 |
sunny1234
Messages: 4
Registered: March 2015
|
Junior Member |
|
|
I have a customer table and transaction table. I want to display all the customer id from Transaction table only if the ShipmentStatus ='Yes'. If any record for ShipmentStatus = NO, I want to exclude all the records for that customer ID. How do i write a query to search in this case?
Customer table:
Customer ID, Order date, Item
1234
2345
6677
Transaction Table:
Customer ID, Transaction ID, ShipmentStatus
1234 45566 Yes
1234 78787 No
1234 55788 Yes
6677 46777 Yes
6677 54666 Yes
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL query [message #635330 is a reply to message #635329] |
Fri, 27 March 2015 01:23  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You said:
Quote:I just need a guidance to write the query
We did.
Now if you want more then read and follow the links John gave you.
To be more specific:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals.
Now maybe you could show us what you tried and tell us where you are stuck and we may help you to go further.
[Updated on: Fri, 27 March 2015 01:32] Report message to a moderator |
|
|
|
|
|
| Re: SQL query [message #635334 is a reply to message #635300] |
Fri, 27 March 2015 02:37 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
sunny1234 wrote on Thu, 26 March 2015 13:21Thanks. But this is just a sample. There is no issue. I just need a guidance to write the query I hve a couple of ideas, but without the tbles I can't try them out.
|
|
|
|
| Re: SQL query [message #635336 is a reply to message #635329] |
Fri, 27 March 2015 02:54 |
|
sunny1234
Messages: 4
Registered: March 2015
|
Junior Member |
|
|
Sorry, i missed to add some more information.. Table structure is changed now.
Order table:
Order No, Order date, ItemStatus
1234 , 01-01-2014 Hold
4567 , 01-31-2013, Sent
6677, 02-02-2015, Hold
Transaction Table:
Order No, Item No, ShipmentStatus
1234 45566 Yes
1234 54345 Yes
1234 95586 Yes
4567 78787 Yes
4567 55788 Yes
6677 46777 No
6677 54666 Yes
In order table, each Order number can have multiple items placed which is in transaction table.
For eg. Order no. 1234 in Transaction table all item no are shipped and in order table Item status is still Hold for 1234. It's not updated. I want to query all the records for order no which is in hold status and Shipment Status is Yes.
|
|
|
|
|
|
| Re: SQL query [message #635340 is a reply to message #635336] |
Fri, 27 March 2015 02:58 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sunny, you will never make any success in your professional life if you ignore all suggestions from people who would like to assist you. Put yoursef in my position: would you bother to spend any more time trying to help someone as deliberately uncooperative as you?
I suggest that you close this topic, with big thankyou to everyone who took time to reply, and start another. This time read the forum guidelines, and do it properly.
|
|
|
|
|
|
| Re: SQL query [message #635352 is a reply to message #635329] |
Fri, 27 March 2015 06:21 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You use analytic COUNT to count ShipmentStatus = NO per customer and then select rows where such count is zero.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
