Combining 2 Mysql queries into one

Question

I currently running two queries to find one result, I have a good feeling I can combine these two queries into one. I've tried the method below but it's still showing me results that already appear from access_number table.

$query = "select * FROM `$table`.`access_number` WHERE `active`='1'"; 
$result = mysql_query($query,$db) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
  list($dd_inuse) = mysql_fetch_row(mysql_query("SELECT `did` FROM `$table`.`customer_dd` WHERE `did`='$row[did]' AND `callerid`='$callerid' LIMIT 1",$db));
  if(!$dd_inuse) {
     $goodone = $row['did'];
     break;
   }
}

I tried combining it like this and it's not showing me unique values

select `access_number`.`did` from `access_number` 
INNER JOIN `customer_dd` 
WHERE `customer_dd`.`callerid`='$callerid' 
AND `customer_dd`.`did` !=`access_number`.`did` LIMIT 1

Bottom line is, I'm trying to find a value in table access_number that does not exist in customer_dd

Any kind of help I can get on this is greatly appreciated!

You need to wrap a while in the results from the first results for the second results. — a coder
– a coder, Commented May 13, 2015 at 4:27
i was hoping to just use one query and not do the looping after the first fetch... — thevoipman
– thevoipman, Commented May 13, 2015 at 6:26

Abhik Chakraborty · Accepted Answer · 2015-05-13 06:44:16Z

1

You may use left join and not null for this

select 
a.access_number from access_number a
left join customer_dd c on c.did = a.did and c.callerid = '$callerid'
where c.did is null

answered May 13, 2015 at 6:44

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Leo Silence · Accepted Answer · 2015-05-13 04:39:42Z

0

your code:

SELECT `did` FROM `$table`.`customer_dd` 
WHERE `did`='$row[did]' AND `callerid` = '$callerid' LIMIT 1

base on you code, i found your table access_number.did = customer_dd.did.

so you can try:

select `access_number`.`did` from `access_number` 
INNER JOIN `customer_dd` ON `customer_dd`.`did` = `access_number`.`did`
WHERE `customer_dd`.`callerid` = '$callerid' AND `access_number`.`active`='1' LIMIT 1

answered May 13, 2015 at 4:39

Leo Silence

1,19011 silver badges22 bronze badges

1 Comment

thevoipman Over a year ago

this is the opposite of what I want... I want to find an access_number that is not in customer_dd

Collectives™ on Stack Overflow

Combining 2 Mysql queries into one

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related