SQL Query syntax, I want to use INNER JOIN

Question

I'm working on a windows application project using front end "vb.net" & back end "Ms Access" I have problem in wrinting sql query Actually there are 5 tables Transaction,items,itemtitle,itemtype & userinfo. check the following query & with this referance if u get idea then plz change in correct query Thanking You

SELECT 
TRANSACTIONS.ACCESSIONNO AS 
ACCESSIONNO,TRANSACTIONS.TYPEID,
TRANSACTIONS.CHECKOUTDATE AS CHECKOUTDATE,ITEMTITLE.ITEMTITLE,
TRANSACTIONS.CHECKEDOUTBY,
USERINFO.FULLNAME_ENG,
USERINFO.FULLNAME_MAR,
TRANSACTIONS.ACCOUNTNO,
ITEMTYPE.TYPES_MAR,
ITEMTYPE.TYPES_ENG 

FROM 
  TRANSACTIONS,ITEMTYPE,
  ITEMTITLE,
  USERINFO 

WHERE 
  TRANSACTIONS.ACCOUNTNO=USERINFO.ACCOUNTNO 
  AND TRANSACTIONS.ACCESSIONNO=ITEMS.ACCESSIONNO 
  AND ITEMS.ITEMTITLEID=ITEMTITLE.ITEMTITLEID 
  AND TRANSACTIONS.TYPEID=ITEMTYPE.TYPEID 
  AND TRANSACTIONS.STATUS='Enabled'

Please don't shout, it is unpolite. Also, breaking your code into multiple lines makes it more readable :-) — Péter Török
– Péter Török, Commented May 19, 2010 at 9:24
@Alex K.: ah, the joys of concurrent editing! Your changes overwrote mine, but I'm happy with yours remaining. — Frank Shearar
– Frank Shearar, Commented May 19, 2010 at 9:37

Frank Shearar · Accepted Answer · 2010-05-19 09:36:30Z

1

It looks like you left out the ITEMS table. The below joins to that table. At any rate, it demonstrates the INNER JOIN syntax. (Usually I use aliases for readability. I purposefully them out.)

SELECT TRANSACTIONS.ACCESSIONNO AS ACCESSIONNO, TRANSACTIONS.TYPEID, TRANSACTIONS.CHECKOUTDATE AS CHECKOUTDATE, ITEMTITLE.ITEMTITLE, TRANSACTIONS.CHECKEDOUTBY, USERINFO.FULLNAME_ENG, USERINFO.FULLNAME_MAR, TRANSACTIONS.ACCOUNTNO, ITEMTYPE.TYPES_MAR, ITEMTYPE.TYPES_ENG
FROM TRANSACTIONS
INNER JOIN ITEMTYPE ON (TRANSACTIONS.TYPEID = ITEMTYPE.TYPEID)
INNER JOIN ITEMTITLE ON (ITEMS.ITEMTITLEID = ITEMTITLE.ITEMTITLEID)
INNER JOIN USERINFO ON (TRANSACTIONS.ACCOUNTNO = USERINFO.ACCOUNTNO)
INNER JOIN ITEMS ON (TRANSACTIONS.ACCESSIONNO = ITEMS.ACCESSIONNO)
WHERE TRANSACTIONS.STATUS = 'Enabled'

answered May 19, 2010 at 9:36

Frank Shearar

17.2k8 gold badges72 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query syntax, I want to use INNER JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related