CODEWITHSUNDEEP
sqljoin
user3438489
user3438489

Reputation: 319

sql joins how to join multiple tables

I have 3 tables: medicationorder, Ordercatalog and Person

medicationOrder (
    startdate, 
    enddate,
    catalogId,
    orderId,
    personId)    

ordercatalog (
    catalogId, 
    drugId,
    IsGeneric,
    Brand)

Person (firsstname,
    lastname, 
    dob,
    personId)

I want to retrieve all medication orders for the patient with last name "cat"... I tried this.. can you please tell me where I'm going wrong.. Thanks..

select *
from ordercatalog as o, person as p, medicationorder as m

join ordercatalog on o.CatalogId= m.CatalogId, 

where (p.PersonId= m.PersonId and p.LastName= "Cat");

Upvotes: 1

Views: 81

Answers (3)

crthompson
crthompson

Reputation: 15865

You've combined two different SQL standards.

The older standard called SQL89:

select 
  *
from 
  ordercatalog as o, 
  person as p,
  medicationorder as m
where
  o.CatalogId= m.CatalogId
  and p.PersonId= m.PersonId 
  and p.LastName= "Cat"

Notice that all the joins are handled in your where clause.

Conversely, you can do it based on the SQL92 standard that puts your joins in the from clause:

select
    *
from
    ordercatalog o
    inner join medicationorder m on o.CatalogId = m.CatalogId
    inner join person p on m.PersonId = p.PersonId and p.LastName = "Cat"

You should find that every platform out these days supports the SQL92 standard. But most will support both.

Upvotes: 0

JDGuide
JDGuide

Reputation: 6525

Try this :-

 select * from medicationorder m 
inner join person p on p.PersonId = m.PersonId
inner join ordercatalog o on m.CatalogId=o.CatalogId where p.LastName = 'Cat';

Hope it will help you.

Upvotes: 1

VJ Hil
VJ Hil

Reputation: 904

            SELECT * FROM 
            ORDERCATALOG O INNER JOIN MEDICATIONORDER M
            ON O.CATALOGID=M.CATALOGID
            INNER JOIN PERSON P
            ON P.PERSONID=M.PERSONID 
            AND P.LASTNAME='CAT';

Upvotes: 1

Related Questions