[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to get data from same table

Posted on 2007-11-27
6
Medium Priority
?
211 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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