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

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)

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.

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

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.

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

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";
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.