Help with left join on multiple fields

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

Help with left join on multiple fields

Postby getut » 20. April 2021 00:26

I need some help with Mariadb doing a left join on multiple fields. I have an info table with information about locations and it is linked to a vendor table. The telcom vendor table contains an id key field and all the vendors. The main table has info for multiple fields that look up to the vendor table (Data vendor, Voice vendor, Video vendor).

select s.storenum, s.address, s.city, s.state, s.zip, s.phone, s.county, t.telcom as phoneprovider, s.phoneacctnum, t.telcom as dataprovider, s.dataacctnum from StoreInfo s left join Telcom t on s.telcomphone = t.telcomkey and s.telcomdata = t.telcomkey

This works fine if I only look up a SINGLE telcom vendor, but when I try to lookup each one I get nothing back. How do I do this?

For example vendor table
1 Spectrum
2 AT&T
3 DirecTV
getut
 
Posts: 2
Joined: 20. April 2021 00:19
XAMPP version: 7.4.12
Operating System: Ubuntu 20.04

Re: Help with left join on multiple fields

Postby Altrea » 20. April 2021 01:45

You want to join multiple fields so you need to do multiple joins.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 10911
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Help with left join on multiple fields

Postby getut » 20. April 2021 02:09

I think I have 2 joins in there. But obviously it isnt working. I have tried every syntax for the multiple joins I need but nothing works.

Variants I have tried:

left join Telcom t on s.telcomphone = t.telcomkey and s.telcomdata = t.telcomkey <-- this is the one on there right now

left join Telcom t on s.telcomphone = t.telcomkey left join s.telcomdata = t.telcomkey

left join Telcom t on s.telcomphone = t.telcomkey and left join s.telcomdata = t.telcomkey

I also tried doing it creating 3 views of the telcom table, one for data, one for voice and one for video and then joining each one to the video, voice or data view. That one works... kind of but I'm getting data returned on some fields that should be null (i.e. no video at some locations but it still putting values in.
getut
 
Posts: 2
Joined: 20. April 2021 00:19
XAMPP version: 7.4.12
Operating System: Ubuntu 20.04

Re: Help with left join on multiple fields

Postby Altrea » 20. April 2021 07:16

No you haven't.
Here is an example about how the syntax should look like for self joins
https://stackoverflow.com/a/8988819
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 10911
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 9 guests