CODEWITHSUNDEEP
mysql
Louis Carmichael
Louis Carmichael

Reputation: 21

SQL - Inner Join Syntax after the on statement

So in my database I have these records that have serial key number to separate them from each other. However I want to join them with another table, where a person can have up to 2 serial numbers associated to their account like so.

|account #|Serial#1|Serial#2|
------------------------------------------
|01234201 |P984564|P12388|
------------------------------------------

So my question is, can you set up an Inner Join query in the on statement to compare one column against two columns. eg.

INNER JOIN datatable2 ON datatable1.Serial#1 OR datatabel2.Serial#2 = datatable2.serial#

Upvotes: 1

Views: 70

Answers (2)

user2366842
user2366842

Reputation: 1216

Create a normalized View for the data and query against that instead. This will save you some headaches in the future and make your queries more readable. https://dev.mysql.com/doc/refman/8.0/en/create-view.html for more information on Views. The query associated with your view in particular would be something along the lines of this: 

  Select AccountNumber, Serial1 as Serial from datatable union select AccountNumber, Serial2 as Serial from datatable

I'm a little rusty at mySQL in particular, but that should give you a general gist. Then query against it like so:

  Select AccountNumber from myView where Serial = (whatever)

This is a good workaround if properly normalizing your data is too difficult because of it being tied into other systems/processes.

Upvotes: 1

Kevin B
Kevin B

Reputation: 36

Yes, it would be:

INNER JOIN datatable2 ON datatable1.Serial#1 = datatable2.serial# 
              OR datatable2.Serial#2 = datatable2.serial#

I would strongly encourage you to normalize your tables though, so then you don't need an OR condition in your WHERE.

Upvotes: 1

Related Questions