Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 933
  • Last Modified:

SQL Joint statement

I have three tables from where I am trying to get data.
table 1- groups
table 2- sub-groups
table 3 (reference table) - people_groups

Table 1 - structure  
 Field Type Collation Attributes Null Default Extra Action
  group_id int(11)   No  auto_increment              
  sub_g_id int(11)   No                
  group_name tinytext latin1_swedish_ci  No    

 Table 2 - Structure
  sub_group_id int(11)   No  auto_increment              
  group_id int(11)   Yes NULL                
  sgroup_name varchar(50) latin1_swedish_ci  Yes NULL                

Table 3 struction:  
Field Type Collation Attributes Null Default Extra Action
  people_id int(11)   No                
  group_id int(11)   No                
  sub_group_id int(11)   No    

Only thing that I have is people_id and I am trying to get the data based on the data that I have in the reference table ( people_groups )
I ahve ID's of people_id, groups_id, and sub_group id's saved in that table. Now I am trying to get group names adn sub-group names based of those ids.
Thanks.
0
martyje
Asked:
martyje
  • 3
  • 2
  • 2
1 Solution
 
martyjeAuthor Commented:
This is how far I am right now.
SELECT tbl_people_groups.group_id, tbl_people_groups.sub_group_id, tbl_groups.group_name, tbl_sub_groups.sgroup_name FROM tbl_groups, tbl_sub_groups, tbl_people_groups

Need to add appropriate JOIN Statement that's where I am stuck at.
0
 
Steve BinkCommented:
You actually have a JOIN in your current statement.  It's just implicit.  If you want an explicit JOIN:

SELECT a.group_id, a.sub_group_id, b.group_name, c.sgroup_name
FROM tbl_people_groups a INNER JOIN tbl_groups b ON a.group_id=b.group_id
          INNER JOIN  tbl_sub_groups c ON a.sub_group_id=c.sub_group_id
0
 
Scripting_GuyCommented:
That's fairly easy. Here is the statement. Be aware that you may get more than just one result.
SELECT
	peoplegroups.people_id,
	peoplegroups.group_id,
	peoplegroups.sub_group_id,
	groups.group_name,
	subgroups.sgroup_name
FROM
	people_groups as peoplegroups
JOIN
	groups as groups
ON
	groups.group_id = peoplegroups.group_id
 
JOIN
	sub-groups as subgroups
ON
	subgroups.group_id = peoplegroups.sub_group_id
 
WHERE
	peoplegroups.people_id = $peopleid
	
	

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Scripting_GuyCommented:
I made a mistake in line 17. It should read

subgroups.sub_group_id = peoplegroups.sub_group_id

instead of

subgroups.group_id = peoplegroups.sub_group_id
0
 
Steve BinkCommented:
That's the same query...
0
 
Scripting_GuyCommented:
I posted one minute after you, I didn't see your post yet when I was replying to the original question.
0
 
martyjeAuthor Commented:
Thanks a lot and sorry for the lil delay. appreciated...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now