I am working on a query for a datatable and I can't seem to get it to display how I want, I don't know if this is even possible in SQL What I am looking to do is get a query to respond with ideally an extra column of Boolean type.
Currently I can run two queries and they both work perfectly but I can't work out how to join them together bellow is the code from my first query what this does is return beers a user has tried this works fine and as expected and returns as expected.
ON beer.id = userbeer.beer_id
WHERE userbeer.username_id = 1;
SELECT * FROM keg.beer
You're not going to run into too many scenarios for "Desired Results" that can't be produced with plain 'ol SQL. In this case you'll use a CASE statement to determine if the person has tried a beer. You'll also want a LEFT OUTER JOIN so you don't drop records coming from your
beer table when your filtered
userid doesn't have a
userbeer record for that
SELECT beer.name, beer.id, beer.country, CASE WHEN userbeer.username_id IS NULL THEN 0 ELSE 1 END AS user_tried_beer_boolean FROM keg.beer LEFT OUTER JOIN keg.userbeer ON beer.id = userbeer.beer_id AND userbeer.username_id = 1;
As @SeanLange mentioned in the comments here, the restriction of the WHERE statement for the userid would cause records to be dropped that you want in your result set, so we move the restriction of
username_id = 1 to the
ON portion of the
LEFT OUTER JOIN so that the
userbeer table results are restricted to just that user before it's joined to the
Now I need a drink.