bobthemac bobthemac - 1 month ago 4
MySQL Question

SQL Joining Diffrent Size Tables Together With Null Value Replacement

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.

SELECT *
FROM keg.beer
JOIN keg.userbeer
ON beer.id = userbeer.beer_id
WHERE userbeer.username_id = 1;


The second query is even simpler and is just a select getting the list of beers.

SELECT * FROM keg.beer


What I want to do is run a query and have it return a list of beers with a Boolean value if the user has tried it or not.

Answer

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 beer:

 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 beer table.

Now I need a drink.