/    Sign up×
Community /Pin to ProfileBookmark

Conditional JOIN

Copy linkTweet thisAlerts:
Feb 26.2022

I’ve got a JOIN in my SQL which joined a location ID to a location row.

SELECT locationId FROM tableB b
JOIN tableA a ON a.locationId = b.locationId

However if locationb is not set I want set to to a default value

SELECT IF (b.locationId = 0, 2, a.location) FROM tableB
JOIN tableA a ON b.locationId = IF (g.venueId = 0, 2, g.venueId)

It’s messy but if my table B location ID is 0 I want to force in a value.

to post a comment
PHP

8 Comments(s) ↴

Copy linkTweet thisAlerts:
@ginerjmFeb 27.2022 — The syntax seems incorrect to me. Are you getting any error messages? Do you have error checking turned on in your test script? The lower part of your proposed solution is far from valid SQL
Copy linkTweet thisAlerts:
@NogDogFeb 27.2022 — This is one of those "what are you really trying to do?" situations. Trying to use the MySQL if() function in a join clause smells really odd to me, and has me thinking either the DB schema needs re-factoring, or... 🤷

Even if it might "work", it probably forces a full table scan on either or both tables, which never scales well. Also, it would seem that if there is more than one g.venueId (whatever g is?) that is 0, then you'll join on every such row if you have a 2 for b.locationId (which, btw, I suspect you want to actually be an alias assigned to the results of that if() in the select clause?).
Copy linkTweet thisAlerts:
@maxxwvFeb 27.2022 — OK, so looking at this it looks like there are a couple potential issues. First, you're not specifying which table's locationID you're technically selecting in the first query so you should be getting an error due to the ambiguity. Beyond that, are you actually looking for a value of '0' in the b.locationId field, or are you looking for a default for values that exist in table a but don't have a foreign key in table b? If the former, look at the MySQL conditional statements - IF, CASE, etc. If it's the latter you'll probably want to look at COALESCE.
Copy linkTweet thisAlerts:
@NogDogFeb 27.2022 — > @maxxwv#1642884 are you actually looking for a value of '0'

👍️

Yeah, that's another bad smell: if it's supposed to be a foreign key sort of thing, _maybe_ it would default to null if there is no relation, but zero is certainly not normal for a FK.
Copy linkTweet thisAlerts:
@sibertFeb 27.2022 — As always I miss the whole picture (DB fiddle), so my guess is a shot in the dark:

``<i>
</i>SELECT
CASE b.locationid
WHEN 0 THEN b.locationid
ELSE 'wrong' END
LEFT JOIN tableA a ON a.locationid = b.locationid<i>
</i>``
Copy linkTweet thisAlerts:
@ginerjmFeb 27.2022 — OK - so why are you doing a join in the latest posting? Simply query the b table by itself?
Copy linkTweet thisAlerts:
@sibertFeb 27.2022 — > @ginerjm#1642889 OK - so why are you doing a join in the latest posting? Simply query the b table by itself?

As I said, i have not the whole picture, but I suggest to move the condition out of the JOIN. The exact query depends on the tables and data which still is a secret :-)
Copy linkTweet thisAlerts:
@ginerjmFeb 27.2022 — To the OP:

If the location id is not on table b why worry about any default values? Any value that is supposed to be selected from table b will not be present so the default is a blank/null. That means that you need to be sure that your join is an 'outer join' to ensure that the record from table a that qualifies in your (vague) query is selected.
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.25,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...