[SOLVED] simple mysql query.. :/

[melvincr]

my problem is that i've a table with pictures, one for attributes and one to connect them. for a search function i would like to select all pictures which have the same attributes.

 

ie:

SELECT * FROM pictures a, attributes b, pic2att c WHERE a.id = c.picid AND c.addi = b.id AND b.id = 10 AND b.id = 12

 

this works for one attribute but not for two..  ???

[bubblegum.anarchy]

SELECT * 
FROM pictures a
   , attributes b
   , pic2att c 
WHERE a.id = c.picid 
   AND c.addi = b.id 
   AND ( b.id = 10 OR b.id = 12 )

[melvincr]

if i have..

 

id, attid

1, 10

1, 23

2, 10

3, 23

 

this query also returns picture 2 and 3.. but i just want to have the pictures that have this two (or more) ids in the pic2att table..

 

 

 

 

[Wildbug]

Maybe this

 

SELECT *
FROM pictures AS a
JOIN (SELECT * FROM pic2att WHERE att IN (10,23) GROUP BY picid HAVING COUNT(*) = 2) AS c
ON a.id=c.picid
JOIN attributes AS b
ON b.id=c.attid

[fenway]

Maybe this

 

SELECT *
FROM pictures AS a
JOIN (SELECT * FROM pic2att WHERE att IN (10,23) GROUP BY picid HAVING COUNT(*) = 2) AS c
ON a.id=c.picid
JOIN attributes AS b
ON b.id=c.attid

 

Where's the count?

[Wildbug]

Where's the count?

 

One, two IDs, HA HA HA!

 

The subquery will only return picid's having both attributes due to the "HAVING COUNT(*)=n" portion where "n" must equal the number of elements in the IN() set.  So if there are three attributes, IN(12,90,4), COUNT(*) should = 3, etc.  If not all attributes are found for any given picid, then it won't make it out of the subquery because the COUNT(*) won't equal the number of IN elements.

 

There are at least three ways I can think of to do this query.  The above might have been the easiest.  The first that came to mind was to use JOINs, but doing this for an arbitrarily large n of attributes will quickly become unweildy.  The other would be to make either a set or a string with GROUP_CONCAT and search it with FIND_IN_SET or RLIKE, respectively, but I think the above is faster.

[Illusion]

will it works

SELECT * FROM pictures id=ANY (select picid from c inner join (select id from b where b=10 or b=23) on c.addi = b.id )

 

may be I am not imagined what you want.................

[melvincr]

thx fenway that was the next question

 

thank you very much!! it's working 

 

another thing happened when i added SELECT DISTINCT SQL_CALC_FOUND_ROWS to count the rows.. it just made the mysqld feel funny.. (meaning nothing worked anymore..)

[Wildbug]

What's wrong with PHP's mysql_num_rows() for counting the number of rows returned?

 

(And I should have said it in the earlier post since it may not have been self-evident, but you'll need to make COUNT(*)=??? equal to the number of attributes you will be seeking when you assemble the query.)

[melvincr]

the problem is that the query has a limit so mysql_num_rows()  will just return ie 10 but i need to know the count for the pagination..

 

[Wildbug]

Oh, I understand.

 

Did you check for errors on that query?  If so, was an error returned, and what was it?

[melvincr]

no error no output.. and it never happened before.. the database is not extremely big (pictures 6000, techniques 40, att2pic 40000 entries) no idea how to solve this..

[Wildbug]

Can you post the whole query that you're using, including LIMIT and SQL_CALC....?

[melvincr]

SELECT DISTINCT SQL_CALC_FOUND_ROWS *

FROM art_picture a

JOIN

(SELECT * FROM art_tech2pic WHERE techid IN ( 35, 34, 16) GROUP BY picid HAVING COUNT(*) = 3) AS f ON a.id = f.picid

JOIN art_technique AS g ON g.id = f.techid

LIMIT 0, 10

 

this is working.. the query that killed the server was

SELECT DISTINCT SQL_CALC_FOUND_ROWS *

FROM art_picture a, art_style b, art_technique c, art_tech2pic d, art_user e

WHERE c.id= '35'

AND e.uabout LIKE '%test%'

OR  e.uname LIKE '%test%'

 

i don't get any response from this query..

 

 

 

[Wildbug]

So the top one's working?  That means you're okay, right?

 

The bottom one may have had some other error.  The '%test%' conditions should be enclosed in parentheses since AND binds more tightly than OR.  Of course, you should still get results unless those conditions are not meetable.

[melvincr]

yes i'm fine thxs for the help!

[fenway]

Where's the count?

 

One, two IDs, HA HA HA!

 

The subquery will only return picid's having both attributes due to the "HAVING COUNT(*)=n" portion where "n" must equal the number of elements in the IN() set.  So if there are three attributes, IN(12,90,4), COUNT(*) should = 3, etc.  If not all attributes are found for any given picid, then it won't make it out of the subquery because the COUNT(*) won't equal the number of IN elements.

 

There are at least three ways I can think of to do this query.  The above might have been the easiest.  The first that came to mind was to use JOINs, but doing this for an arbitrarily large n of attributes will quickly become unweildy.  The other would be to make either a set or a string with GROUP_CONCAT and search it with FIND_IN_SET or RLIKE, respectively, but I think the above is faster.

 

*SHUDDER*

 

First, asking for * with a GROUP BY for a single column is bad form... especially because you're using picid (which is obviously the same for all aggreated rows) and attid (which may be, but isn't clear to me)... MySQL will let you do this, and shouldn't -- it feels very unstable to me.

 

Second, I can't stand that you can use expressions in the HAVING clause that aren't in the SELECT column list.

 

Not that I haven't used that "having count() = n" trick many times before, I've just written it very differently.

[Wildbug]

First, asking for * with a GROUP BY for a single column is bad form... especially because you're using picid (which is obviously the same for all aggreated rows) and attid (which may be, but isn't clear to me)... MySQL will let you do this, and shouldn't -- it feels very unstable to me.

 

I agree.  melvin should only take what he uses.  I left it out because I don't necessarily know what he'll be drawing on.

[melvincr]

what do you mean i should use what i use?

 

what's wrong with the HAVING COUNT(*)=n  what can i do else?

[fenway]

what do you mean i should use what i use?

 

what's wrong with the HAVING COUNT(*)=n  what can i do else?

 

You don't use all of the non-aggreagated columns from the dervied table.

[Wildbug]

what do you mean i should use what i use?

 

We mean that instead of retreiving everything (*) from the database, you should only have the columns you'll actually use in PHP in the column list.  For instance, if you just want the picture id from art_picture and (I'm making this up) the name of the technique from art_technique, you should just use "SELECT DISTINCT SQL_CALC_FOUND_ROWS a.id, g.name FROM...."  But since we don't know what you'll be using, it'll be up to you to make that change.  It's more efficient to only request what you use, and it's also prone to less confusion.  Say you have an "id" column in art_picture and an "id" column in art_technique.  If you get everything and use mysql_fetch_assoc() and use $row['id'], you will get the last column by that name, art_technique.id instead of art_picture.id since the first "id" element will be overwritten by the second in PHP.

 

what's wrong with the HAVING COUNT(*)=n  what can i do else?

 

Nothing's wrong with it in practice.  In "strict" SQL, it's not "proper" to reference something in the HAVING (or GROUP or ORDER) clauses that's not also in the column list.  In other words, we didn't bother using something like "SELECT picid, techid, COUNT(*) AS gcount FROM ..... HAVING gcount=2".  See where we defined COUNT(*) as a column in the SELECT clause and then later referenced it in the HAVING clause?  That's "proper" SQL, as far as I understand it.

[melvincr]

ok now i see

 

thank you!