1 Replies - 4682 Views - Last Post: 24 November 2012 - 01:25 PM

#1 rollinhard  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 13-February 05

SQL*PL Query

Posted 15 November 2012 - 01:34 PM

I'm having some issues getting the correct outcome for this query. When I enter this code :

 SQL> select dr_drvname, ic_cabnum, ic_incitype
  2  from (driver JOIN shift ON sh_drvnum = dr_drvnum) JOIN incident ON ic_wkdate = sh_wkdate
  3  where ic_wkdate = '15-NOV-2007'
  4  order by dr_drvnum;



I get the correct selections but I am getting multiples for each incident type.

DR_DRVNAME IC_ IC_INCITYPE
------------------------------ --- ------------
Pettit, R. Richardson 215 TICKET
Pettit, R. Richardson 144 TICKET
Pettit, R. Richardson 144 TICKET
Pettit, R. Richardson 144 TICKET
Pettit, R. Richardson 455 TICKET
Hickman, Ronald S. 215 TICKET
Hickman, Ronald S. 215 TICKET
Hickman, Ronald S. 215 TICKET
Hickman, Ronald S. 144 TICKET
Hickman, Ronald S. 144 TICKET
Hickman, Ronald S. 144 TICKET

Why is this happening and what do I need to change?

Thanks

The output I should be getting based on my table should have only 6 incidents with no repeating information.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL*PL Query

#2 rollinhard  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 13-February 05

Re: SQL*PL Query

Posted 24 November 2012 - 01:25 PM

HOLY CRAP! I finally got it!!!! I probably should have posted tables as it would have probably been helpful. Code that pulled the correct output was
SQL> select dr_drvname, ic_cabnum, ic_incitype
  2  from (driver join shift on dr_drvnum = sh_drvnum)
  3  join incident on ic_wkdate = sh_wkdate and ic_wkshift = sh_wkshift and ic_cabnum = sh_cabnum
  4  where ic_wkdate = '15-NOV-2007';



For future reference when I am joining tables, do I need to show all fields that are the same from joining tables? Like with the join on incident and shift.

Thanks for making me work that out on my own. Pretty rewarding feeling that I figured it out myself.
This is a huge project for school so I'll probably be posting some more questions soon. Some of these queries are ridiculous and our book does a poor job in covering what is being asked in a majority of the queries.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1