Two queries into one

[_fr99rf_]

Hi everyone, I'm having troubles with query from database. I want to select all from one table and select sum from second table in the same time. Is this possible?

Here is my script:

$conn = mysqli_connect('localhost', 'root', '', 'test');

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
}

$sql = "SELECT first_id, first_name FROM first";
$sql = "SELECT sum(total) as SumTotal FROM second";

$result = mysqli_query($conn, $sql);

while ($data = mysqli_fetch_array($result)) {
    echo '<tr>';
    echo '<th>'.$data['first_id'].'</th>';
    echo '<th>'.$data['first_name'].'</th>';
    echo '<th>'.$data['SumTotal'].'</th>';
    echo '</tr>';
}

mysqli_close($conn);

Thanks!

[Barand]

Is there a common key in both table to relate the records or do you want the same total to appear on every output row

[_fr99rf_]

Hi Barand, thanks for your quick reply. If I did understand correctly, there is foreign key with relation between.

Maybe my question is not set up correctly..

 

I want make query with "SELECT" to 2 different tables?

 

Thanks!

[_fr99rf_]

No I made that display sum total but from all city, I want to display from city that are in one common state.

 

For example:

 

You have table of cities that call table1, and you have table2 with group of another cities.

I wan to display total number of citizens form table1 and table2, but separated, not total sum from both.

 

I hope I did explain correctly...

$conn = mysqli_connect('localhost', 'root', '', 'test');

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
}

$sql = "SELECT * FROM table1, table2";
$result = mysqli_query($conn, $sql);

function numCitizens($conn) {
    $data = $conn->query("SELECT sum(citizens) as numCitizensFROM table2")->fetch_array();
    return $data["numCitizens"];
}
$sum = numCitizens($conn);

while ($data = mysqli_fetch_array($result)) {
    echo '<tr>';
    echo '<th>'.$data['table1_id'].'</th>';
    echo '<th>'.$data['table1_name'].'</th>';
    echo '<th>'.$sum.'</th>';
    echo '</tr>';
}

mysqli_close($conn);

Edited February 9, 2015 by _fr99rf_

[maxxd]

If I'm reading your question correctly, you're going to want to look into the GROUP BY clause. Something along the lines of

SELECT	 a.city_name
	,SUM(b.citizens) AS numCitizens
FROM table1 a
LEFT JOIN table2 b
	ON a.cityID = b.cityID
GROUP BY numCitizens

No guarantee this will work if you copy and paste it (I'm only on my second up of coffee this morning), but it should get you moving in the right direction. Also, note that this assumes a foreign key of cityID in both table1 and table2 which you use to make the join.

[_fr99rf_]

If I'm reading your question correctly, you're going to want to look into the GROUP BY clause. Something along the lines of

SELECT	 a.city_name
	,SUM(b.citizens) AS numCitizens
FROM table1 a
LEFT JOIN table2 b
	ON a.cityID = b.cityID
GROUP BY numCitizens

No guarantee this will work if you copy and paste it (I'm only on my second up of coffee this morning), but it should get you moving in the right direction. Also, note that this assumes a foreign key of cityID in both table1 and table2 which you use to make the join.

 

Well, thank you Max, I will give a try and let you know If I am on the right way... Thanks, and enjoy your second coffee   

[Barand]

You need to GROUP BY city_name with that query

[maxxd]

You need to GROUP BY city_name with that query

 

Thank you - you're absolutely right!