I learned SQL through oracle and am wondering if maybe the syntax is just a little different. Anyway, I am trying to run a query to find all the rows that contain a certain blank field. I know that they exist and they are not null. The column is a varchar. I try a simle query,
It does not return an error but it does not return anything. Also, my join statemet will not work.
select row from table where row = '';
(apparently subqueries are not available until mysql 4.1). Once again no error just no results. I thought maybe it was an issue with whitespace but it is to my understanding that in mysql if the column is a varchar that it chops off trailing whitespice. This is really frustrating me. Any help would be much appreciated.
select table.row from table, table2 where table.row = table2.row;
Yes whitespaces are removed from the end of inputs in a varchar field.
Have you tried to search for where row IS NULL and see if that returns the results you expected
note that in MySQL these 2 queries have different results:
INSERT INTO your_table(single_field) VALUES('');
basically in MySQL the only thing that's null is "NULL".
INSERT INTO your_table(single_field) VALUES(NULL);
As for the second issue, I couldn't explain that without understanding more of your table structure (for some reason I doubt that row, table, and table2 are the real names) and what values they're comparing.
I figured out the first one. There was a carriage return.
Still have not figured out the join though.
select * from table where row = '\r';
this is what I am trying. There are two tables. One is web_desc and the other is machines2. web_desc has two rows, web_desc and groupname where machiens2 has several rows web_desc being one of them. There are several web_desc for every groupname and there are several machines for every web_desc. I am trying to get all the distinct web_desc for a certain groupname.
It seems kind of funny to me. I am not sure why. I guess maybe because I have not done much with joins but I think most of the ones I have done I am not outputting the same column that I am joining.
select distinct web_desc.web_desc from web_desc, machines2 where web_desc.web_desc = machines2.web_desc and web_desc.groupname = 'somegroup';
This is how I would do a subquery
It is not critical to get this working because I have used php to get the results I want. It would make the code a bit simpler though.
select web_desc from web_desc where groupname = 'somegroup' and web_desc in (select distinct web_desc from machines2);
Here is it all in action:
for some reason, i don't think you need to join the tables, at least for this part, based on how you described it.
I'm sure you already know this, but,
will give you all of the web_desc for a specific groupname. you should be able to do this also
select web_desc from web_desc where groupname = 'somegroup'
should give you all of the machines2 data for the particular groupnames.
select m.* from machines2 m, web_desc w WHERE w.groupname='somegroup' and m.web_desc = w.web_desc
I found out what it was. It was the carraige return again.
Thanks for your help.
select distinct web_desc.web_desc from web_desc, machines2 where
groupname = 'automatics' and web_desc.web_desc = machines2.web_desc & '\r'
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)