Subquery Help

[adam84]

SELECT pictureLink FROM userPictures, (SELECT count(pictureID) FROM userPictures WHERE userID = 11 AND relType = 1 AND pictureGallery = 1 LIMIT 1) as galCount, 
(SELECT count(pictureID) FROM userPictures WHERE userID = 11 AND relType = 1 AND picturePicture = 1 LIMIT 1) as priCount 
WHERE userID = 11 AND relType = 1 LIMIT 1

 

When I run this query, all I get returned is the pictureLink.

If i ran the last two sub queries, i get a value of 1 returned.

 

How come, when it is all put together, only the pictureLink row gets returned?

[bluejay002]

SELECT pictureLink FROM

 

You will definitely only get pictureLink here. What is it that you want to achieve?

[adam84]

1 - get the pictureLink. I am able to do that

2 - I want to see if there is at least 1 gallery picture for a certain user and certain relationship

3 - I want to see if there is at least 1 private picture for a certain user and certain relationship

 

I can do it in different queries, but I just thought one query would be easier

[bluejay002]

why not use INNER JOIN for that instead?

 

if you can post the structure of the tables and the rules for what you are aiming at... maybe I can help you then.

[adam84]

This is the table structure

 

Table userPictures:

 

pictureID - int auto

userID - int

relType - int  - a picture can be used in different profiles (we can just use 1)

pictureLink - varchar  - url of pic

pictureMain - int    - is this picture the main picture

pictureGallery - int - is this picture a gallery picture

picturePrivate - int - is this picture a private picture

pictureCreated - datetime

 

Ok, what I want returned is three different things.

1 - the 'pictureLink' of a 'pictureMain'

2 - is there at least 1 gallery picture? - Hence the count(pictureID)

3 - is there at least 1 private picture? - Hence the count(pictureID)

 

 

 

SELECT pictureLink FROM userPictures, 

(SELECT count(pictureID) FROM userPictures WHERE userID = 11 AND relType = 1 AND pictureGallery = 1 LIMIT 1) as galCount, 

(SELECT count(pictureID) FROM userPictures WHERE userID = 11 AND relType = 1 AND picturePrivate = 1 LIMIT 1) as priCount 

WHERE userID = 11 AND relType = 1 AND pictureMain = 1 LIMIT 1

 

[bluejay002]

Okay, so let me rephrase it and see if I get you right.

 

What you want to do is to get the main picture for a certain user. Also, to check whether a user has a gallery picture or not, and whether a user has a private picture or not, am I correct?

 

We can use UNION here instead.

 

NOTE: For pictureMain, there should be only one row for this so you wont need to use LIMIT since the result then would be ambiguous. Also, you do not need LIMIT for COUNT since the result then would only be one.

 

SELECT 'mainpic' as descr, 
pictureLink as setoff
FROM userPictures as setA
WHERE userID = 11 
AND relType = 1 
AND pictureMain = 1
UNION 
SELECT 'gallery' as descr, 
COUNT(pictureID) as setoff
FROM userPictures as setB
WHERE userID = 11 
AND relType = 1 
AND pictureGallery = 1
UNION 
SELECT 'private' as descr, 
COUNT(pictureID) as setoff
FROM userPictures as setC
WHERE userID = 11 
AND relType = 1 
AND picturePrivate = 1

 

cheers,

 

Jay