CODEWITHSUNDEEP
sqloracle-databasejoinoracle10g
user9364289
user9364289

Reputation: 11

Inner Join and Count throwing up errors

I am still a bit of a newbie when it comes to SQL and I am wondering if you can assist. I have been asked to join two tables (which I assumed was an inner join) and count and group the results.

The code I have written is:

SELECT publisher.name, printjob.JobNo, r.CountPO
    FROM Publisher, PrintJob
    INNER JOIN  (SELECT purchaseorder.orderno count (*) as CountPO, FROM purchaseorder
GROUPBY publisher.name) as r ON 
as ORDER BY publisher.name;

But it states that FROM is not where it is supposed to be. I am not sure if thats the case, any advice would be much appreciated.

Ta!

EDIT: Ok, I have corrected the above to:

SELECT PrintJob.JobNo, count (*) as POS FROM PurchaseOrder INNER JOIN PrintJob WHERE purchaseorder.printjob = printjob.jobno GROUP BY publisher.name;

Still gotten nowhere.

cries

Upvotes: 1

Views: 55

Answers (1)

Alex
Alex

Reputation: 741

you have coma in wrong place:

SELECT purchaseorder.orderno, count (*) as CountPO FROM purchaseorder
GROUPBY publisher.name

And you need group by in inner query. And you need some join condition after ON.

Also it is not nice to use 2 types of join in one query:

FROM Publisher, PrintJob

should do everywhere via join:

FROM Publisher as p
inner join PrintJob as pj on [some condition]
INNER JOIN  (SELECT purchaseorder.orderno count (*) as CountPO, FROM purchaseorder
GROUPBY publisher.name) as r ON [some condition]

Upvotes: 1

Related Questions