Solved

SQL Joint statement

Posted on 2008-10-23
7
904 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Insert data into database 2 45
How to count in a table in php 22 45
change database name 2 36
Display images from mysql blob type (Not working) 9 43
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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