how to get data from same table

Posted on 2007-11-27
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.
Question by:ammartahir1978

Author Comment

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

Expert Comment

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
LVL 11

Accepted Solution

deroby earned 500 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??)

  FROM [offercom] f -- f for FULL info
                  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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.


Author Comment

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
LVL 25

Expert Comment

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...
LVL 11

Expert Comment

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.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL recovery 7 31
find SQL job run average duration 24 56
getting error while running below query  in sql 2 16
Need quicker response from an Execption table 11 23
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

749 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