Join Query (Please Urgent !!!!!!!!!!)

• @nks

  Mr or Mrs. 500

  Points: 500

  More actions

  January 1, 2009 at 5:44 am

  #103653

  Hello,

  I have three tables Tour, Group, Journey.

  In tour table i have fields like tourid, tourname,TourCancelFlag,....,etc. Here tourid is primary key.

  In group table i have groupid, groupname, tourid,GroupCancelFlag,....,etc.

  Here groupid is primary key and tourid is foreign key to table tour.

  In journey table i have journeyid,groupid,JourneyMode(i.e. Air or Rail),BookingStatus(Cancel,Confirmed,InProcess,...etc),...etc.

  here journeyid is primary key and groupid is foreign key to table group.

  i want combine query to select,

  1) tourid,tourname from tour table where tourcancelflag is not true(i.e all tours except cancelled tours).

  2) count of all groups related to respective tours where groupcancelflag is not true (i.e All groups except cancelled groups).

  3) count of all journies related to groups of respective tour where mode is Air and bookingstatus is not cancel (i.e. All Journies except bookingstatus is Cancel).

  4)count of all journies related to groups of respective tour where mode is Rail and bookingstatus is not cancel (i.e. All Journies except bookingstatus is Cancel).

  5)count of all journies related to groups of respective tour where mode is Air and bookingstatus is Confirmed.

  6)count of all journies related to groups of respective tour where mode is Air and bookingstatus is InProcess.

  7)count of all journies related to groups of respective tour where mode is Rail and bookingstatus is Confirmed.

  8)count of all journies related to groups of respective tour where mode is Rail and bookingstatus is InProcess.

  Thanks & Regards,

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 1, 2009 at 5:57 am

  #919943

  Please post table structure (as create table statements), some sample data and your expected results. Read this to see the best way to post this to get quick responses.

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Do you want one query, or eight? What have you got so far?

  Is this a homework exercise?

  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

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  January 1, 2009 at 1:57 pm

  #920026

  This is obviously either homework or an interview test.

  First, please read the article at the link in my signature below for how to post questions concerning data.

  Second, show us what YOU have tried on each question.

  --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)
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 1, 2009 at 2:13 pm

  #920027

  Jeff Moden (1/1/2009)

  ---

  This is obviously either homework or an interview test.

  And it doesn't appear to be particularly urgent.

  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

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  January 1, 2009 at 2:23 pm

  #920032

  Heh... interview must be over, huh? 😛

  --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)
  

• @nks

  Mr or Mrs. 500

  Points: 500

  More actions

  January 1, 2009 at 9:42 pm

  #920090

  hello

  This is not an interview question or homework exercise.

  I don't have much knowledge of sql.

  I am working on a live project where i have to create one report which displays all above mentioned data.

  Thats why i want a single resultant query.

  Thanks & Regards.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 2, 2009 at 12:15 am

  #920109

  GilaMonster (1/1/2009)

  ---

  Please post table structure (as create table statements), some sample data and your expected results. Read this to see the best way to post this to get quick responses.

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  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

• jon-474332

  SSC Eights!

  Points: 839

  More actions

  January 2, 2009 at 1:26 am

  #920122

  i assume you need help on writting select queries, read following BOL on table joins.

  http://msdn.microsoft.com/en-us/library/ms191472(SQL.90).aspx

  cheers,

  jon

• Grant Fritchey

  SSC Guru

  Points: 398910

  More actions

  January 2, 2009 at 7:22 am

  #920261

  At least show what you've tried that hasn't worked.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• null-512060

  SSChasing Mays

  Points: 613

  More actions

  January 2, 2009 at 12:23 pm

  #920479

  DECLARE @Tour TABLE (tourID INT IDENTITY PRIMARY KEY

  ,tourName VARCHAR(50) NOT NULL

  ,tourCancelFlag BIT NOT NULL)

  DECLARE @Group TABLE (groupID INT IDENTITY PRIMARY KEY

  ,tourID INT NOT NULL

  ,groupName VARCHAR(50) NOT NULL

  ,groupCancelFlag BIT NOT NULL)

  DECLARE @Journey TABLE (journeyID INT IDENTITY PRIMARY KEY

  ,groupID INT NOT NULL

  ,journeyMode VARCHAR(20) NOT NULL

  ,bookingStatus VARCHAR(20) NOT NULL)

  INSERT INTO @Tour (tourName,tourCancelFlag)

  SELECT 'Tour 1', 0UNION ALL

  SELECT 'Tour 2', 1UNION ALL

  SELECT 'Tour 3', 1UNION ALL

  SELECT 'Tour 4', 0UNION ALL

  SELECT 'Tour 5', 0

  INSERT INTO @Group (tourID,groupName,groupCancelFlag)

  SELECT 1, 'Group 1', 0UNION ALL

  SELECT 2, 'Group 2', 1UNION ALL

  SELECT 3, 'Group 3', 1UNION ALL

  SELECT 4, 'Group 4', 0UNION ALL

  SELECT 5, 'Group 5', 0UNION ALL

  SELECT 5, 'Group 1', 0UNION ALL

  SELECT 4, 'Group 2', 1UNION ALL

  SELECT 3, 'Group 3', 1UNION ALL

  SELECT 2, 'Group 4', 0UNION ALL

  SELECT 2, 'Group 5', 0

  INSERT INTO @Journey (groupid,journeyMode,bookingStatus)

  SELECT 1, 'Air', 'Cancel'UNION ALL

  SELECT 2, 'Rail', 'Confirmed'UNION ALL

  SELECT 3, 'Air', 'InProcess'UNION ALL

  SELECT 4, 'Rail', 'Confirmed'UNION ALL

  SELECT 5, 'Air', 'Confirmed'

  SELECT t.tourID

  , t.tourName

  , COUNT(DISTINCT g.groupID) 'GroupCount'

  , SUM(CASE WHEN j.journeyMode = 'Air' AND j.bookingStatus <> 'Cancel' THEN 1 ELSE 0 END) 'AirNotCanceled'

  , SUM(CASE WHEN j.journeyMode = 'Rail' AND j.bookingStatus <> 'Cancel' THEN 1 ELSE 0 END) 'RailNotCanceled'

  , SUM(CASE WHEN j.journeyMode = 'Air' AND j.bookingStatus = 'Confirmed' THEN 1 ELSE 0 END) 'AirConfirmed'

  , SUM(CASE WHEN j.journeyMode = 'Air' AND j.bookingStatus = 'InProcess' THEN 1 ELSE 0 END) 'AirInProcess'

  , SUM(CASE WHEN j.journeyMode = 'Rail' AND j.bookingStatus = 'Confirmed' THEN 1 ELSE 0 END) 'RailConfirmed'

  , SUM(CASE WHEN j.journeyMode = 'Rail' AND j.bookingStatus = 'InProcess' THEN 1 ELSE 0 END) 'RailInProcess'

  FROM @Tour t

  LEFT JOIN @Group g

  ON g.tourID = t.tourID

  AND g.groupCancelFlag <> 1

  LEFT JOIN @Journey j

  ON j.groupID = g.groupID

  WHERE t.tourCancelFlag <> 1

  GROUP BY t.tourID

  , t.tourName

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  January 2, 2009 at 3:35 pm

  #920591

  NULL (1/2/2009)

  ---

  DECLARE @Tour TABLE (tourID INT IDENTITY PRIMARY KEY

  ,tourName VARCHAR(50) NOT NULL

  ,tourCancelFlag BIT NOT NULL)

  DECLARE @Group TABLE (groupID INT IDENTITY PRIMARY KEY

  ,tourID INT NOT NULL

  ,groupName VARCHAR(50) NOT NULL

  ,groupCancelFlag BIT NOT NULL)

  DECLARE @Journey TABLE (journeyID INT IDENTITY PRIMARY KEY

  ,groupID INT NOT NULL

  ,journeyMode VARCHAR(20) NOT NULL

  ,bookingStatus VARCHAR(20) NOT NULL)

  INSERT INTO @Tour (tourName,tourCancelFlag)

  SELECT 'Tour 1', 0UNION ALL

  SELECT 'Tour 2', 1UNION ALL

  SELECT 'Tour 3', 1UNION ALL

  SELECT 'Tour 4', 0UNION ALL

  SELECT 'Tour 5', 0

  INSERT INTO @Group (tourID,groupName,groupCancelFlag)

  SELECT 1, 'Group 1', 0UNION ALL

  SELECT 2, 'Group 2', 1UNION ALL

  SELECT 3, 'Group 3', 1UNION ALL

  SELECT 4, 'Group 4', 0UNION ALL

  SELECT 5, 'Group 5', 0UNION ALL

  SELECT 5, 'Group 1', 0UNION ALL

  SELECT 4, 'Group 2', 1UNION ALL

  SELECT 3, 'Group 3', 1UNION ALL

  SELECT 2, 'Group 4', 0UNION ALL

  SELECT 2, 'Group 5', 0

  INSERT INTO @Journey (groupid,journeyMode,bookingStatus)

  SELECT 1, 'Air', 'Cancel'UNION ALL

  SELECT 2, 'Rail', 'Confirmed'UNION ALL

  SELECT 3, 'Air', 'InProcess'UNION ALL

  SELECT 4, 'Rail', 'Confirmed'UNION ALL

  SELECT 5, 'Air', 'Confirmed'

  SELECT t.tourID

  , t.tourName

  , COUNT(DISTINCT g.groupID) 'GroupCount'

  , SUM(CASE WHEN j.journeyMode = 'Air' AND j.bookingStatus <> 'Cancel' THEN 1 ELSE 0 END) 'AirNotCanceled'

  , SUM(CASE WHEN j.journeyMode = 'Rail' AND j.bookingStatus <> 'Cancel' THEN 1 ELSE 0 END) 'RailNotCanceled'

  , SUM(CASE WHEN j.journeyMode = 'Air' AND j.bookingStatus = 'Confirmed' THEN 1 ELSE 0 END) 'AirConfirmed'

  , SUM(CASE WHEN j.journeyMode = 'Air' AND j.bookingStatus = 'InProcess' THEN 1 ELSE 0 END) 'AirInProcess'

  , SUM(CASE WHEN j.journeyMode = 'Rail' AND j.bookingStatus = 'Confirmed' THEN 1 ELSE 0 END) 'RailConfirmed'

  , SUM(CASE WHEN j.journeyMode = 'Rail' AND j.bookingStatus = 'InProcess' THEN 1 ELSE 0 END) 'RailInProcess'

  FROM @Tour t

  LEFT JOIN @Group g

  ON g.tourID = t.tourID

  AND g.groupCancelFlag <> 1

  LEFT JOIN @Journey j

  ON j.groupID = g.groupID

  WHERE t.tourCancelFlag <> 1

  GROUP BY t.tourID

  , t.tourName

  Hey there Null... [font="Arial Black"]Please [/font]don't take this the wrong way... look at what some of the others have posted and really think about why they answered in such a fashion. Although it's highly admirable for you to post all that code, the op has done nothing to help himself. The great "crime" here is that the op has likely learned nothing in the process.... not even how to ask a question. 😉 Save the code and the effort for people who try on their own.

  --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)
  

• null-512060

  SSChasing Mays

  Points: 613

  More actions

  January 2, 2009 at 4:50 pm

  #920604

  Will do.

• @nks

  Mr or Mrs. 500

  Points: 500

  More actions

  January 3, 2009 at 2:44 am

  #920662

  Great !! Hats off !!

  It's working.

  Thanks & Regards.