T-SQL Query help

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 1:31 pm

  #263490

  The T-SQL query (displayed below) will SELECT customers (including Name, CustomerID, LinkID and OrderItem) based on a specified order date. The customers are identified by a CustomerID, but there is also a method to list family members based on CustomerID and LinkID. The first family member has a LinkID=1, then 2, 3, 4, etc for each family member. Also note, all family members have the same CustomerID but with a different LinkID.

  I need assistance in adding to the query below so I will get the Customer Name, CustomerID, LinkID and OrderItem based on the specified date. But also, the query will return all other family members (Name, CustomerID and LinkID) that are associated with the CustomerID for each order (grouped together).

  Any ideas? Thanks for any assistance you can give.

  --Query:

  Select Cusomter.Name, Customer.CustomerID, Customer.LinkID, Orders.OrderItem,

  Orders.OrderDate

  From Customer

  JOIN Orders ON Customer.CustomerKey=Orders.OrderKey

  WHERE Orders.OrderDate='mm-dd-yyyy'

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 7, 2012 at 1:36 pm

  #1510017

  a suggestion

  if you can provide some 'create table' scripts for your relevant tables and some sample data...say 20 rows (insert data)....then someone maybe jump in and help

  edit>>...and your expected results based on sample data provided

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 1:53 pm

  #1510018

  Thanks for the feedback. I would post the data but for security reasons

  I can't. Some of the items in the query have been changed as well for this reason.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 7, 2012 at 1:59 pm

  #1510020

  forefj (7/7/2012)

  ---

  Thanks for the feedback. I would post the data but for security reasons

  I can't. Some of the items in the query have been changed as well for this reason.

  I understand why you cannot post exact data...but surely, with a little effort, you could easily post some ''sample'' data....that represents your question.

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  July 7, 2012 at 2:00 pm

  #1510021

  forefj (7/7/2012)

  ---

  Thanks for the feedback. I would post the data but for security reasons

  I can't. Some of the items in the query have been changed as well for this reason.

  You could make up some data to help us help you. Please see the first link in my signature line below for how to do that correctly.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 2:25 pm

  #1510023

  Here is an example of what I am currently getting and what I need as a result of the query:

  I need to display not only the Name and CustomerID of the person who purchased an item on a specified date but I also need to display the other family members. Below I will write an example of what I am getting now and what I am looking to get.

  Current Results:

  Name-----CustomerID------LinkID-----OrderItem----OrderDate

  Smith-------12345----------------- 3----------Widget-----2012-07-05

  What I need to be displayed (below)

  (includes info of person who placed an order and 3 other family members with LinkID)

  Name------CustomerID------LinkID-----OrderItem--OrderDate

  Smith-------12345----------------- 3----------Widget----2012-07-05

  Smith-------12345------------------1

  Smith-------12345------------------2

  Smith-------12345------------------4

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 2:33 pm

  #1510024

  I wasn't sure you meant to post some sample data when you asked for

  "'create table' scripts"

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 7, 2012 at 2:35 pm

  #1510025

  I suspect you didn't read the article Jeff asked you to read. Please do so.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 2:59 pm

  #1510027

  I read Jeff's posting, especially the part about people posting for homework assignments or a job interview - neither apply in my case.

  I am still somewhat new working with T-SQL, I am not sure what other sample data you need to be able to assist. It looks like the example in Jeff's article is talking about building a table.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 7, 2012 at 3:10 pm

  #1510029

  forefj (7/7/2012)

  ---

  I read Jeff's posting, especially the part about people posting for homework assignments or a job interview - neither apply in my case.

  I am still somewhat new working with T-SQL, I am not sure what other sample data you need to be able to assist. It looks like the example in Jeff's article is talking about building a table.

  appreciate you are new...heres a quick example on providing data that can be easily used by people trying to help you.

  CREATE TABLE [dbo].[Employee](

  [EmployeeID] [int] NOT NULL,

  [ManagerID] [int] NULL,

  [EmployeeName] [varchar](10) NOT NULL,

  [Sales] [int] NOT NULL,

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[Employee]([EmployeeID], [ManagerID], [EmployeeName], [Sales])

  SELECT 1, NULL, N'Jim', 200000 UNION ALL

  SELECT 2, 1, N'Lynne', 90000 UNION ALL

  SELECT 3, 1, N'Bob', 100000 UNION ALL

  SELECT 6, 17, N'Eric', 75000 UNION ALL

  SELECT 7, 3, N'Vivian', 60000 UNION ALL

  SELECT 8, 3, N'Bill', 80000 UNION ALL

  SELECT 12, 8, N'Megan', 50000 UNION ALL

  SELECT 13, 8, N'Kim', 55000 UNION ALL

  SELECT 17, 2, N'Butch', 70000 UNION ALL

  SELECT 18, 39, N'Lisa', 40000 UNION ALL

  SELECT 20, 3, N'Natalie', 40000 UNION ALL

  SELECT 21, 39, N'Homer', 30000 UNION ALL

  SELECT 39, 1, N'Ken', 90000 UNION ALL

  SELECT 40, 1, N'Marge', 120000

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 3:20 pm

  #1510031

  Thanks for the info. Can I obtain some of that info by looking at the properties of the database? It is SQL Server 2000 and I using Management Studio 2008 to connect. I search for some documentation but didn't finding anything specific to that table (only a Sales_Commision table) and it isn't related to the specific query I am looking to build.

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  July 7, 2012 at 3:36 pm

  #1510034

  J Livingston SQL (7/7/2012)

  ---

  forefj (7/7/2012)

  ---

  I read Jeff's posting, especially the part about people posting for homework assignments or a job interview - neither apply in my case.

  I am still somewhat new working with T-SQL, I am not sure what other sample data you need to be able to assist. It looks like the example in Jeff's article is talking about building a table.

  appreciate you are new...heres a quick example on providing data that can be easily used by people trying to help you.

  CREATE TABLE [dbo].[Employee](

  [EmployeeID] [int] NOT NULL,

  [ManagerID] [int] NULL,

  [EmployeeName] [varchar](10) NOT NULL,

  [Sales] [int] NOT NULL,

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[Employee]([EmployeeID], [ManagerID], [EmployeeName], [Sales])

  SELECT 1, NULL, N'Jim', 200000 UNION ALL

  SELECT 2, 1, N'Lynne', 90000 UNION ALL

  SELECT 3, 1, N'Bob', 100000 UNION ALL

  SELECT 6, 17, N'Eric', 75000 UNION ALL

  SELECT 7, 3, N'Vivian', 60000 UNION ALL

  SELECT 8, 3, N'Bill', 80000 UNION ALL

  SELECT 12, 8, N'Megan', 50000 UNION ALL

  SELECT 13, 8, N'Kim', 55000 UNION ALL

  SELECT 17, 2, N'Butch', 70000 UNION ALL

  SELECT 18, 39, N'Lisa', 40000 UNION ALL

  SELECT 20, 3, N'Natalie', 40000 UNION ALL

  SELECT 21, 39, N'Homer', 30000 UNION ALL

  SELECT 39, 1, N'Ken', 90000 UNION ALL

  SELECT 40, 1, N'Marge', 120000

  Oh, now that's too funny. I recognize that data.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• forefj

  Mr or Mrs. 500

  Points: 593

  More actions

  July 7, 2012 at 5:01 pm

  #1510036

  I am not sure if I posted my last reply in the correct area, so I am adding it in again. Thanks

  Thanks for the info. Can I obtain some of that info by looking at the properties of the database? It is SQL Server 2000 and I using Management Studio 2008 to connect. I search for some documentation but didn't finding anything specific to that table (only a Sales_Commision table) and it isn't related to the specific query I am looking to build.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 8, 2012 at 1:11 am

  #1510056

  forefj (7/7/2012)

  ---

  I read Jeff's posting, especially the part about people posting for homework assignments or a job interview - neither apply in my case.

  I am still somewhat new working with T-SQL, I am not sure what other sample data you need to be able to assist. It looks like the example in Jeff's article is talking about building a table.

  does this give you an idea?.....am guessing on the table structure

  CREATE TABLE [dbo].[Customer](

  [CustomerID] [int] NULL,

  [LinkID] [int] NULL,

  [Name] [varchar](50) NULL,

  [Customerkey] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[Customer]([CustomerID], [LinkID], [Name], [Customerkey])

  SELECT 1, 1, N'Paul', 11 UNION ALL

  SELECT 1, 2, N'Ringo', 12 UNION ALL

  SELECT 1, 3, N'George', 13 UNION ALL

  SELECT 1, 4, N'John', 14 UNION ALL

  SELECT 2, 1, N'Peter', 21 UNION ALL

  SELECT 2, 2, N'Paul', 22 UNION ALL

  SELECT 2, 3, N'Mary', 23 UNION ALL

  SELECT 3, 1, N'Emerson', 31 UNION ALL

  SELECT 3, 2, N'Lake', 32 UNION ALL

  SELECT 3, 3, N'Palmer', 33

  GO

  CREATE TABLE [dbo].[Orders](

  [OrderKey] [int] NULL,

  [OrderItem] [varchar](50) NULL,

  [OrderDate] [datetime] NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[Orders]([OrderKey], [OrderItem], [OrderDate])

  SELECT 21, N'Widget', '20120704' UNION ALL

  SELECT 32, N'Wangle', '20120701' UNION ALL

  SELECT 13, N'Waffle', '20120615' UNION ALL

  SELECT 11, N'Wangle', '20120708'

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 8, 2012 at 2:55 am

  #1510060

  try this......let us know if this is anywhere near your requirements

  regards

  CREATE TABLE [dbo].[Customer](

  [CustomerID] [int] NULL,

  [LinkID] [int] NULL,

  [Name] [varchar](50) NULL,

  [Customerkey] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[Customer]([CustomerID], [LinkID], [Name], [Customerkey])

  SELECT 1, 1, N'Paul', 11 UNION ALL

  SELECT 1, 2, N'Ringo', 12 UNION ALL

  SELECT 1, 3, N'George', 13 UNION ALL

  SELECT 1, 4, N'John', 14 UNION ALL

  SELECT 2, 1, N'Peter', 21 UNION ALL

  SELECT 2, 2, N'Paul', 22 UNION ALL

  SELECT 2, 3, N'Mary', 23 UNION ALL

  SELECT 3, 1, N'Emerson', 31 UNION ALL

  SELECT 3, 2, N'Lake', 32 UNION ALL

  SELECT 3, 3, N'Palmer', 33

  GO

  CREATE TABLE [dbo].[Orders](

  [OrderKey] [int] NULL,

  [OrderItem] [varchar](50) NULL,

  [OrderDate] [datetime] NULL,

  [OrderID] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[Orders]([OrderKey], [OrderItem], [OrderDate], [OrderID])

  SELECT 21, N'Widget', '20120704 00:00:00.000', 1 UNION ALL

  SELECT 32, N'Wangle', '20120701 00:00:00.000', 2 UNION ALL

  SELECT 13, N'Waffle', '20120615 00:00:00.000', 3 UNION ALL

  SELECT 11, N'Wangle', '20120708 00:00:00.000', 4

  GO

  SELECT Name ,

  CustomerID ,

  LinkID ,

  OrderItem ,

  OrderDate ,

  OrderID

  FROM(

  SELECT Customer.Name ,

  Customer.CustomerID ,

  Customer.LinkID ,

  Orders.OrderItem ,

  Orders.OrderDate ,

  Orders.OrderID

  FROM

  Customer INNER JOIN Orders ON Customer.Customerkey = Orders.OrderKey

  UNION ALL

  SELECT C2.Name ,

  C2.CustomerID ,

  C2.LinkID ,

  NULL ,

  NULL ,

  O1.OrderID

  FROM

  Customer AS C2 INNER JOIN Customer AS C1 ON C2.CustomerID = C1.CustomerID

  AND C2.LinkID <> C1.LinkID

  INNER JOIN Orders AS O1 ON C1.Customerkey = O1.OrderKey

  )AS x

  ORDER BY OrderID , OrderDate DESC , LinkID;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day