Solved

SQL Joint statement

Posted on 2008-10-23
7
909 Views
Last Modified: 2010-04-21
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
Comment
Question by:martyje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:martyje
ID: 22790478
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
 
LVL 50

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 22796212
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
 
LVL 3

Expert Comment

by:Scripting_Guy
ID: 22796226
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 3

Expert Comment

by:Scripting_Guy
ID: 22796281
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22796590
That's the same query...
0
 
LVL 3

Expert Comment

by:Scripting_Guy
ID: 22796974
I posted one minute after you, I didn't see your post yet when I was replying to the original question.
0
 

Author Closing Comment

by:martyje
ID: 31509418
Thanks a lot and sorry for the lil delay. appreciated...
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question