SQL query returning multiple rows

Question

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.

why you neet to join all three tables if you just want total_physical_memory ? — fthiella
– fthiella, Commented Dec 2, 2013 at 19:46
Do you have the id of the system you want to query? I don't see the reason for doing any JOINs — Explosion Pills
– Explosion Pills, Commented Dec 2, 2013 at 19:47
I need to use the device_id to get 'to the total_physical_memory' — chocksaway
– chocksaway, Commented Dec 3, 2013 at 0:18

willy · Accepted Answer · 2013-12-02 19:55:26Z

1

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.

answered Dec 2, 2013 at 19:55

willy

1,50011 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chocksaway Over a year ago

The background to answering this question is that I need to use the device_id to get to the total_physical_memory

Pradeep · Accepted Answer · 2013-12-02 20:01:23Z

0

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.

answered Dec 2, 2013 at 20:01

Pradeep

465 bronze badges

Comments

uvais · Accepted Answer · 2013-12-03 07:19:29Z

0

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>;

answered Dec 3, 2013 at 7:19

uvais

4162 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL query returning multiple rows

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related