issues with where clause

[prophoto]

I am in the process of setting up a new list and it requires a number of joins from other existing lists. I keep getting errors when I put the where clause in the join. One of them is "unknown column #___tickets.id" when typing in the full jos_tickets.id instead of using {thistable}. I've tried {thistable} also with the same issue. Can you give me the exact syntax to use in this case for where and concat? Thanks!

CCB site, list 43.

 

[cheesegrits]

Which elements? I had a look at the elements, but there's a buttload of joins, I looked at 3 or 4 but don't see any with concat/where like you described.

-- hugh

 

[prophoto]

Most of them are turned off already to help diagnose, I also had removed some of the concats to figure it out. Eventually all of the joins will need concats and where. I will add the concats back in, lets start with 1033 and 1021.

 

[prophoto]

After doing some more reading in the forum I found a reference to dbjoins and checkboxes. I changed element #1033 to a dropdown and it works fine! Must be something wrong with the code for checkboxes or I need a different where clause since I am using checkboxes.

 

[cheesegrits]

Ah, yes. Checkbox joins automatically create a many-to-many junction table that stores the actual selections. A checkbox or multiselect join can obviously have multiple values, unlike a dropdown or radio, which can only have one. So it needs a junction table, as it's no longer a simple one-to-one, "single foreign key on main table points to single row in joined table" relationship.

If you look at your tables in phpMyAdmin, for checkbox join elements you'll see tables with names like tablename_repeat_elementname, with id, parent_id and elementname fields. If you save a form with multiple selections checked for that element, you'll then see multiple rows in the junction table, with parent_id all set the same (the rowid of the main table), and the 'elementname' having the actual selected values.

So ... when building WHERE clauses, you need a grasp of the table structure and how to write queries for this kind of junction mapping.

-- hugh

 

[prophoto]

The where clause I'm currently using doesn't have anything to do with the junction table, only the other lists/tables that I'm joining, which is what confuses me!

 

[prophoto]

Are these issues one and the same?

http://fabrikar.com/~fabrikar/forum...ge-in-wildcard-in-databasejoin-element.39661/

 

[cheesegrits]

I have no idea. I don't have the time, energy or equanimity to read any of Bauer's posts.

-- hugh

 

[prophoto]

Interesting. Any suggestions? I've tried this clause 10 ways to Sunday and can't get it to work.

 

[prophoto]

I think I got it! I'll post soon when I can confirm.

 

[cheesegrits]

If you want to catch me on Skype and show me what you are trying to do, I'll help.

Yes, lots of history with Bauer. Many years of butting heads. I appreciate his efforts in diagnosing issues, but he tends to get a little confrontational, and ... well, long winded. Sometimes I can deal with it, sometimes I can't. Right now, I'm too busy, and not in the mood to wade through one of his old threads.

-- hugh