I don't know your table content but if you join two tables eg : table1(id, desc) and table2(id, refid, desc) wich containts:
table1:
1 test1
2 test2
table2: 1 1 testbla 2 1 testbla
and you select: select id from table1 where table1.id = table2.refid
you got two rows (because if you show * it would be:
1 test1 1 1 testbla
and
1 test1 2 1 testbla
So distinct is the keyword for getting only the unique records. Distinct is in MYSQL very effective (see the user manual for : How MySQL Optimizes `DISTINCT').
regards
At 15:02 11-3-04, you wrote:
Thanks, but this is just a nicer way to apply my second solution, I'm looking for a more efficient solution (and if someone can than for an explanation - why are the results getting duplicated?)
-Amir. On Thursday 11 March 2004 15:40, Hans van Dalen wrote: > SELECT distinct main.id, etc. etc. > > At 14:37 11-3-04, you wrote: > >I have 3 tables: > > > >main(id int, type tinyint(1)) > >categories(id int, name varchar) > >items(id int, name varchar) > > > >I want to select the id and name. > >If type is 1 then I want to select the name from categories, > >if type is 0 I want to select the name from items, here is the query I'm > >trying to use: > > > >SELECT main.id, IF(main.type,categories.name,items.name), > >IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > >IF(main.type,categories.id,items.id)=main.id; > > > >This query gives me each row couple of times, can anyone tell me why? or > > can any one give me a better solution? > > > >My solution which I guess is not good is adding GROUP BY: > > > >SELECT main.id, IF(main.type,categories.name,items.name), > >IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > >IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; > > > >Thanks, > > -Amir. > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
