CODEWITHSUNDEEP
mysqlsqlunion
hamzah
hamzah

Reputation: 375

SQL Query help using UNION or nested queries

I am trying to execute a query but its not working out so well for me. I got these tables:

create table spelare(
   Spnr char(13),
   Snamn varchar (20),
   primary key (Spnr)
)engine=innodb;


create table speltillfalle(
   starttid varchar (15),
   Tnamn varchar (20),
   Spnr char (13),
   resultat char (10),
   primary key (starttid, Tnamn, Spnr),
   foreign key (Tnamn) references tavling(Tnamn),
   foreign key (Spnr) references spelare(Spnr)
)engine=innodb;

What i want to do is the match 'Spnr' column from the table "spelare" with the Spnr column from the table "speltillfalle". I did that using this query:

SELECT Spnr FROM spelare UNION SELECT Spnr FROM speltillfalle;

This gave me all the Spnr values that matched in both tables. What i would like to do is the same thing AND add another condition. IT should return the same thing but only if the value of "resultat" column in the table "speltillfalle" is 'Diskad' for the corresponding matched Spnr.

Upvotes: 0

Views: 84

Answers (6)

karan shah
karan shah

Reputation: 77

SELECT s.Spnr 
  FROM spelare as s 
 INNER 
  JOIN speltillfalle as sf 
    on s.Spnr=sf.Spnr 
 where sf.Spnr = 'Diskad';

As you have applied a join on both these table and it returns all matching records for column Spnr we will just need to add one more condition as shown above for fecting only those records whoes value is 'Diskad'.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Use Exists like this:

SELECT Spnr, Snamn 
FROM spelare 
WHERE EXISTS 
(
SELECT 1
FROM speltillfalle
WHERE resultat = 'Diskad'
AND Spnr = spelare.Spnr
);

Upvotes: 2

m c
m c

Reputation: 1104

While you are asking "the Spnr values that matched in both tables" and "the match 'Spnr' column from the table spelare with the Spnr column from the table speltillfalle" you do not need an UNION. In my book "matched" means they are present in both tables.

Spnr column on speltillfalle is defined as

foreign key (Spnr) references spelare(Spnr)

So, if speltillfalle.Spnr is not null (it could be because you didn't add any NOT NULL constraint) certainly does exist in the spelare table. In this case

SELECT DISTINCT Spnr FROM speltillfalle WHERE resultat = 'Diskad' AND Spnr IS NOT NULL

does the job: the Spnr values you get from speltillfalle certainly do exist in spelare table, due to the foreign key constraint.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I think you want join:

select sp.Spnr from spelare sp
join speltillfalle sf on sp.Spnr = sf.Spnr and sf.resultat = 'Diskad'
group by sp.Spnr

Upvotes: 0

Anns
Anns

Reputation: 21

if i understand your question correctly then the answer may be

"select a.spnr,b.spnr from spelare a,speltillfalle b where a.spnr=b.spnr and b.resultat='Diskad'"

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

Based on your description you simply add this as a WHERE-condition:

SELECT Spnr FROM spelare 
UNION 
SELECT Spnr FROM speltillfalle
WHERE resultat = 'Diskad';

Or you use a EXISTS:

SELECT Spnr FROM spelare AS s
WHERE EXISTS
 ( 
   SELECT * FROM speltillfalle AS sf
   WHERE s.Spnr = sf.Spnr
     AND resultat = 'Diskad'
 );

Or an IN:

SELECT Spnr FROM spelare AS s
WHERE Spnr IN
 ( 
   SELECT Spnr FROM speltillfalle AS sf
   WHERE resultat = 'Diskad'
 );

Both IN and EXSISTS are a more common solution than UNION:-)

Upvotes: 5

Related Questions