CODEWITHSUNDEEP
sqlselectmysqlphpmyadmin
Chris Edwards
Chris Edwards

Reputation: 3602

How do i do this simple SQL SELECT statement?

I'm having trouble with what I think should be the simplest of SQL statements, however it is not working for me...

I have 4 tables: S, P, J and SPJ where SPJ is basically a table linking all the ID's together.

Now, the statement required is: "Get supplier numbers for suppliers who supply part P2 and part P4."

SELECT SNO FROM SPJ WHERE PNO = 'P2' AND PNO = 'P4'
- returns no results (which is wrong)

SELECT SNO FROM SPJ WHERE PNO = 'P2' UNION SELECT SNO FROM SPJ WHERE PNO = 'P4'
- returns the result, plus an extra which only supplies one of the two...

I've tried all manner of statements but can't figure it out, I know it's got to be simple, but I just can't see it...

Anybody got any ideas?

Cheers

Upvotes: 1

Views: 211

Answers (5)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

This will get you suppliers who provide both parts, as opposed to those who supply either/or.

select sno
    from spj
    where pno in ('P2','P4')
    group by sno
    having count(distinct pno) = 2

Upvotes: 5

yurib
yurib

Reputation: 8147

"SELECT SNO FROM SPJ WHERE PNO = 'P2' AND PNO = 'P4'"

obviously PNO can't be both P2 and p4 at the same time. it's difficult to answer your question without a knowing the db scheme, you should give a description of your tables.

Upvotes: 0

mat-mcloughlin
mat-mcloughlin

Reputation: 6702

Or as an alternative to @Sathya

SELECT SNO 
FROM SPJ 
WHERE PNO = 'P2'
OR PNO = 'P4'

Upvotes: 2

JonH
JonH

Reputation: 33143

Better to use OR

SELECT SNO FROM SPJ WHERE PNO = 'P2' OR PNO= 'P4'

Upvotes: 0

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

SELECT SNO FROM SPJ WHERE PNO in ('P2','P4')

Upvotes: 1

Related Questions