Solved

how to get data from same table

Posted on 2007-11-27
6
203 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
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 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??)

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now