SQL Joins

I'm having issues understanding joins...

This is where I'm at right now but it's not working:

SELECT
      T_PORCOMP.SP_ID,
      T_PLITEM.ITEMTAG,
      T_DRAWING.DNR

FROM
      SCHEMA1.T_PORCOMP,
      SCHEMA1.T_PLITEM,
      SCHEMA1.T_DRAWING,
      INNER JOIN SCHEMA1.T_DRAWING ON SCHEMA1.T_REPRESENTATION.SP_DRAWINGID = SCHEMA1.T_DRAWING.SP_ID

WHERE
      T_PLITEM.ITEMTAG LIKE '50%' AND
      T_DRAWING.DNR ='1'



tables.gif
KingMooBotAsked:
Who is Participating?
 
SharathData EngineerCommented:
OP_Zaharin - You missed the ON keyword.
damerval - INNER JOIN is ANSI syntax and Oracle supports ANSI syntax.
KingMooBot - You can use any of these.
SELECT T_PORCOMP.SP_ID, 
       T_PLITEM.ITEMTAG, 
       T_DRAWING.DNR 
  FROM T_PORCOMP 
       INNER JOIN T_DRAWING 
         ON T_REPRESENTATION.SP_DRAWINGID = T_DRAWING.SP_ID 
       INNER JOIN T_PLITEM 
         ON T_DRAWING.SP_ID = T_PLITEM.SP_ID 
 WHERE T_PLITEM.ITEMTAG LIKE '50%' 
       AND T_DRAWING.DNR = '1'  

Open in new window

SELECT T_PORCOMP.SP_ID, 
       T_PLITEM.ITEMTAG, 
       T_DRAWING.DNR 
  FROM T_PORCOMP, 
       T_DRAWING, 
       T_PLITEM 
 WHERE T_PLITEM.ITEMTAG LIKE '50%' 
       AND T_DRAWING.DNR = '1' 
       AND T_REPRESENTATION.SP_DRAWINGID = T_DRAWING.SP_ID 
       AND T_DRAWING.SP_ID = T_PLITEM.SP_ID  

Open in new window

0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi
"inner join" is T-SQL, not Oracle SQL. For Oracle joins, just use the following syntax:

SELECT table1.field1, Table2.field2
FROM Table1, Table2
WHERE Table1.id = Table2.foreignId

HTH

Philippe
0
 
KingMooBotAuthor Commented:
I need to find the record where a value in T_DRAWING.DNR ='1'.

But to find that I need to use the T_PORCOMP.SP_ID to get the T_REPRESENTATION.SP_DRAWINGID. Once I have that then I can use it in T_DRAWING to find the matching T_DRAWING.SP_ID. Then I need to use the value from a field there in the where clause (T_DRAWING.DNR)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Philippe DamervalSenior Analyst ProgrammerCommented:
It's confusing from what you write what keys are where.
It seems that
T_PORCOMP contains SP_ID, which is also contained by T_DRAWING, in which you want to filter DNR=1.
What is the role of T_REPRESENTATION?

Philippe
0
 
OP_ZaharinCommented:
- an what is the matching ID to SCHEMA1.T_PLITEM? i assume T_PLITEM.SP_ID or change it to what ID it suppose to match to, either to SP_DRAWINGID or SP_ID

SELECT
      T_PORCOMP.SP_ID,
      T_PLITEM.ITEMTAG,
      T_DRAWING.DNR

FROM
      T_PORCOMP
      INNER JOIN T_DRAWING
              T_REPRESENTATION.SP_DRAWINGID = T_DRAWING.SP_ID
      INNER JOIN T_PLITEM
              T_DRAWING.SP_ID = T_PLITEM.SP_ID
WHERE
      T_PLITEM.ITEMTAG LIKE '50%' AND
      T_DRAWING.DNR ='1'

0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Er, OP_Zaharin, this guy is using Oracle. Oralce doesn't support the INNER JOIN syntax.
0
 
OP_ZaharinCommented:
- probably true damerval, if he is using oracle 8i and older. inner/outer join do exist on the older version but its not on ANSI standard so they use (+)/(-). 9i up to the latest version, Oracle SQL in on ANSI standard.

http://www.orafaq.com/wiki/Join
0
 
OP_ZaharinCommented:
- there's no (-) actually. just (+) and =
i really can't remember the old syntax back then :)
0
 
OP_ZaharinCommented:
- oh yea i miss the ON. t for highlighting it Sharath_123 :)

SELECT
      T_PORCOMP.SP_ID,
      T_PLITEM.ITEMTAG,
      T_DRAWING.DNR

FROM
      T_PORCOMP
      INNER JOIN ON T_DRAWING
              T_REPRESENTATION.SP_DRAWINGID = T_DRAWING.SP_ID
      INNER JOIN ON T_PLITEM
              T_DRAWING.SP_ID = T_PLITEM.SP_ID
WHERE
      T_PLITEM.ITEMTAG LIKE '50%' AND
      T_DRAWING.DNR ='1'
0
 
OP_ZaharinCommented:
uh. wrong again. Sharath_123 would work for you KingMooBot.
0
 
GhunaimaCommented:
OP Zaharin you put the ON in the wrong place. It is to be palced after table name & before join condition. Like This

 
SELECT T_PORCOMP.SP_ID, 
  T_PLITEM.ITEMTAG,
  T_DRAWING.DNR
FROM 
  T_PORCOMP 
  INNER JOIN T_DRAWING ON 
      T_REPRESENTATION.SP_DRAWINGID = T_DRAWING.SP_ID
  INNER JOIN T_PLITEM ON 
      T_DRAWING.SP_ID = T_PLITEM.SP_ID
WHERE 
      T_PLITEM.ITEMTAG LIKE '50%' AND
      T_DRAWING.DNR ='1'

Open in new window

0
 
hnasrCommented:
"I'm having issues understanding joins..."
Try this article covering inner joins.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.