CODEWITHSUNDEEP
mysqlsql
TryHarder
TryHarder

Reputation: 750

complex sql query issue

I have a little SQL but I can't find the way to get back text just numbers. - revised!

SELECT if( `linktype` = "group", 

(SELECT contactgroups.grname
FROM contactgroups, groupmembers
WHERE contactgroups.id = groupmembers.id ???
AND contactgroups.id = groupmembers.link_id),

(SELECT contactmain.contact_sur
FROM contactmain, groupmembers
WHERE contactmain.id = groupmembers.id ???
AND contactmain.id = groupmembers.link_id) ) AS adat

FROM groupmembers;

As now I have improved a bit gives back some info but ??? (thanks to minitech) indicate my problem. I can't see how could I fix... Any advice welcomed! Thansk

Contactmain (id, contact_sur, email2)

data:
1 | Peter | [email protected]
2 | Andrew| [email protected]

Contactgroups (id, grname)

data:
1 | All
2 | Trustee
3 | Comitee

Groupmembers (id, group_id, linktype, link_id) 

data:
1 | 1 | contact | 1
2 | 1 | contact | 2
3 | 2 | contact | 1
4 | 3 | group   | 2

And I would like to list out who is in the 'Comitee' the result should be Andrew and Trustee if I am right:)

Upvotes: 0

Views: 71

Answers (2)

DRapp
DRapp

Reputation: 48179

It does look a bit redundant on the join since you are implying both the ID and Link_ID columns are the same value. Since BOTH select values are derived from a qualification to the group members table, I have restructured the query to use THAT as the primary table and do a LEFT JOIN to each of the other tables, anticipating from your query that the link should be found from ONE or the OTHER tables. So, with each respective LEFT JOIN, you will go through the GroupMembers table only ONCE. Now, your IF(). Since the group members is the basis, and we have BOTH tables available and linked, we just grab the column from one table vs the other respectively. I've included the "linktype" too just for reference purposes. By using the STRAIGHT_JOIN will help the engine from trying to change the interpretation of how to join the tables. 

SELECT STRAIGHT_JOIN
      gm.linktype,
      if( gm.linktype = "group", cg.grname, cm.contact_sur ) ADat
   from
      groupmembers gm
         left join contactgroups cg
            ON gm.link_id = cg.id
         left join contactmain cm
            ON gm.link_id = cm.id

Upvotes: 1

Ry-
Ry-

Reputation: 225281

If contactgroups.id must equal groupmembers.id but must also equal 2, that's redundant and also probably where your problem is. It works fine as you've written it: http://ideone.com/7EGLZ so without knowing what it's actually supposed to do I can't help more.

EDIT: I'm unfamiliar with the comma-separated FROM, but it gives the same result since you don't select anything from the other table so it doesn't really matter.

Upvotes: 1

Related Questions