combining resultset from two columns into one...

[mkosmosports]

Hey everyone,

Ive got this query:
SELECT DISTINCT DATE_FORMAT(START_DATE,'%Y%m') AS date, TEAM_ID as tid,  OLDCLUB_ID as tid2 from sf_team_player WHERE OLDCLUB_ID IS NOT NULL ORDER BY START_DATE DESC

TEAM_ID as tid,  OLDCLUB_ID as tid2. I want to combine these two selections so they produce only one resultset. So basically select tid and tid2 would produce one resultset of those two without any duplicates.

Any ideas?

Thanks.

[artacus]

I can't really tell what you've got going on from your post. I don't see any GROUP BY and it looks like tid and tid2 are from the same row in the database. And I'm not sure why you'd want DISTINCT for date. Are there multiple entries for each team?

Anyhow you can combine two rows using CONCAT() eg. CONCAT(TEAM_ID, '-', OLDCLUB_ID) AS id. You can then sort or group by that combined field. You can combine result sets using UNION, but I don't think thats what you're trying to do here.

[mkosmosports]

True artacus, my initial post is a little confusing. Ive narrowed it down to this though. How can I take distinct values from two columns together instead of each column separately. I mean so:

SELECT DISTINCT TEAM_ID, OLDCLUB_ID from sf_team_player

This will get me distinct values from TEAM_ID and OLDCLUB_ID separately . The thing is I want distinct values from both of these columns at once.

Is that clearer? Any ideas?

Thanks.

[artacus]

Well I guess I don't understand why you would need DISTINCT for team_id, is it not the PK? So I'm a little unclear on your structure. Anyhow, it will probably be easier if you use GROUP BY instead of DISTINCT.

[fenway]

You almost never want DISTINCT... except maybe in a UNION.

[artacus]

What's your reasoning behind that fenway? I don't use distinct much outside of GROUP_CONCAT(), but I'm curious what would make you speak so strongly against it.

[fenway]

Because it's used inappropriately all the time... GROUP BY works on columns, DISTINCT works on entire rows of results.  They're not interchangable, except in boundary cases (like when you're only getting back one field).