MySQL LEFT JOIN Problem

[Muchard]

the situation is:

 

i have two tables, one ("p") with fields id(int) and active(bool), and one table ("a"), which works as generic n:m entity association table

 

second table fields:

type1 is an int value for a table, parameter1 is the int value of a record in that table, type2 is the int valued first table and parameter2 is a record in that first table - additionally there is a field value i'd like to catch

 

entries of the first table don't need to exist in the association table, therefore i want to use a left join with the first table providing its id field and setting active set to true - AND i need several restrictions applying to values of the second table for the left join in order to fetch valid results.

 

sql example for this:

SELECT p.id,a.value FROM gtable AS p LEFT JOIN associations AS a ON p.id = a.parameter2 WHERE a.type1 = 1 AND a.parameter1 = 40 AND a.type2 = 34 AND p.active = 1 AND p.language = 1 ORDER BY p.create_date DESC

 

so i have a left join, restrictions in where clause for both tables, though only first must provide results.

 

problem: it never returns any results.. where is my logic problem? guess, there is one. is it the way the declaration is processed?

 

another solution could be in dropping all where restrictions for the second table, but that wouldn't be of any use for me. i could also fix the association be adding a dedicated n:m table, but i'd like to avoid that.

 

i really appreciate an idea to this :-)

Edited September 13, 2010 by Muchard

[daredevil]

You mean to say your 2nd condition of and is not working correctly?

[Muchard]

You mean to say your 2nd condition of and is not working correctly?

this one, yes: "a.parameter1 = 40"

 

if i dont use it, it works, but i get more content and than i need to filter manually for that

[daredevil]

Are you joining the primary keys correctly? I am actually not able to figure out table structure from your post...

[Muchard]

Are you joining the primary keys correctly? I am actually not able to figure out table structure from your post...

 

tried to paint structure - problem could be, that the association table doesn't hold primary key information for this usage (unique index is the row (field combo) itself) - need this structure to export into various designated formats

 

 

forgot to say: use innodb as engine

Edited September 13, 2010 by Muchard

[CaldasGSM]

I would like to help.. but I cant understand the tables or the relations

can you please post the real names of the tables and columns.. or at least make up some names that are meaning full

ex: client(id,active) invoice(id,client_id,invoice_type)

that will be more helpful than a simple parameterx = 99

 

still..without understanding what you want specifically.. on generic tip i can give you is the ON clause supports more constaints

try to do

SELECT p.id,a.value 
FROM gtable AS p 
LEFT JOIN associations AS a ON p.id = a.parameter2 AND a.parameter1 = 40
WHERE a.type1 = 1  AND a.type2 = 34 AND p.active = 1 AND p.language = 1 ORDER BY p.create_date DESC

 

that changes the logic of the query sometimes producing different results..

[Muchard]

Thank you and i appreciate your help. Also tip is good, though it leads to same matter, but i will try to explain the structure itself a bit further, in order to show my problem:

 

the entities table works as a representation of all entities, which are implemented as tables (for example the user entity, which is an own table and a record in table entities (with id '1'), or the record subscriptions for example, which is record with id '34'). i did this to implement associations between the entities, but not in dedicated n:m-tables, but in the association tables. there each type (whether type1 or type2) is an id value of the entities table which shall be referenced. So i can connect two entities with a specific id, where each parameter (whether parameter1 or parameter1) represents not the entity (=table) itself, but a record in it (user 40 with subscription nr. 13). this is very helpful in decreasing development efforts for binding new associations (i have a simple class which allows connecting / referencing entities by type, parameter or even an associated value). generally, type1|parameter1|type2|parameter2 is a unique combination. This also works good when exported into "real" connection tables.

 

In the problem described here, i want to know on the left side:

each active subscription available

on the right side:

which subscriptions were connected to user 40 yet

 

So i would be able to display the first entry which is available and active, but not associated with the user (user is supplied with null). In order to not filter out entries manually, which do not apply to that user, i want to use LEFT JOIN, but as described, it doesn't work, when i provide parameter1 = 40 as a filter. If i don't, it works, but that doesn't bring up any good :/