?
Solved

how to get data from same table

Posted on 2007-11-27
6
Medium Priority
?
210 Views
Last Modified: 2010-03-20
HI everyone,

i have a table which go all the information of orders,

Cust-number   style-code qty and date-time

customer number can be repeated as one custoemr can place many orders and probably have place many orders in same date-time.

what i want is

Select * from [offercom]
where style_code in(style codes.)
but i need other information as well as which people have ordered other stuff includin gthese specific styles.
0
Comment
Question by:ammartahir1978
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

Author Comment

by:ammartahir1978
ID: 20361776
here is the original data
2,272,771      18P02      PRINTED BANDED DRESS            INTERNET      1.00      20/11/2007
2,272,771      08N28      BELTED JERSEY DRESS            INTERNET      1.00      20/11/2007
2,272,771      12P06      TWILL PANELLED SKIRT            INTERNET      1.00      20/11/2007
2,272,771      17P04      PAISLEY SHIRT DRESS            INTERNET      1.00      20/11/2007
2,272,771      17P06      DAISY PRINT SHIRT DRESS      INTERNET      1.00               20/11/2007
2,272,771      06P04      ROLL NECK KNIT DRESS            INTERNET      1.00      20/11/2007
2,350,216      10P14      SUPERSOFT LUREX ROBE            INTERNET      1.00      20/11/2007
2,350,216      14N12      BOOTLEG JEAN 36"            INTERNET      1.00      20/11/2007
2,340,542      14N32      BOOTLEG JEAN 38"            INTERNET      1.00      20/11/2007
2,237,158      18P05      JERSEY SHIRT DRESS            INTERNET      1.00      20/11/2007
2,237,158      07P27      SEQUIN TUNIC            INTERNET      1.00      20/11/2007
2,334,543      05P24      SEQUIN CAMI            INTERNET      1.00      20/11/2007
2,334,543      11P05      STAND COLLAR BELTED JACKET      INTERNET      1.00      20/11/2007
2,334,543      06P19      ANGORA MIX CARDIGAN            INTERNET      1.00      20/11/2007
1,369,734      05P24      SEQUIN CAMI            INTERNET      1.00      20/11/2007
1,369,734      05P24      SEQUIN CAMI            INTERNET      1.00      20/11/2007
1,369,734      07P26      COVER UP                                  INTERNET      1.00      20/11/2007
2,369,455      03P02      MULTI STITCH LINEN CROP      INTERNET      1.00      20/11/2007
2,303,385      10P06      STRIPE T SHIRT            INTERNET      1.00      20/11/2007


the deesired result is
if any customer had bought the items in IN STATEMENT
the result should bring other items of purchase also for that specific customer

for exampl

customer number 2772771     bought 18P02 as this is in my statment that if a customer bought 08p02 what else he have bought
so the result should be

2,272,771      18P02      PRINTED BANDED DRESS            INTERNET      1.00      20/11/2007
2,272,771      08N28      BELTED JERSEY DRESS            INTERNET      1.00      20/11/2007
2,272,771      12P06      TWILL PANELLED SKIRT            INTERNET      1.00      20/11/2007
2,272,771      17P04      PAISLEY SHIRT DRESS            INTERNET      1.00      20/11/2007
2,272,771      17P06      DAISY PRINT SHIRT DRESS      INTERNET      1.00               20/11/2007
2,272,771      06P04      ROLL NECK KNIT DRESS            INTERNET      1.00      20/11/2007
0
 
LVL 1

Expert Comment

by:arunyeshi2000
ID: 20361798
Why dont you just do a select * with order by Customer Number and style code...
select * from [offercom] order by customer,style_codes
0
 
LVL 11

Accepted Solution

by:
deroby earned 2000 total points
ID: 20361822
Not sure I understand the problem. The SELECT you wrote should return you ALL the information for given specific styles.
Do you want to add information from other tables (using a JOIN statement) or do you want to add information from this table, but for stuff that is not part of these styles ?

In the latter case I think what you want is something like the query below.
This will list ALL the orders for a customer for which he had at least one order that matches the required style-code for a given date (the last might be optional??)

SELECT * 
  FROM [offercom] f -- f for FULL info
 WHERE EXISTS ( SELECT 1
                  FROM [offercom] s -- s for STYLE-related
                 WHERE s.cust_number = f.cust_number -- same customer
                   AND s.date_time   = f.date_time   -- same order date (optional?)
                   AND s.style_code IN (style-codes) )

Open in new window

0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:ammartahir1978
ID: 20362075
i want is

customer who order the styles and also have ordered other stuff with the style included in IN statement

hope this explain
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20362258
try this pattern from your other question

select * from [offercom]
where CustomerID in
(Select CustomerID from [offercom] where style_code in (style codes)
where ... date filter ... other filter...
)
and ... date filter ... other filter...
0
 
LVL 11

Expert Comment

by:deroby
ID: 20364820
[customer who order the styles and also have ordered other stuff with the style included in IN statement]

Actually, this makes it even less clear =) Anyway,  according to the example you gave, I think that the code presented by imitchie & me should do the trick alright.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question