0

On my production server, this query returns no results, so I took it to SQLFiddle to test the results with test data to see if maybe it was a conversion issue. Well SQLFiddle throws an error anytime I attempt to run this query which leads me to believe that the issue is with my syntax.

Using the subset sample data listed below, I want to return userID & Name from my table named blue where the users title is like '%Manager%' (I have also tried to use is ILIKE '%Manager%')This is sample DDL that returns no results but if you look at my sample data you can see that I should have these two results returned:

1 Bob
3 Mike

However, my query returns 0 results. What do I need to edit in order for my query to return relevant results?

CREATE TABLE blue(
  userID INT PRIMARY KEY     NOT NULL, 
  name text NOT NULL   
);

CREATE TABLE RED(
  userID INT PRIMARY KEY NOT NULL
  ,title text NOT NULL
 );

 INSERT INTO blue (userID, name) VALUES
 (1, 'Bob'), (2, 'Joe'), (3, 'Mike'), (4, 'Sam'), (5, 'Bridge');
 INSERT INTO RED (userID, title) VALUES
 (1, 'Manager'), (2, 'Field Operator'), (3, 'Manager II'), (4, 'CFO'), (5, 'New Hire');

And this is the query I tried to run

Select *
FROM blue
where userID IN (SELECT userID 
                 FROM RED
                 WHERE title ILIKE '%Manager%')

This works fine on SQL Server, but it will not work on my PostgreSQL install (which is where my database resides). I was getting the "Something went wrong" error.

  • Does it help if you give your table aliases? Maybe the two similar attribute names confuse the engine – HoneyBadger Sep 8 '16 at 21:22
  • 2
    This works for me. sqlfiddle.com/#!15/67b85/1/0 . What error are you getting? – jjanes Sep 8 '16 at 22:59
  • 1
    This works just fine. There must be something you are not telling us. – a_horse_with_no_name Sep 9 '16 at 19:09
  • 1
    Perhaps it's the missing statement terminator that's causing the issue? (;) "Something went wrong" does not look like a Postgres error message anyway. – ypercubeᵀᴹ Sep 10 '16 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.