DISTINCT Query Fails - simple.

nelsons

Am attempting to get this query just show me one of each numerical s_job.id. Right now this query is showing me the same records as duplicates . Tried to DISTINCT it, but somehow I am just not getting it.

Any help - much appreciated.

				
				$result = mysql_query("SELECT DISTINCT 
			    starterstable.s_jobid,
				con2.c_jobid,
				con2.c_id,
				con2.c_uname,
				con2.c_hours,
				con2.c_complete,
				con2.c_jobid,
				starterstable.s_initname,
				starterstable.s_clientname, 
				starterstable.s_jobtitle,
				starterstable.s_duedate,
				starterstable.s_duetime,
				starterstable.s_initname,
				starterstable.s_initname,
				starterstable.s_initname
				FROM starterstable, con2
				WHERE con2.c_uname = 'Nelson' 
				AND con2.c_jobid = starterstable.s_jobid");

Roger_Ramjet

Because the data in some of the other columns is distinct. DISTINCT will only eliminate rows if every column is identical. To do what you want you need a subquery to get distinct ids first.

nelsons

Any code examples or resources on how a subquery is done. Have never had the opportunity to do one yet, so this is uncharted territory.

Thanks

Roger_Ramjet

Originally posted by nelsons
Any code examples or resources on how a subquery is done. Have never had the opportunity to do one yet, so this is uncharted territory.

Thanks

Here is the manual link for subqueries . Take the time to study it all as this is new to you. Subqueries are version specific so the first thing you'll need to do is check your mysql version.

Subqueries are very powerfull and very usefull, which is why I have directed you to the manual instead of just writing this one for you. Once you have read the basics, have a go at one for this and we'll help you through it.

Hint: start with a simple select query that will return just the c_id s that you want from con2. This will then be the subquery for the full query that will return the other columns. I expect it will need to use GROUP BY c_jobid with MAX or MIN for the c_id depending on your criteria.