Multiple object records forming one query

[Phrenetic]

Heya, what i would like to do in one query is pull out two objs, that are very similar from the same table "ol_obj_data"

These two objs are "ratings" and "reviews" they are not linked except for a USER_ID of who submitted them. But they relate to our business data.

ie, if a user has submitted a review (cat_id=8) and a rating (cat_id=9) into table "ol_obj_data", then the business is considered connected to the user.

SELECT	od.cat_id,od.obj_id,od.date_added,od.date_updated,od.data,bc.title,bc.business_contacts_id,gde.addre ss_string
FROM ol_obj_data od
INNER JOIN ol_obj_2_business o2b ON od.obj_id = o2b.obj_id
INNER JOIN ol_business_contacts bc ON o2b.business_contacts_id = bc.business_contacts_id
INNER JOIN ol_geo_data_ext gde on gde.geo_id=bc.geo_id
WHERE od.uid = USER_ID AND od.cat_id = 8

This query selects all the business records connected to a user which also have a rating by that user in ol_obj_data.

What i need to do is have a query that can

Select those same business records connected to the user, if that user has not only a rating, but also a review in "ol_obj_data" but no idea how to combine that together.

ie. if theres a obj with cat_id=8, and a obj with cat_id=9 both with same user_id, get business records

If that doesn't make sense im sorry and will try to explain it further.

thanks for any help

[dickersonka]

i'm not sure of your table structure but would you be able to achieve it with this at the end

WHERE od.uid = USER_ID AND (od.cat_id = 8 or od.cat_id=9)

[Phrenetic]

Hey, the problem with that is then it will select the records where there exists either A

Review (od.cat_id = 8)

or a

Rating (od.cat_id = 9)

What i need is to select those records when, there exists

Review (od.cat_id = 8) and Rating (od.cat_id = 9)

But you obviously cant use "WHERE od.uid = USER_ID AND (od.cat_id = 8 AND od.cat_id=9)
" because that will always evaluate to false, the category cat_id will never equal two different things.

[dickersonka]

then use two selects

the first one you have above, the second is the same just change od.cat_id = 9

do an inner join on these on uid and then you will have the uid's that are both 8 and 9

[Phrenetic]

yep thats what i need to do but have no idea how to write that kind of query. If you could provide an example of how i do two queries and join on their results it would be appreciated.

[dickersonka]

select cat_id,obj_id,date_added,date_updated,data,title,business_contacts_id,address_string, USERID

(SELECT	od.cat_id,od.obj_id,od.date_added,od.date_updated,od.data,bc.title,bc.business_contacts_id,gde.address_string, od.uid as USERID
FROM ol_obj_data od
INNER JOIN ol_obj_2_business o2b ON od.obj_id = o2b.obj_id
INNER JOIN ol_business_contacts bc ON o2b.business_contacts_id = bc.business_contacts_id
INNER JOIN ol_geo_data_ext gde on gde.geo_id=bc.geo_id
WHERE od.uid = USER_ID AND od.cat_id = 8 ) as query1

inner join

(SELECT	od.cat_id,od.obj_id,od.date_added,od.date_updated,od.data,bc.title,bc.business_contacts_id,gde.address_string, od.uid as USERID
FROM ol_obj_data od
INNER JOIN ol_obj_2_business o2b ON od.obj_id = o2b.obj_id
INNER JOIN ol_business_contacts bc ON o2b.business_contacts_id = bc.business_contacts_id
INNER JOIN ol_geo_data_ext gde on gde.geo_id=bc.geo_id
WHERE od.uid = USER_ID AND od.cat_id = 9 ) query2

on query2.USERID = query1.USERID

you must specifiy each column using 'AS' like the one for userid

[Phrenetic]

hmmm wasn't to sure about your query, couldn't get it to work, so i wound up going with this as a solution.

Seems to work fairly well and isn't to slow, even though it is 3 queries.

SELECT	od.cat_id,
        od.obj_id,
        od.date_added,
        od.date_updated,
        od.data,
        bc.title,
        bc.business_contacts_id,
        gde.address_string
FROM 
    ol_obj_data od
    INNER JOIN ol_obj_2_business o2b ON od.obj_id = o2b.obj_id
    INNER JOIN ol_business_contacts bc ON o2b.business_contacts_id = bc.business_contacts_id
    INNER JOIN ol_geo_data_ext gde on gde.geo_id=bc.geo_id
WHERE od.uid = ".$user->id." 
    AND (
        o2b.business_contacts_id NOT IN (
            SELECT o2b.business_contacts_id 
            FROM 
                ol_obj_data od 
                INNER JOIN ol_obj_2_business o2b ON od.obj_id = o2b.obj_id 
            WHERE od.uid = ".$user->id." AND od.cat_id =7
        ) 
        OR
        o2b.business_contacts_id NOT IN (
            SELECT o2b.business_contacts_id 
            FROM 
                ol_obj_data od 
                INNER JOIN ol_obj_2_business o2b ON od.obj_id = o2b.obj_id 
            WHERE od.uid = ".$user->id." AND od.cat_id =8
        ) 
    )
ORDER BY bc.title ASC, od.date_added DESC, od.obj_id DESC";