[SOLVED] Show one result

[ctcmedia]

Crappy title

 

What I have is a database of say 791 results and I am trying to sort them out into alphabetical order now I have got the query working but there are duplicate results. What I mean by that is that it is a list of programmes so just say for a there will be about 7 results for one programme but different episodes. How would I only show one result for one programme?

 

Thanks

 

 

[fenway]

First you need to get a unique list of programmes... then join this back to the table of episodes.

[ctcmedia]

that would be excellent but I donot have control over the list it comes from the BBC and Parses it into the DB. So is there no other way ?

 

Thanks

[fenway]

That is the way -- and you already have everything you need.

[kickstart]

Hi

 

What is the structure of the database?

 

Do you want just a list of programs, or some other data with it (eg, a list of programs with a count of episodes for that program)?

 

All the best

 

Keith

[fenway]

That is the way -- and you already have everything you need.

To clarify, write a select query with a GROUP BY on the programme, then JOIN that back using a derived table to get whatever else you desire.

[ctcmedia]

Hi,

Yes it all gets parsed into one table incl all relevant data. I am trying to show one result for each programme but instead it shows all programmes including all episodes

 

its for an xml structure a to z

[kickstart]

Hi

 

This would get you the list of programmes:-

 

SELECT DISTINCT programme

FROM ProgrammeTable

 

You might want the episode count in which case:-

 

SELECT programme, COUNT(*)

FROM ProgrammeTable

GROUP BY programme

 

All the best

 

Keith

[ctcmedia]

I see that would work if I am wanting one programme but what it is

 

<programme>
    <AZ letter="A">
    <?php
    $queryA = mysql_query("SELECT * FROM programme WHERE title LIKE 'A%'");
    while($rowA=mysql_fetch_array($queryA, MYSQL_ASSOC)){
    ?>
    <title><?=$rowA['title']?></title>
    <description><?=$rowA['description']?></description>
    <pID><?=$rowA['pID']?></pID>
    <?php  } ?>
    </AZ>

</programme>

 

So on and so forth. Since I have this updating the db every hour I wont know what show to look for to put into distinct

[kickstart]

Hi

 

Distinct will bring you back unque rows, not a single row. So it will give you a list of the unique programmes.

 

SELECT DISTINCT whateveryourprogrammecolumnis

FROM ProgrammeTable

 

All the best

 

Keith

[ctcmedia]

Hi thanks I tried but it wont get me the full row. for instance I am using

 

SELECT DISTINCT title FROM programme WHERE title LIKE 'A%'

 

it works for just bringing back the title but I am needing one more item to get me to a result page which is pid now when I use the following

 

SELECT DISTINCT title, pid FROM programme WHERE title LIKE 'A%'

 

It does what it was before and list everything

 

any ideas?

 

 

 

 

[kickstart]

Hi

 

Is pid unique for a programme or for an episode? If for a programme then it should be fine. If it is for an episode then you need to specify which one you are interested in.

 

Eg, something like:-

 

SELECT title, MIN(pid) FROM programme WHERE title LIKE 'A%' GROUP BY title

 

If you want more than that then probably something like:-

 

SELECT a.*

FROM programme a

INNER JOIN (SELECT title, MIN(pid) AS MinPid FROM programme WHERE title LIKE 'A%' GROUP BY title) b

ON a.title = b.title AND a.pid = b.MinPid

 

All the best

 

Keith

[ctcmedia]

THANK YOU KEITH

 

this one done the trick

 

SELECT a.*

FROM programme a

INNER JOIN (SELECT title, MIN(pid) AS MinPid FROM programme WHERE title LIKE 'A%' GROUP BY title) b

ON a.title = b.title AND a.pid = b.MinPid

 

 

Sorry for being a pain my brain has been slow for a good few weeks

 

 

Thanks again

 

 

[kickstart]

Hi

 

No problem. For real use it is best to avoid using SELECT *. I just used it as a shortcut. In live use it can cause a lot more data to be sent around than required, and possibly cause issues should someone change the table layout in future.

 

All the best

 

Keith