Re: Difficult query and am kinda stuck how to continue can someone help?
You are totally right! Harald Fuchs [EMAIL PROTECTED] schreef in bericht news:[EMAIL PROTECTED] In article [EMAIL PROTECTED], Timon Berkowitz [EMAIL PROTECTED] writes: Hi there, Sorry for being a little bit confusing, but de criterias has to be AND. So it has to be like this: - all cds that matches the string 'test' AND - all pieces of software that matches the string 'test' This contradicts with your example. I think you mean OR: show all records of the join where either cd name OR software name is LIKE '%test%'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficult query and am kinda stuck how to continue can someone help?
I finally got it working like I wanted it :-) SELECT DISTINCT t1.softid, IF( t1.nameofcd LIKE '%test%' OR t2.nameofsoftware LIKE '%test%', t1.nameofcd, '') AS nameofcd, IF( t2.nameofsoftware LIKE '%test%', t2.nameofsoftware, '') AS nameofsoftware FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.softid = t2.softid WHERE t1.nameofcd LIKE '%test%' OR t2.nameofsoftware LIKE '%test%' ORDER BY t1.softid, t1.nameofcd, t2.nameofsoftware Thank you all for your valuable time! Kind regards, Timon Berkowitz The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficult query and am kinda stuck how to continue can someone help?
Hi there, I have tried that query it works, but not good enough When you add in Table2 another row with softid 1 and without the string 'test' in it like this : softid| nameofcd 1test cd 1 2utils cd 1 3test cd 2 4backup id| softid| nameofsoftware 1 1test software 2 1software 3 2software 4 2software 5 1software the query you used will give the following result softid nameofcd nameofsoftware 1 test cd 1 1 test cd 1 1 test cd 1test software 3 test cd 2 Thats incorrect because I want to see only - all cds that matches the string 'test' - all pieces of software that matches the string 'test' So the result has to be like this softid nameofcd nameofsoftware 1 test cd 1 1 test cd 1test software 3 test cd 2 I hope I have made myself a little bit more clear By the way, Thank you for the quick response! Kind regards, Timon Berkowitz The Netherlands
Re: Difficult query and am kinda stuck how to continue can someone help?
Hi there, Sorry for being a little bit confusing, but de criterias has to be AND. So it has to be like this: - all cds that matches the string 'test' AND - all pieces of software that matches the string 'test' Kind regards, Timon Berkowitz The Netherlands Peter Brawley [EMAIL PROTECTED] schreef in bericht news:[EMAIL PROTECTED] Timon, What is unclear to me is whether you wish to AND or OR your two criteria ... - all cds that matches the string 'test' - all pieces of software that matches the string 'test' so if your table2 had (6, 2, 'test'), where table1.nameofcd for softid=2 does not contain 'test', do you want it in your result? PB - Original Message - From: Timon Berkowitz To: [EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 12:34 PM Subject: Re: Difficult query and am kinda stuck how to continue can someone help? Hi there, I have tried that query it works, but not good enough When you add in Table2 another row with softid 1 and without the string 'test' in it like this : softid| nameofcd 1test cd 1 2utils cd 1 3test cd 2 4backup id| softid| nameofsoftware 1 1test software 2 1software 3 2software 4 2software 5 1software the query you used will give the following result softid nameofcd nameofsoftware 1 test cd 1 1 test cd 1 1 test cd 1test software 3 test cd 2 Thats incorrect because I want to see only - all cds that matches the string 'test' - all pieces of software that matches the string 'test' So the result has to be like this softid nameofcd nameofsoftware 1 test cd 1 1 test cd 1test software 3 test cd 2 I hope I have made myself a little bit more clear By the way, Thank you for the quick response! Kind regards, Timon Berkowitz The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficult query and am kinda stuck how to continue can someone help?
What I ment is this :-) Table1 consists out of the following fields and data +--++ | softid | nameofcd | +--++ | 1 | test cd 1 | | 2 | software cd 2 | | 3 | software cd 3 | | 4 | test cd 2 | +--++ Table2 consists out of the following fields and data ++++ | id| softid | nameofsoftware | ++++ | 1 | 1 | test software | | 2 | 1 | software 1| | 3 | 1 | software 2| | 4 | 2 | software 3| | 5 | 2 | software 4| ++++ The output I need is the following ++++ | softid | nameofcd | nameofsoftware | ++++ | 1 | test cd 1 | | | 1 | test cd 1 | test software | | 4 | test cd 2 | | ++++ So that will require the following criteria when using the string 'test': - Show all records where nameofcd which contain the string test AND - Show all records where nameofcd which contain the string test AND - Do not show any duplicates of the combination and nameofcd and nameofsoftware That means do no show the others, how obvious! As always, Kind regards, Timon Berkowitz The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difficult query and am kinda stuck how to continue can someone help?
Hi there, I have a hard question, don't know if its possible to solve my query. I have set up two tables like this Table1: softid (integer) (unique) nameofcd (string) softid| nameofcd 1test cd 1 2utils cd 1 3test cd 2 4backup Table2: id (integer) (unique) softid (integer) (refers to a record in table1) nameofsoftware (string) id| softid| nameofsoftware 1 1test software 2 1software 3 2software 4 2software Now what I want to find in a query is the following. I want to show all the records of table1.nameofcds which contain the string 'test' and I want to show all the records of table2.nameofsoftware which contain the string 'test' and I dont want duplicates in the generated table. So the table which has to be generated has to be like this softid nameofcd nameofsoftware 1 test cd 1 1 test cd 1test software 3 test cd 2 I want to know is this possible to make? And if so.. can someone make the query as well? Kind regards, Timon Berkowitz The Netherlands
