1

I would write the query (for a mysql relational DB) like this:

select email, IP, PCname, Type, OwnerName
from comps, owners
where OwnerName=*puthtmlInputTextValueHere*
  and IP=*IPhtmlInputTextValueHere*
  or Type=*typehtmlInputTextValueHere*
  or pcname=*pcnameHtmlInputTextValueHere*

The requirement for query: Everything should be related by keys only, not by names or text values. How to do that? What does that mean?

The example I am given:

select o.email, c.pcname, c.ip, c.type
from owners o, comps c
where
  (c.ownername=o.key)
  and ( (c.ip=:1) or (c.type=:1) or (c.pcname=:1))

=:1 ---what is that part? So, what must be after the where part here?

Another example:

select pcname
from comps c, laps l, gen g
where (g.key=:1) and (l.key=g.LapKey) and (c.LapKey=l.key)

what is g.key=:1? what is l.key?

Normally, I use condition like WHERE columnName=someValue but the requirement says not to use values, how to get a result then?!

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
CodeGust
  • 831
  • 3
  • 15
  • 36
  • Where did you get this answer? – PM 77-1 Feb 13 '15 at 19:23
  • Probably [What does the equal colon =: mean in an update statement?](http://stackoverflow.com/questions/10393986/mysql-what-does-the-equal-colon-mean-in-an-update-statement) / [What does the colon sign “:” do in a SQL query?](http://stackoverflow.com/questions/2177978/what-does-the-colon-sign-do-in-a-sql-query) The `:1` is likely a placeholder for a parameter. It isn't used my MySQL alone. – Jonathan Lonowski Feb 13 '15 at 20:31
  • Those may be parameters for a prepared statement, such as those [in `PDO::prepare()`](http://php.net/manual/en/pdo.prepare.php), but we would need to see the surrounding code for context. – Michael Berkowski Feb 13 '15 at 20:36
  • Thank you!! In my java code I use prepared statements. pst = conn.prepareStatement("select Email from table0 where PCname=?") pst.setString(1, myValue); So I use ? sign instead of :1 -- is that correct? And what is l.key - can it be something other than a "key" column of "l" table? a relation of some kind? (...I ask as there's no such column as key in that table... as the example given to me says) – CodeGust Feb 13 '15 at 20:51

1 Answers1

1

The example queries you present are not pure SQL. They appear to be code such as would be consumed by a programming interface to initialize an object typically known as a "prepared statement". Such an object can then be used multiple times to perform the same or similar queries.

Prepared statements allow the query to be parameterized, so that you can plug different actual values into various places in the query. In such a context, the :1 would almost surely refer to the first such parameter.

As for l.key in the second example, consider the FROM clause:

from comps c, laps l, gen g

See how each table name is followed by a one-letter label? The labels are called "aliases", and as that name might lead you to suppose, they can be used elsewhere in the query in place of the corresponding table name. Aliases can be longer than that, but they are typically only a few characters long. (There's not much point to them if they are as long / complex as the table name itself.) Given that knowledge, consider the expression that confused you:

(l.key=g.LapKey)

That's simply a conditional expression that evaluates to true for rows of the join (as specified in the FROM clause) for which the key value contributed by table l (== laps) is the same as the LapKey value contributed by table g (== gen). And getting back to your first questions, that is an example of relating tables laps and gen via their (related) keys.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
  • Thank you! (l.key=g.LapKey) so both "key" and "LapKey" are existing columns of the corresponding tables, right? I ask as there's no such column as key in that "l" table... as the example given to me says. Can it (l.Key) be anything else but column? – CodeGust Feb 13 '15 at 20:57
  • I've found the answer: l.key is a primary key, like guid... e.g. l.guid=g.lapkey – CodeGust Feb 16 '15 at 12:18