Solved

SQL Joint statement

Posted on 2008-10-23
7
891 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:Scripting_Guy
Comment Utility
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
Comment Utility
That's the same query...
0
 
LVL 3

Expert Comment

by:Scripting_Guy
Comment Utility
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
Comment Utility
Thanks a lot and sorry for the lil delay. appreciated...
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now