CODEWITHSUNDEEP
mysqlsqljoin
chocksaway
chocksaway

Reputation: 900

SQL query returning multiple rows

I have three database tables:

system_profile 

id  manufacturer    model   name      total_physical_memory
12345   my_pc           5000    DGS       3219206144

device

id      version   date_created      device_id      instance 
2828493 0         2013-10-13 20:13:00   12345      q 
2828494 1         2013-10-13 xxxxx      12345      e
2828495 2         2013-10-13 aaaaa      12345          r    
2828496 3         2013-10-13 ccccc      12345      r 


hw_profile

id     manufacturer model   name      disk            device_id
12345  my_pc            5000      ABC 2 gb        12345

The hw_profile and system_profile tables are linked by primary key "id"

The hw_profile and device tables are linked by a device_id.

I am trying select to "get just the system_profile.total_physical_memory":

SELECT system_profile.total_physical_memory
FROM system_profile, hw_profile, device
WHERE hw_profile.id = system_profile.id
AND device.device_id = hw_profile.device_id

This query is not working (I am getting multiple results) back.

I need to return "just" system_profile.total_physical_memory (3219206144)

I have tried various left joins, exists - but no success.

A clear example would be great (plus some aspirin).

Thanks

Miles.

Upvotes: 1

Views: 1278

Answers (3)

uvais
uvais

Reputation: 416

try this:

 SELECT sp.total_physical_memory
 FROM system_profile sp 
 INNER JOIN  device d ON d.device_id = sp.id
 INNER JOIN hw_profile hp ON hp.device_id = d.device_id;

Note: if the data is related to each table the you need to join the table , otherwise you simply select the total_physical_memory for a particular id , like:

 SELECT total_physical_memory
 FROM system_profile where id = <some_id>;

Upvotes: 0

Pradeep
Pradeep

Reputation: 46

I think you don't need join if you are getting data from single table.

In case you want to apply some condition based on other tables, try this query : $query = "SELECT DISTINCT system_profile.total_physical_memory FROM system_profile JOIN hw_profile ON(system_profile.id = hw_profile.id) JOIN device ON(device.device_id = hw_profile.device_id)";

You can also use LEFT JOIN rather than JOIN in case your application need this.

Upvotes: 0

willy
willy

Reputation: 1490

There is no reason to join to tables whose data is not used in any way. Just do a SELECT total_physical_memory FROM system_profile.

Upvotes: 1

Related Questions