Closed Thread Icon

Topic awaiting preservation: sql question (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=32053" title="Pages that link to Topic awaiting preservation: sql question (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: sql question <span class="small">(Page 1 of 1)</span>\

 
Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 11-20-2010 15:43

Hi. I know the title is a BIT vague but who cares.

Following situation: I have two tables groups and contacts_to_groups
groups:
id
name

contactst_to_groups
contact_id
group_id

i want to querry the groups table but add an additional column, something like is within
I have the id of the contact that i am cheking against so the result should be something like:
id | name | iswithin
0 group1 false
1 group2 true
2 group3 true
4 group4 false

any idea how i could achive this? (musst be purely in SQL no php java etc.)

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 11-20-2010 17:08

well, your description is sparse and partially confusing,
but the answer will be http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join I feel.

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 11-20-2010 17:20

ok let me rephrase.

i have the table groups, which i want to querry "Select * FROM groups"
in the table contacts_to_groups i have the association between contacts and the group.
in my application i have the id of one contact. I want to add a new column to the resultset that tellls me if the contact i have is associated with the current group.

I hope that was more clear

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 11-20-2010 18:08

ok thanks i managed to figure it out (little workaround)

"SELECT groups._id, groups.name, " +
"(SELECT COUNT(contacts_togroups.contact_id) FROM contacts_togroups " +
"WHERE contacts_togroups.contact_id="+contact+" " +
"AND contacts_togroups._gid = groups._id) AS iswithin " +
"FROM groups"

id | name | iswithin
0 group1 0
1 group2 1
2 group3 1
4 group4 0

instead but it works

thanks for the help

« BackwardsOnwards »

Show Forum Drop Down Menu