I have one large table consisting of a number of foreign key columns.
The join I’ve used is like this:
$q = “SELECT column names
FROM bigtable AS big INNER JOIN
table1 AS T1 ON T1.some_id=big.some_id INNER JOIN
table2 AS T2 ON T2.another_id=big.another_id
…WHERE category = ‘$category’”;
In the above query I’m joining all of the tables to the “bigtable”, but now I’d like to also join two other tables in the same query. These two tables only join one another, the one table has a foreign key from the “bigtable” (if this is relevant).
I tried the following but it doesn’t work too well:
$q = “SELECT column names
FROM bigtable AS big INNER JOIN
table1 AS T1 ON T1.some_id=big.some_id INNER JOIN
table2 AS T2 ON T2.another_id=big.another_id INNER JOIN
other_table1 AS OT1 INNER JOIN
other_table2 AS OT2 ON OT2.other_id = OT1.other_id
…WHERE category = ‘$category’”;
“the one table has a foreign key from the “bigtable” (if this is relevant).”
SELECT column names
FROM bigtable AS big INNER JOIN
table1 AS T1 ON T1.some_id=big.some_id INNER JOIN
table2 AS T2 ON T2.another_id=big.another_id INNER JOIN
other_table1 AS OT1 ON OT1.somekey = big.someFK INNER JOIN
other_table2 AS OT2 ON OT2.other_id = OT1.other_id
thank you for your assistance. I think it works but I’ve noticed a problem.
“the one table has a foreign key from the “bigtable” (if this is relevant).”
This table with the foreign key is a “lookup” table (or whatever the term is), which means that the same foreign key can appear multiple times in the table. At one point my query loops out some data but it now loops out the same data multiple times. If the foreign key appears 5 times in the lookup table, the data will be looped out 5 times.
sorry for the confusion, this is what I’m trying to do:
$q = "SELECT column names
FROM hotels AS h INNER JOIN
rooms AS r ON r.hotel_id=h.hotel_id INNER JOIN
hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN
features AS f ON f.features_id = hf.features_id
...WHERE category = '$category'";
I need to loop out the rooms but because the hotel_id appears multiple times in the hotels_features table, the rooms will loop out multiple times.
it still doesn’t work, it seems that the hotel_id in the lookup table is definitely the problem. As a last resort, must I use another query?
$q = "SELECT column names
FROM hotels AS h INNER JOIN
rooms AS r ON r.hotel_id=h.hotel_id INNER JOIN
hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN
features AS f ON f.features_id = hf.features_id
...WHERE category = '$category'";
$r = @mysqli_query($dbc, $q);
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
// echo data here
$r2 = @mysqli_query ($dbc, $q);
while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {
// loop content here
}
// continuing echoing data here
yeah, i can see the problem now that i stop and think about the probable cardinalities – a hotel has multiple rooms, and a hotel also has multiple features
you will have to collapse one of the multiplicities in a subquery
if i knew the extent of the columns being returned (instead of “column names”) then i’d be able to recommend which of them would be easier
I’ve changed my query and am now looping out the hotels, together with the features of every hotel. There are plenty more columns in my hotel table but to simplify things I’ve only included the hotel name here. The problem now is that the same hotel is looping out however many times the hotel_id appears in the lookup table.
$q = “SELECT h.hotel_name, f.features_name
FROM hotels AS h INNER JOIN
hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN
features AS f ON f.features_id = hf.features_id”;
