Help with a MySQL query

[lat9]

I've got the following SQL query:

SELECT pa.*, pad.*, p.products_model, po.products_options_name, pov.products_options_values_name 
FROM products AS p, products_options AS po, products_options_values AS pov, products_attributes AS pa LEFT JOIN products_attributes_download AS pad ON (pa.products_attributes_id = pad.products_attributes_id) 
WHERE pa.products_id = p.products_id
AND pa.options_id = po.products_options_id
AND po.language_id = 1
AND pa.options_values_id = pov.products_options_values_id
AND pov.language_id = 1 
ORDER BY pa.products_id ASC, pa.options_id ASC, pa.options_values_id ASC

What I'm trying to do is retrieve a single row for each attribute that contains the information from both the products_attributes and products_attributes_download tables (in addition to a couple of "defining" fields).

The query is working except that the products_attributes_id value is coming back as "NULL" unless there's a companion record in the products_attributes_download table, in which case it's properly returning the numerical attributes_id. Is there some MySQL "magic" that will cause the result to be returned with those index values, i.e. so the query works the same as when it's run via phpMyAdmin?

[mc12345678]

Try swapping your original sequence of fields to export: pa.* and pad.*. Both tables have products_attributes_id associated with them, it looks like the later replaces the former. Of course the "clear" answer is to include on the select only the fields that are desired to be returned, not everything...

[DrByte]

lat9,

A couple questions:

a) what exactly is the difference you're seeing between how phpMyAdmin returns the results and how "whatever else" returns them?
(cuz, unless something is intercepting and overriding exactly what the query is doing, the results should be the same ... if the query is the same. It seems suspicious that you're suggesting you're getting different results from different tools running the same query)

b) Your query is a left/inner join on multiple tables. By the very nature of such a query, NULL is returned when companion records don't exist.
Are you saying you want to "always have all attributes, regardless of whether a download exists, and if a download exists then ALSO return its info"?

Where are you using this query? Some context may help determine the ultimate solution.

[lat9]

@mc12345678, you're spot-on. I modified the query to select only the pa.products_attributes_id (so the pad version doesn't overwrite it) and now the products_attributes_id values aren't being nulled-out when there's no matching record in the products_attributes_download table.

@DrByte, the difference that I'm seeing (even with the previous all-fields select) between phpMyAdmin and my under-development database I/O handler is that phpMyAdmin always returns the non-NULL version of the products_attributes_id while the $db execution of that same sQL query returns NULL as the id value if no matching record is found in the joined table.

What I'm looking to return is a full products_attributes+products_attributes_download field-list, with the download table's fields set to NULL if not present.

Like I indicated, the query's working as I wanted now with the removal of the pad.products_attributes_id value as one of the selected fields. It is still a mystery to me why the values returned by phpMyAdmin and the mysqli interfaces are different.

[DrByte]

Like I indicated, the query's working as I wanted now with the removal of the pad.products_attributes_id value as one of the selected fields. It is still a mystery to me why the values returned by phpMyAdmin and the mysqli interfaces are different.

If phpMyAdmin is actually showing ALL fields for ALL tables (and NOT collapsing/overwriting the ones with duplicate field names) then that would explain the difference.
The way ZC is designed to bring back an associative array, which causes duplicates to overwrite one another could be the problem you were inadvertently running into.
If you don't want those collisions, simply name each field uniquely with an "as" clause. In most cases the "collision" is harmless, and intentionally not an issue.
But in the case of your desired output, reversing the order is probably the simplest and best approach.


The "as" clause I mention would be something like "select po.products_attributes_id, pa.products_attributes_id as pa_id ..." ... so that the second one is given a new associative-array entry (of pa_id in this example). Then you can access that unique field just like any other.

[lat9]

Ah, that explains it! Thanks, DrByte.

[mc12345678]

Besides that, a majority of ZC's sql statements using multiple tables are "straight" joins rather than left joins, so only records that are in both (all) tables are returned, or as one of the two original suggestions, just the field(s) in question are returned.

I am curious about the reversal of the two pa.* and pad.* (making them pad.* first and then pa.*) to see if/that the originally expected results are returned. Very interesting...

[carlwhat]

I've got the following SQL query:

SELECT pa.*, pad.*, p.products_model, po.products_options_name, pov.products_options_values_name 
FROM products AS p, products_options AS po, products_options_values AS pov, products_attributes AS pa LEFT JOIN products_attributes_download AS pad ON (pa.products_attributes_id = pad.products_attributes_id) 
WHERE pa.products_id = p.products_id
AND pa.options_id = po.products_options_id
AND po.language_id = 1
AND pa.options_values_id = pov.products_options_values_id
AND pov.language_id = 1 
ORDER BY pa.products_id ASC, pa.options_id ASC, pa.options_values_id ASC

i'm a little late to the party on this one. but the path you are traveling is fraught with danger....

i will not get into the specifics on what you are trying to accomplish, but the construction of your SQL statement is bound to cause you grief. whenever you use multiple tables in the FROM statement where the join characteristic is in the WHERE statement, you should NEVER combine that with another table using a LEFT JOIN, INNER JOIN or any other JOIN statement. you should stick to one style else you are bound to have problems. (there is a name for the difference in styles, but i can not remember/track it down.)

i would modify your SQL statement as below.

SELECT pa.*, pad.*, p.products_model, po.products_options_name, pov.products_options_values_name 
FROM products AS p
JOIN products_attributes AS pa on pa.products_id = p.products_id
JOIN products_options AS po on pa.options_id = po.products_options_id
JOIN products_options_values AS pov on pa.options_values_id = pov.products_options_values_id
LEFT JOIN products_attributes_download AS pad ON (pa.products_attributes_id = pad.products_attributes_id) 
WHERE 
po.language_id = 1
AND pov.language_id = 1 
ORDER BY pa.products_id ASC, pa.options_id ASC, pa.options_values_id ASC