problem combining data from multiple tables.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem combining data from multiple tables.

Author

Topic

Stefan
Starting Member

11 Posts

Posted - 2006-04-02 : 09:29:40

Hey,
I have 3 tables:
match(id,matchtype,time,date) -- loads of other fields
groups(id,name,description)
match_group(id,match_id,group_id)

In my project people can add new matches/games to the database. Those matches will then appear on the "upcomming events" page of my website. However, the matches are only visible to people that are member of a certain group. For every match its specified which groups can, and which groups cannot see the match on the "upcomming events" page. This is specified in the "match_group" table. So if the match_group table had the following records:


id           match_id      group_id
1            10            5
2            10            6

This would mean that the match 10 would be visible only to the groups with id 5 and 6.

Now my problem is that on the upcoming event page i want to show each match like this:

Match 1 (id=5)
time: 23:30
date: 2nd of April
matchtype: league match
For groups: <here the names of the groups specified in the match_group table>

Match 2 (id=6)
time: 22:30
date: 8th of April
matchtype: league match
For groups: <here the names of the groups specified in the match_group table>

etc.

Here is the code i currently use to retrieve all data except the group names:


SELECT match.id,
       match.date,
       match.time,
       matchtypes.name AS matchtype
FROM   match
INNER
       JOIN matchtypes
	   ON matchtypes.id = match.matchtype

I just dont know how i can retrieve ALL the group names related to each match.
So what i still need now is to get the group id's from the match_group table and with that id get the name from the groups table.

I hope i made my problem clear enough for you to understand

and i hope you can give me a solution.

Thanks in advanace

PS. I use PHP to display the data on my website.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-02 : 10:26:27

I couldn't find a table "matchtypes" ??

How About:


SELECT m.id,
       m.date,
       m.time,
       mg.name, 
       g.name 
FROM   match m
INNER  JOIN match_group mg ON mg.match_id = m.id
INNER  JOIN groups g ON mg.group_id = g.id

or u may need to use 2 queries
Qry 1: To get Match details (Only Match table is involved)
Qry 2: To get Groups related to a match (Join Match table to Groups with the Match_Group table

Srinika

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-02 : 10:27:30

Does these works??

SELECT match.id,
match.date,
match.time,
matchtypes.name AS matchtype
Group.*
FROM match
INNER
JOIN matchtypes
ON matchtypes.id = match.matchtype Inner join
(Select Group.*,Match_Group.Match_ID From Group Inner Join Match_Group On Group.[ID] = Match_Group.GroupID ) Group On Group.Match_ID = Match.[ID]

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Stefan
Starting Member

11 Posts

Posted - 2006-04-02 : 11:15:28

Thanks for the replies :)

Srinika, i think your first suggestions would return a result looking like this:


m.id    m.date         m.time       g.name
1       2006-04-03     13:30:00     groupname1
1       2006-04-03     13:30:00     groupname2
2       2006-05-20     14:30:00     groupname1
2       2006-05-20     14:30:00     groupname2
2       2006-05-20     14:30:00     groupname3
etc.

I dont know how i would display all match details just once with all the groups related to that match. So i think you're right that i have to do 2 queries.

But i dont know how i should do your second suggestion:

quote:
Qry 1: To get Match details (Only Match table is involved)
Qry 2: To get Groups related to a match (Join Match table to Groups with the Match_Group table

Because i will have 2 recordsets then (1 with group names and 1 with the other match data) with a different amount of records in it. Example:
recordset match details:


id      date           time
1       2006-04-03     13:30:00 
2       2006-05-20     14:30:00

The group name recordset will have 5 records in this example (2 records for match 1 and 3 records for match 2, offcourse this depends on how many groups were added for those matches).

I think my question is going more in the direction of PHP now so i dont know if i'm still on the right place. Using PHP, i loop through the recordset with the match details and within that loop i have to do another loop to get through the recordset with the group names.

Maybe i could do that like this:


while($match = mysql_fetch_array($recset_match_details)){
   echo "id: " . $match['id'];
   // display match details.
   echo "groups: ";
   while(< here a statement to "fetch" only the group names that have the id: $match['id']){
       echo $the_group_names;
    }
}

Heh, i think i should better move on to a PHP forum now

chiragkhabaria, i will take a look at your code now :)

Thank you both for your replies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources