Solved

how to get data from same table

Posted on 2007-11-27
6
207 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to setup several different housekeeping processes for a 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.

691 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