Making two queries into one (1 Viewer)

smig

Registered User.
Local time
Today, 12:42
Joined
Nov 25, 2009
Messages
2,209
*** Edit ***

I have two queries that I want to combine into one (I want to have one Query only - to delete the first one)

The first one will select all PPL with Cellular phones
Code:
SELECT PPL_Phones.PPLID
FROM PPL_Phones INNER JOIN PPL ON PPL_Phones.PPLID = PPL.PPLID
WHERE PPL_Phones.SugTelephoneID=2    ' --- SugTelephoneID=2 ---> Cellular

The second one will use the first one to select all PPL with no Cellular phone
Code:
SELECT PPL.PPLID, PPL.Phone
FROM PPL LEFT JOIN PPLWithCellularPhones ON PPL.PPLID = PPLWithCellularPhones.PPLID
WHERE PPLWithCellularPhones.PPLID Is Null

What I need is a list of PPL that have no Cellular phone.
The phones list of PPL are in PPL_Phones table
Also in the PPL table I have the main phone of each person (PPL.Phone)

Thanks
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,358
So, one query gives you a list of all people with phones and the other those without. If we combine the two, what result did we want to see? All people with and without phones?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:42
Joined
Feb 28, 2001
Messages
27,001
You COULD get the results with something like

Code:
SELECT PPL.PPLID, NZ( PPL.Phone, "" ) AS PPLPhone
FROM PPL LEFT JOIN PPLWithCellularPhones
ON PPL.PPLID = PPLWithCellularPhones.PPLID
WHERE IIF( PPLWithCellularPhones.PPLID Is Null, "Not Cellular", "Cellular" ) AS PhoneType
ORDER BY PhoneType,  PPLPhone ;

This might do the job, though I'll bet there are nuances here that we don't understand yet. For instance, the "SugTelephoneID=2" part?
 

smig

Registered User.
Local time
Today, 12:42
Joined
Nov 25, 2009
Messages
2,209
You COULD get the results with something like

Code:
SELECT PPL.PPLID, NZ( PPL.Phone, "" ) AS PPLPhone
FROM PPL LEFT JOIN PPLWithCellularPhones
ON PPL.PPLID = PPLWithCellularPhones.PPLID
WHERE IIF( PPLWithCellularPhones.PPLID Is Null, "Not Cellular", "Cellular" ) AS PhoneType
ORDER BY PhoneType,  PPLPhone ;

This might do the job, though I'll bet there are nuances here that we don't understand yet. For instance, the "SugTelephoneID=2" part?

Thanks
Sug = Type (In Hebrew :D )
SugTelephoneID=2 --> Cellular

I did some edits to the post.
Hope it's more clear now
 
Last edited:

smig

Registered User.
Local time
Today, 12:42
Joined
Nov 25, 2009
Messages
2,209
So, one query gives you a list of all people with phones and the other those without. If we combine the two, what result did we want to see? All people with and without phones?

Thanks for looking at this.
I did some edits to the post.
Hope it's more clear now

What I need are the ones with no Cellular
 
Last edited:

Users who are viewing this thread

Top Bottom