Query

[rashmi_k28]

Hi,

The fileds of table is
Table A
node
cpu_util
cpu_speed
timestamp


Table B

node
timestamp
ping_status


How to select data from table A if ping_status is 1 in Table B.

[ss1289]

Assuming that node is your association.

SELECT * FROM TableA WHERE node IN (SELECT node FROM TableB WHERE ping_status = 1);

[NogDog]

I think using a join as I replied to the same question here (http://phpbuilder.com/board/showthread.php?t=10355151) would be more efficient, but I'm not positive. Any DB gurus know which would be better in terms of speed?

[ss1289]

I think using a join as I replied to the same question here (http://phpbuilder.com/board/showthread.php?t=10355151) would be more efficient, but I'm not positive. Any DB gurus know which would be better in terms of speed?
The query is so simple that I don't think it makes any difference. The problem with a JOIN is that it includes all of the fields from Table B where you only need the node value from Table B. With my example, you're only selecting one value from Table B. A JOIN would be good if you needed to pull values from both tables.

[legendx]

A join will be much more efficient since using the IN (subquery) method will execute the subquery for every row in TableA. Where as a join will only query once then spend time comparing the two tables for matching node fields and ping_status = 1.

SELECT TableA.*
FROM TableA INNER JOIN TableB
ON TableA.node = TableB.node
WHERE b.ping_status = 1

ss1289 is right that this query is pretty simple and if both tables are pretty small the difference would be almost unnoticeable.

rashmi_k28 try both methods and let us know the results :p

[mattyblah]

exists would be faster than in. a join might give you a different result set when you are comparing a one to many relationship.

SELECT * FROM TableA a WHERE exists (SELECT * FROM TableB b WHERE b.ping_status = 1 and a.node = b.node);