Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Joins

Posted on 2011-05-06
12
Medium Priority
?
427 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:KingMooBot
12 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35710210
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
 

Author Comment

by:KingMooBot
ID: 35710240
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
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35710300
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35710308
- 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
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35710336
Er, OP_Zaharin, this guy is using Oracle. Oralce doesn't support the INNER JOIN syntax.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35710379
- 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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35710397
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35710399
- there's no (-) actually. just (+) and =
i really can't remember the old syntax back then :)
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35710402
- 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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35710409
uh. wrong again. Sharath_123 would work for you KingMooBot.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35711125
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
 
LVL 31

Expert Comment

by:hnasr
ID: 35713189
"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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

577 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