How do I get this query to select rows with no repeats of one value

[cherold]

I'm trying to figure out how to avoid duplicate rows, or more accurately avoid rows that duplicate one particular value.



I have two tables, Discs and DiscContents.

DiscContents is made up of:
disk_contents_id
composition_id
disk_id

Discs has a DiscID that joins DiscContents at disk_id, and a slew of text fields.

Right now I'm using this query, which gives me, as I'll explain below, more rows than I want:

SELECT Discs.*, DiscContents.*
FROM DiscContents INNER JOIN Discs ON DiscContents.disc_id = Discs.DiscID;

The issue is that DiscContents.disk_id has records that use the same number, and what I need is a query that will allow me to only grab one record with each disk_id number. So if my query results in:

disk_contents_id	composition_id	disk_id	DiscID	miscellaneous_text_fields
1	7	2	2	this
2	4	2	2	that
3	10	2	2	the
4	17	9	9	other
5	3	9	9	thing

I would want to only see two rows, one for disk_id 2 and one for disk_id 27 (doesn't matter which rows).

How would I do that?

[June7]

disk_id 27 is not in the example data.

If data from other fields is not important then why reference them?

SELECT DISTINCT disk_id FROM tablename;

[JoeM]

Looks like you couldn't figure out if you wanted to spell it "disc" or "disk" (you have a lot of inconsistent back-and-forth in your post!).

You can do an Aggregate Query where you group on the DiscID fields and just take the First of all the other fields.

SELECT 
    First(DiscContents.disc_contents_id) AS FirstOfdisc_contents_id, 
    First(DiscContents.composition_id) AS FirstOfcomposition_id, 
    DiscContents.disc_id, 
    Discs.DiscID, 
    First(Discs.miscellaneous_text_fields) AS FirstOfmiscellaneous_text_fields
FROM DiscContents 
INNER JOIN Discs ON DiscContents.disc_id = Discs.DiscID
GROUP BY 
    DiscContents.disc_id, 
    Discs.DiscID;

Note that you CANNOT use "*" on Aggregate Queries, you need to list out every field you want to display.

[cherold]

Yeah, I used to always use disk but it seems most people use disc nowadays so I've been trying to alter the habit. It will take a while.

Thanks.

[JoeM]

I was just pointing that out in case you tried to use my SQL code "as-is" and found it didn't work because of spelling discrepancies.