SQL query problem

jerricateh · #1 (**permalink**) 10-03-03, 06:47

hi, i need some help with an sql query. it should be a simple sql if i could used subqueries but mysql doesn't support subqueries.

i have 3 tables.

Outlet table: outletname, slot, outletseq(PK)
Phone table: phoneseq (PK), outletseq(FK)
Data table: dataseq (PK), outletseq(FK)

an outletname may be repeated with different slots. eg

outlet1, slotA, 1
outlet1, slotB, 2
outlet1, slotC, 3
outlet2, slotA, 4
outlet2, slotB, 5
.
.
.

i need my sql to retrieve the outletnames that do not exist in the other 2 tables as foreign keys.

if outlet1, slotB appears in either phone table or data tables, outlet1 should NOT be returned even if the other 2 slots of outlet1 do not exist in the other tables.

might help your understanding to look at this subquery i tried to use.

Select DISTINCT(o.outletname) from outlet o where o.outletname NOT IN (select o.outletname from outlet o, phonepanel p, datapanel d where p.outletseq=o.outletseq or o.outletseq=d.outletseq);

thanx

LKBrwn_DBA · #2 (**permalink**) 10-03-03, 12:40

You could try using MINUS:

Code:


Select DISTINCT(outletname) from outlet 
MINUS
Select DISTINCT(outletname) from phonepanel
MINUS
Select DISTINCT(outletname) from datapanel;

jerricateh · #3 (**permalink**) 10-03-03, 13:32

Quote:

Originally posted by LKBrwn_DBA
You could try using MINUS:

Code:


Select DISTINCT(outletname) from outlet 
MINUS
Select DISTINCT(outletname) from phonepanel
MINUS
Select DISTINCT(outletname) from datapanel;

outletname does not exist in datapanel table or phonepanel table. but i altered your suggestion and tried it out anyway and it seems mysql does not understand MINUS

thanx anyway

Internet Services

Web Development

Digital Marketing

Consumer Tech