Solved

SQL Joint statement

Posted on 2008-10-23
7
898 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 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