Report Builder and displaying labels

Hi Jerry,

When you need to pull reference data from other tables like this it requires the use of "joins". For this purpose, I'll suggest using a LEFT JOIN, which would look something like this:

SELECT b.title as 'Title',
b.genre as 'Genre',
bl.shelf_location as 'Shelf Location'
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
WHERE b.hidden = 0 OR b.hidden = 1

I've aliased the "books" table as "b" and the "books_location" table as "bl" and prepended these aliases to the column names (e.g. "b.title"); when you perform a join, it makes all columns from both tables available to the query. If the tables have any of the same column names the query won't know which you refer to, so it needs to be made explicit like this. If a column only exists in one table it can be left as-is, but to be safe I've done this for every column name. (It's also possible to use the full table name instead of an alias, but that can get cumbersome)

Here's a page with a good overview of different joins and how they work: https://www.guru99.com/joins.html (Note that an INNER join may also have worked here; they function similarly to LEFT joins but with some difference in how they return data)

The above should probably do the trick, but let me know if you run into any issues or have any further questions!

Thanks,

Hi Daniel,

That worked fine and I thought it would be a relatively simple matter to add the labels from a third table that gets it's values in a similar manner. (Ha...)

IE: The 'genre' field in the 'books' table gets it's values from the 'genre_names' field in the 'genre_categories' editor.

So far I tried a number of permutations based around code similar to this, but I keep getting MySql errors:

SELECT b.title as 'Title',
b.genre as 'Genre',
c.genre_categories as 'Genre Names'
bl.shelf_location as 'Shelf Location'
gc.genre_names as ‘Genre NamesA’
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
FROM `<?php echo $TABLE_PREFIX ?>books` c
LEFT JOIN `<?php echo $TABLE_PREFIX ?>genre_categories` c ON gc.genre_names = gc.num
WHERE b.hidden = 0 OR b.hidden = 1 OR c.hidden = 0 OR c.hidden

I had a look at the link you included and some other coding sites, but I keep getting more confused and I’m not sure where to go from here.

I could really use a bit more help...

Thanks,

Jerry

Hey Jerry,

It looks like you were most of the way there - the main issue is that a MySQL query only needs (and should only have) one "FROM" statement. You can simply continue to add more JOIN statements underneath, like this:

SELECT b.title as 'Title',
b.genre as 'Genre',
bl.shelf_location as 'Shelf Location'
gc.genre_names as 'Genre Names'
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
LEFT JOIN `<?php echo $TABLE_PREFIX ?>genre_categories` gc ON b.genre = gc.num
WHERE b.hidden = 0 OR b.hidden = 1

I also cleaned up a few of the column names and table aliases based on what I think is correct, but you'll need to confirm these still.

• gc.genre_names should be the column in the "genre_categories" table that contains the genre text label
• b.genre should be the column in the "books" table that contains the genre num

Let me know if this does the trick, or if you have any more questions!

Thanks,

Thanks Daniel,

I added a missed comma after 'bl.shelf_location as 'Shelf Location',' and removed the 'b.genre as 'Genre',' line since it's now superfluous, and all works as planned.

Here's my final working code:

SELECT b.title as 'Title',
bl.shelf_location as 'Shelf Location',
gc.genre_names as 'Genre Names'
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
LEFT JOIN `<?php echo $TABLE_PREFIX ?>genre_categories` gc ON b.genre = gc.num
WHERE b.hidden = 0 OR b.hidden = 1

Appreciate the help (as always).

Jerry Kornbluth