[ENG] mysql query question

Hello there,

I know this isn't a english NG, but I've no access to any good mysql
newsgroup, and since this is the best I've access to, I ask here.

I've a table in wich I store pictures of articles. I've a field where I
store the priority (let's say is the order the pictures are shown).

So I've those fields in the database: articleID, imagepath, priority.

So for article 455 I've this record:
455;455-1.jpg;1
455;455-2.jpg;2
455;455-3.jpg;3

Now some articles have missing numbers priority.
example article 24 has those records:
24;24-2.jpg;2
24;24-4.jpg;4

I've up to 5 pictures per article. How to retrieve with a query all
articlesID where I've priority 2 but not priority1 with mysql ?

Thanks for helping

Re: [ENG] mysql query question

Hi Bob

Bob Bedford wrote:
> I've a table in wich I store pictures of articles. I've a field where I
> store the priority (let's say is the order the pictures are shown).
> So I've those fields in the database: articleID, imagepath, priority.
> Now some articles have missing numbers priority.
> I've up to 5 pictures per article. How to retrieve with a query all
> articlesID where I've priority 2 but not priority1 with mysql ?

Try this:
select id from Pictures p1
left join Pictures p2
on p1.id=p2.id and p1.priority=2 and p2.priority=1
where p2.id is null;

I don't know, if this will work. But perhaps you will get an idea, how
to solve your problem.

HTH, Johannes

Re: [ENG] mysql query question

"Johannes Vogel" a écrit dans le message de news:
dupkoe$8i5$1@news.hispeed.ch...
> Hi Bob
>
> Bob Bedford wrote:
>> I've a table in wich I store pictures of articles. I've a field where I
>> store the priority (let's say is the order the pictures are shown).
>> So I've those fields in the database: articleID, imagepath, priority.
>> Now some articles have missing numbers priority.
>> I've up to 5 pictures per article. How to retrieve with a query all
>> articlesID where I've priority 2 but not priority1 with mysql ?
>
> Try this:
> select id from Pictures p1
> left join Pictures p2
> on p1.id=p2.id and p1.priority=2 and p2.priority=1
> where p2.id is null;
>
> I don't know, if this will work. But perhaps you will get an idea, how to
> solve your problem.
>
> HTH, Johannes
Thanks for your help Johannes, but I got an other answer from an other NG.
Seems simpler:
SELECT articleID FROM ArticleTable GROUP BY articleID HAVING
MIN(priority)='2';

Cheers.