Select distinct

[stone28]

HI all,

I have a simple query:
SELECT tableA.fieldA, tableA.fieldB, tableB.fieldA FROM tableA LEFT JOIN tableB ON tableA.fieldID = tableB.fieldID;

So now if there are two entries in tableB for any entry in tableA I will have two results for that field, but I just want to have one (the first one or doesn't really matter) I just want to know if there is something in tableB for evetry record in tableA but don't want to know the entry.... I just want to check if there is something for each record. How can I achieve that?

Thanks,

 

[spaddhu]

group by in design view and change the group mode as first or last for tableB.fieldA

 

[stone28]

I can't really follow that....
Is it something like that:

SELECT tableA.fieldA, tableA.fieldB, tableB.fieldA FROM tableA LEFT JOIN tableB ON tableA.fieldID = tableB.fieldID GROUPE BY tableB.fieldID;
???

 

[spaddhu]

SELECT tableA.fieldA, tableA.fieldB, first(tableB.fieldA) as expr1 FROM tableA LEFT JOIN tableB ON tableA.fieldID = tableB.fieldID group by tableA.fieldA, tableA.fieldB;

This should do it..

 

[stone28]

Nice, You are a star!
Thank you.

 

[spaddhu]

Thnx for the praise. But I don't deserve it now...
Coz ppl like you bring out the star in me..

 

[stone28]

Bad news now.....

Tested in Access works - but it doesn't for mySQL ;-( that is what I need it for... Just using Access to test queries. hmm.... doesn't like "first" I think...

 

[spaddhu]

Did you try max() instead of first() ??

 

[stone28]

no success...... ;-(

 

[spaddhu]

I'll have to admit, that this star's shine is not on mySQL...
However, I don't understand why max does not work...
May I know what error is actually thrown??

 

[stone28]

Maybe it's me ;-)

SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb, MAX( tbl_users_comments.comment_id ) AS [ 'users comments']
FROM tbl_movies
LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id
GROUP BY tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb
ORDER BY tbl_movies.movie_title

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['users comments'] FROM tbl_movies LEFT JOIN tbl_users_comments ON tbl_movies.mo' at line 1

 

[mihabaki]

Maybe it's me ;-)

SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb, MAX( tbl_users_comments.comment_id ) AS [ 'users comments']
FROM tbl_movies
LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id
GROUP BY tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb
ORDER BY tbl_movies.movie_title

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['users comments'] FROM tbl_movies LEFT JOIN tbl_users_comments ON tbl_movies.mo' at line 1

instead of ['users comments'] try 'users comments'

 

[stone28]

Tried that already ;-(

 

[stone28]

Not sure what was wrong. Check it again and MAX works!!!! Thanks again - and count works as well...

 

[OddProject]

Can you help me now?

Selecting all excluding Nulls.

Please I have tried everything

 

[stone28]

Of course I can ;-)

you just have to add at the end:

HAVING (((Count(tableB.fieldA))>0))

so in my case it's

SELECT tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb, Count(tbl_users_comments.movie_id) AS ['users comments']
FROM tbl_movies LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id
GROUP BY tbl_movies.movie_id, tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb
HAVING (((Count(tbl_users_comments.movie_id))>0))
ORDER BY tbl_movies.movie_title;

 

[mihabaki]

Tried that already ;-(

try using instead of character " ' " character " ` "

 

[stone28]

I think the problem was the "first" clouse. After changing it to "count" or "max" it works straight away... It must be that that version of mySQL doesn't support that function.