Originally posted by Mincer
What is your current/desired query?
SELECT outletname, port
WHERE outletseq NOT IN (
SELECT o.outletname, o.slot
FROM outlet o LEFT OUTER JOIN
phonepanel p ON p.outletseq = o.outletseq LEFT OUTER JOIN
datapanel d ON d.outletseq = o.outletseq
WHERE p.outletseq IS NULL AND
d.outletseq IS NULL );
It looks to me like you are trying to find something that isn't there. The subquery returns rows that are only in outlet, but the main query seems to looks for rows that are not in outlet. What is it you are looking for, exactly?
ok tabe outlet has outletname and a n of slots:
outletname Slot outletseq(pk)
------------- ------- -----------
outletA1 A 1
outletA1 B 2
outletA1 C 3
outletA1 D 4
outletA2 A 5
now, IF a outletslot is been used it can either be for a phone or data and thus the outletseq is sent to the data or phone table.
but if a outlet is not been used then it only exisit in the outlet table and other link exist.
The broblem is that my result must be a entire outlet (slot A-wotevr) in which no slots are been used.
Try running only the subquery without the WHERE clause and take a look at your results. The results you want will be a subset of the records returned. You can then weed out the records you don't want with the WHERE clause. If you are looking for the outlets not attached to any panel, then the WHERE clause you have already should do the trick. I might not be seeing your side of it. If this doesn't work try executing
mysqldump <yourdbname> outlet phonepanel datapanel > outletquery.sql
then attach the sql file so that I can set up those tables in my database and work from there.