Query help across 3 tables.

[cl8855]

Alerts Table:
alertId, col1, col2, col3

Msgs Table:
alertId, MsgId

Comments Table:
Msgid, col1, col2, col3, col4


Is there a way to build just one query that grabs
all columns/records from the alerts table for "col1 > 1", AND
all columns from the comments table for that record's corresponding AlertId/MsgId from the Msgs table?

Right now I am looping through the query of the alerts table and querying the comments table, but that is too slow.

 

[stefanwagner]

SELECT a.*, c.*
FROM alert a,
message m,
comment c
WHERE a.alertid = m.alertid
AND m.msgid = c.msgId
AND a.col1 > 1;

?

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[cl8855]

OK, cool I think that will work.

How about I make it harder then!

I want to get a.* as long as a.col1 > 1, but no matter whether (a.alertid = or <> m.alertid, m.msgid = or <> c.msgId)

Of course if the others are correct then I want to get c.*

 

[stefanwagner]

If you don't want a cartesian product of a.* c.*, you can use UNION.

SELECT a.*, c.*
FROM alert a,
  message m,
  comment c
WHERE a.alertid = m.alertid
  AND m.msgid = c.msgId
  AND a.col1 != 1
UNION 
SELECT a.*, "foo", "foo", "foo"
FROM alert a
WHERE a.col1 > 1;

Is this a game?

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[cl8855]

Hmm I don't think that works, I thought Union needed same data types on the columns.

What is the "foo" stuff you are adding? Trying to get the columns to match in the union?

 

[stefanwagner]

String or varchar(3) or ...
If it should be a number, use 0, 0, 0 or 999, 999, 999

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung