CODEWITHSUNDEEP
mysqlsqlforeign-keys
Pragya Dasgupta
Pragya Dasgupta

Reputation: 123

SQL Query: Single row has multiple foreign keys from another table

I have this structure DB

create table server(name varchar, dev varchar, qa varchar, prod varchar);

create table app(id varchar, teamname varchar);

insert into server values('trans', 't1','t2','t3');

insert into app values('t1', 'appdev');

insert into app values('t2', 'appqa');

insert into app values('t3', 'appprod');

What i need is to get the values of t1, t2 and t3 and the only variable i have with me is server.name. How do i get it. My initial query looks like this 

select(select app.teamname from app join server on app.id=server.dev)as devteam,
(select app.teamname from app join server on app.id=server.qa)as qateam,
(select app.teamname from app join server on app.id=server.prod)as prodteam from server where server.name='trans'

but it looks really long and childish. Is there a better way to get this?

Upvotes: 0

Views: 825

Answers (2)

donPablo
donPablo

Reputation: 1959

The varchar needs (9) or some length, else it defaults to 1. Temp table used for this example... comment out -- the drop the first time.

drop table #server;
drop table #app;

create table #server(name varchar(9), dev varchar(9), qa varchar(9), prod varchar(9));

create table #app(id varchar(9), teamname varchar(9));

insert into #server values('trans', 't1','t2','t3');

insert into #app values('t1', 'appdev');
insert into #app values('t2', 'appqa');
insert into #app values('t3', 'appprod');

select 
 ts.name
 ,tdev.teamname
 ,tqa.teamname
 ,tprod.teamname
from #server as ts

left join #app as tdev
on ts.dev = tdev.id

left join #app as tqa
on ts.qa = tqa.id

left join #app as tprod
on ts.prod = tprod.id

where ts.name = 'trans'

result:

name    teamname    teamname    teamname
trans   appdev      appqa       appprod

Upvotes: 1

rd_nielsen
rd_nielsen

Reputation: 2459

Another way--not necessarily better, but perhaps simpler, is:

select
    app1.teamname as devteam,
    app2.teamname as qateam,
    app3.teamname as prodteam
from
   server
   inner join app as app1 on app1.id = server.dev
   inner join app as app2 on app2.id = server.qa
   inner join app as app3 on app3.id = server.prod
where
   server_name = 'trans';

Upvotes: 1

Related Questions