Limit dbselect output

Questions and Help
1

Is it possible to adapt this {dbselect} function to limit the output to a single Buyer?

{note}{dbselect: SELECT Colourway, BookPage, Orders, Buyer FROM Colourways WHERE Orders = yes ORDER BY Buyer ASC LIMIT 900; space=02HdgZGm0jNamFkoQbvhd3; multiple=yes; menu=no; name=colours}{endnote}

Colourway Book-Page Buyer
{repeat: for row in colours} {=row.colourway} {=row.bookpage} {=row.buyer}{endrepeat}

2

Hi @tonyd.

You can toggle the multiple option off for the dbselect command like this:

{dbselect: SELECT Colourway, BookPage, Orders, Buyer FROM Colourways WHERE Orders = yes ORDER BY Buyer ASC LIMIT 900; space=02HdgZGm0jNamFkoQbvhd3; multiple=no; menu=no; name=colours}

Then you can remove the repeat and use the field values directly like this: {=colours.colourway}, {=colours.bookpage}, {=colours.buyer}.

Here's the full snippet after changing it:

{note}{dbselect: SELECT Colourway, BookPage, Orders, Buyer FROM Colourways WHERE Orders = yes ORDER BY Buyer ASC LIMIT 900; space=02HdgZGm0jNamFkoQbvhd3; multiple=no; menu=no; name=colours}{endnote}

Colourway Book-Page Buyer
{=colours.colourway} {=colours.bookpage} {=colours.buyer}

Please let me know if that helps.

3

Thanks but that Buyer has more than one order and I need to select which Buyer to retrieve

4

I may not have explained my initial requirement too well. The original snippet outputs all the colouways that are marked as Ordered. One buyer could have multiple orders. I would like to limit the output to only show a single buyer with however many orders they have so the initial input would need to be which buyer.

5

Hi @tonyd.

Thanks for clarifying the requirement. You can introduce an input in the note section for the buyer using SELECT DISTINCT, then use it as an input to the other query as follows:

{note}{dbselect: SELECT DISTINCT Buyer FROM Colourways WHERE orders=yes; space=02HdgZGm0jNamFkoQbvhd3; menu=yes}{dbselect: SELECT Colourway, BookPage, Orders, Buyer FROM Colourways WHERE Orders = yes AND buyer=@buyer ORDER BY Buyer ASC LIMIT 900; space=02HdgZGm0jNamFkoQbvhd3; multiple=yes; menu=no; name=colours}{endnote}

Colourway Book-Page Buyer
{repeat: for row in colours} {=row.colourway} {=row.bookpage} {=row.buyer}
{endrepeat}

Let me know if that helps you.

6

Thank you, that works exactly as I needed. I didn't know about the SELECT DISTINCT and I'm sure it will be useful in other snippets.