Sql query number of occurance

Question

Hi,

I wanna to have query:

Select cars.* from cars where cars.code in
(
select carCode from articles where 
numberofrecords with this car (it is not a column) >1
and Accepted=1
order by date
)

How to write it?

Answer 1

Try something like this:

SELECT  cars.*
FROM    cars
WHERE   cars.code IN (
    SELECT  carCode
    FROM    articles
    WHERE   Accepted = 1
    GROUP BY carCode
    HAVING COUNT(articleId) > 1
)

Answer 2

This should do it:

SELECT c.*
FROM cars c
    JOIN
    (
        SELECT carCode 
        FROM articles
        WHERE Accepted = 1
        GROUP BY carCode
        HAVING COUNT(carCode) > 1
    ) a ON c.code = a.carCode