MySQL Forums
Forum List  »  Optimizer & Parser

Re: Join without duplicates
Posted by: Jamie M
Date: November 29, 2011 04:19AM

Hi,

Thanks for the reply but I'm not sure that will work. Maybe I didn't explain myself properly.

-Needed results-
I need to perform a SELECT query to get the competitorName and competitorDescription just once from the competitorTable and multiple scores for that competitor from the scoresTable and somehow also join this with the eventsTable to display on a webpage like so:


Competitor Name=Micrsoft
Competitor Description=Microsoft company description

Scores
+------------+-------------+
| Event Name | Event Score |
+------------+-------------+
| Pub Quiz | 23 |
| Bowling | 12 |
| Boat Race | 8 |
+-----+--------------------+

---------------------------------------------------------

Tables

scoresTable
+-----+---------------+---------+-----------+
| id | competitorId |eventId |eventScore |
+-----+---------------+---------+-----------+
| 1 | 1 | 1 | 23 |
| 2 | 1 | 2 | 12 |
| 3 | 1 | 3 | 8 |
| 4 | 2 | 1 | 19 |
| 5 | 2 | 2 | 15 |
+-----+---------------+---------+-----------+


eventsTable
+-----+------------+--------------------------+
| id | eventName |eventDescription |
+-----+------------+--------------------------+
| 1 | Pub Quiz | Description of Pub Quiz |
| 2 | Bowling | Description of Bowling |
| 3 | Boat Race | Description of Boat Race |
+-----+------------+--------------------------+


competitorTable
+-----+-----------------+--------------------------------+
| id | competitorName |competitorDescription |
+-----+-----------------+--------------------------------+
| 1 | Microsoft | Microsoft company description |
| 2 | Apple | Apple company description |
+-----+-----------------+--------------------------------+


I have tried many different queries using JOIN and DISTINCT but cannot get the results i want. I can return all the scores for a certain competitor but cannot return this with just the competitorName and competitorDescription displayed once.

The results are always returned with competitorName and competitorDescription duplicated based on the number of rows returned from the scoresTable.


Any other help greatly appreciated. Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
3457
November 28, 2011 11:22AM
993
November 28, 2011 02:39PM
Re: Join without duplicates
1287
November 29, 2011 04:19AM
1485
November 29, 2011 02:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.