Hi Daniel, I am still not sure what exactly you are trying to achieve.
If you specify the WHERE condition correctly (or ON or USING for the LEFT JOIN) then there is no duplicate entry. I believe what you are asking has more to do with the way how to present the data in the output. Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 14:[EMAIL PROTECTED] さんは書きました: > well i'd like to join tables without duplicates, currently i cannot do this > unless i select the category table in a second loop to list all the > categories for that particular row/record > > > Hi Daniel and Kim, > > > > Isn't > > > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > > > > the way its supposed to be ? > > > > You are using the LEFT JOIN syntax, but actually using it just as a > > JOIN. > > > > You could also use something like > > > > SELECT * FROM products p, product_cat_join,product_category pc WHERE > > p.productID=pcj.productID AND pc.catID=pcj.catID; > > > > if I remember that correctly. This does exactly the same as the LEFT > > JOIN above. > > > > A LEFT JOIN is normally used to find records which exits in one table, > > BUT NOT in the other table. > > > > So for example: > > > > SELECT * FROM products p LEFT JOIN product_cat_join pcj ON > > p.productID=pcj.productID LEFT JOIN product_category pc ON > > pc.catID=pcj.catID WHERE pc.catID IS NULL > > > > > > Anybody: Please correct me if I talk rubbish here ;-) > > > > > > Best regards > > > > Nils Valentin > > Tokyo/Japan > > > > > > 2003å¹´ 8月 13æ—¥ 水曜日 13:[EMAIL PROTECTED] > > > > ã•ã‚“ã¯æ›¸ãã¾ã—ãŸ: > >> he is getting the same problem as me as i explained > >> > >> i use joins left join and inner join > >> > >> SELECT * FROM products p LEFT JOIN product_cat_join pcj ON > >> p.productID=pcj.productID LEFT JOIN product_category pc ON > >> pc.catID=pcj.catID; > >> > >> products > >> productID > >> product > >> > >> product_cat_join > >> joinID > >> productID > >> catID > >> > >> product_category > >> catID > >> product_category > >> > >> so say there are 5 records of a product category against a product it > >> would result in > >> > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> product_name product_category > >> > >> instead of > >> > >> product_name product_category > >> product_category > >> product_category > >> product_category > >> product_category > >> > >> > Hi Kim, > >> > > >> > no offense, but are you actually using the JOIN syntax correctly ? > >> > > >> > Could you post your query please ? > >> > > >> > My guess is you are doing a JOIN without any set conditions. hat > >> > would give you the result as described (which is normal). You would > >> > use the WHERE clause or for a LEFT JOIN ...the ON clause to filter > >> > to correct data which you are requesting. > >> > > >> > Please have a look once more at the JOIN syntax: > >> > > >> > http://www.mysql.com/doc/en/JOIN.html > >> > > >> > Some examples: > >> > > >> > mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; > >> > mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; > >> > mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); > >> > > >> > Best regards > >> > > >> > Nils Valentin > >> > Tokyo/Japan > >> > > >> > > >> > 2003å¹´ 8月 13日 > >> > 水曜日 09:25ã€ÂKim Kohen > >> > > >> > ã•んã¯書ãÂÂã¾ã—ãŸ: > >> >> G'day all, > >> >> > >> >> I'm pretty new to MySQL and very new to v4. > >> >> > >> >> This is probably going to sound strange, but is it possible to > >> >> create a union using the logic of a join? I have 4 tables and I > >> >> need to join data from them, but I'd like to display them like a > >> >> Union. If I do a straight join, I get lots of multiple entries > >> >> thus: > >> >> > >> >> Page Ad_num status stories > >> >> 1 4312-T1308 Ad Complete 1-marchingband-A33 Placed 1 > >> >> 4312-T1308 Ad Complete 1-jobs-T33 Placed > >> >> 1 > >> >> 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed > >> >> 1 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 > >> >> 4312-T1308 Ad Complete 1-jobs-T33 > >> >> Corrected 1 4312-T1308 Ad Complete 1-car crash-T33 > >> >> Placed > >> >> > >> >> Where ad_num "1 4312-T1308 Ad Complete" is replicated to > >> >> accommodate all the Joined data from the 'stories' table > >> >> > >> >> What I'd like to see is something like this: > >> >> > >> >> 1 4312-T1308 Ad Complete > >> >> 1 1-marchingband-A33 Placed > >> >> 1 1-jobs-T33 Placed > >> >> 1 1-jobs-T33 Corrected > >> >> 1 1-illegalbuild-A32 Placed > >> >> 1 1-haldonstfesty-T33 Placed > >> >> 1 1-car crash-T33 Placed > >> >> > >> >> Is this sort of thing possible? > >> >> > >> >> cheers > >> >> > >> >> kim > >> > > >> > -- > >> > --- > >> > Valentin Nils > >> > Internet Technology > >> > > >> > E-Mail: [EMAIL PROTECTED] > >> > URL: http://www.knowd.co.jp > >> > Personal URL: http://www.knowd.co.jp/staff/nils > >> > > >> > > >> > -- > >> > MySQL General Mailing List > >> > For list archives: http://lists.mysql.com/mysql > >> > To unsubscribe: > >> > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > > --- > > Valentin Nils > > Internet Technology > > > > E-Mail: [EMAIL PROTECTED] > > URL: http://www.knowd.co.jp > > Personal URL: http://www.knowd.co.jp/staff/nils > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
