WHERE EXISTS problem

[PhoenixD]

I have a table with pennames in them, and this database is going to be used by multiple sites, but I only want to grab the pennameID and title from a specific site, but I don't want to grab the site value.

This is what I came up with:

SELECT pennameID, title,
FROM pen_names AS t1

WHERE EXISTS (
  SELECT t2.site
  FROM pen_names AS t2
  WHERE t2.site = 'prnewsy'
  OR t2.site = 'all'
}

It's not working, general syntax error at WHERE EXISTS

Any suggestions?

 

[gmmastros]

Couple problems...

You have a comma after the last Column in your select clause. Remove it.

Instead of having a closing parenthesis for the exists section, you have a closing curly bracket ( [!]}[/!] ). Change it to close parenthesis ( [!])[/!] ).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PhoenixD]

Heh, I feel foolish. The } wasn't in my program, not sure why it ended up there when I pasted, but it was the comma. Thanks

 

[ESquared]

Your query is NOT going to work the way you expect. You don't have the correlation name t1 in your subquery. This means that all you're doing is selecting ALL rows from pen_names, as long as there are any rows with the sites 'prnewsy' or 'all' in the table.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[PhoenixD]

Ok... well I have it working now, but as you said it probaly isn't getting the results I want. Could you give me an example of how it works?

I changed the code a bit, but I think it has the same effect - the one I don't want.

SELECT pennameID, title
FROM pen_names

WHERE EXISTS (
  SELECT site
  FROM pen_names WHERE site = 'prnewsy'
  OR site = 'all'
)

My only guess would be

SELECT t1.pennameID, t1.title,
FROM pen_names AS t1

WHERE EXISTS (
  SELECT t2.site, t2.pennameID
  FROM pen_names AS t2
  WHERE (t2.site = 'prnewsy'
  OR t2.site = 'all') AND t2.pennameID = t1.pennameID
}

-Kerry

 

[PhoenixD]

correction to last code:

SELECT t1.pennameID, t1.title
FROM pen_names AS t1

WHERE EXISTS (
  SELECT t2.site, t2.pennameID
  FROM pen_names AS t2
  WHERE (t2.site = 'prnewsy'
  OR t2.site = 'all') AND t2.pennameID = t1.pennameID
)

This brings up a result and would seem to be the right solution... but I'd like a second opinion.

 

[ESquared]

This works, but in my experience beginners ought to avoid correlated subqueries: eventually they get you in trouble.

Why not just do a join? That's what you're logically doing, so literally do it. Learning to use joins is a hugely important part of learning to be a SQL developer. And even if a correlated subquery works, learning to use derived tables is a key skill. It's usually very easy to switch to a correlated subquery from a derived table, but not so easy to go the other way around, and many time the derived table performs better (there are situations where it doesn't). Derived tables help teach set-based thinking, too, whereas the thinking for correlated subqueries is row-based, which is NOT what the database engine excels at.

Even though the execution plan for your query and for mine may be similar, I am not convinced that one can always rely on the query optimizer to choose the right plan. Once a query gets complicated enough it may go back to the worst case scenario for correlated subqueries—one query per row!

SELECT t1.pennameID, t1.title
FROM
   pen_names t1
   INNER JOIN (
      SELECT DISTINCT pennameID
      FROM pen_names
      WHERE site IN ('prnewsy', 'all')
   ) t2 on t1.pennameID = t2.pennameID

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]