Join two tables and using computed columns

Question

I have two simple tables in a database. The schema looks like this:

I have created an SQL-Fiddle with some example-data.
Link: http://sqlfiddle.com/#!9/c5e87c/2

At first I want to simple query for all Printers that should get connected to a Computer.
I select the table computermapping and query for a ComputerGUID like this:

SELECT PrinterGUID
FROM computermapping
WHERE ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'

This is OK. At second, I want to join the table "computerdefaultprinter".
I use a LEFT JOIN on the ComputerGUID like this:

SELECT computermapping.PrinterGUID
FROM computermapping
LEFT JOIN computerdefaultprinter ON computerdefaultprinter.ComputerGUID = computermapping.ComputerGUID
WHERE computermapping.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'

Also OK.
Finally I want to know, which Printer will be the Default-Printer. A Computer can only have one Default-Printer. Using my sample data in the SQL-Fiddle I want the output to be like this:

PrinterGUID                          | isDefaultPrinter  
--------------------------------------------------------  
5549f63f-e02f-4685-a976-96b50c299bed | 1
957b7233-e590-4e7d-aed6-aee0573fc3a8 | 0
5106f1f7-068f-463f-9b76-7cc0ba017184 | 0

I used something in the past like:

SELECT computermapping.PrinterGUID, ( computerdefaultprinter.PrinterGUID IS NOT NULL) as isDefaultPrinter
...

But I can't get it to work anymore. I haven't used SQL for some years now. Currently I am using MySQL, if this is important.

Could you please help me to solve this?

Thank you.

PS: I had problems to find an adequate title for my request. I don't know if using "computed columns" mentioned in the title is correct.

user_10498828 · Answer

You are missing the condition that relates the PrinterGUIDs of the 2 tables in the ON clause:

SELECT m.PrinterGUID,
       p.PrinterGUID IS NOT NULL AS isDefaultPrinter
FROM computermapping AS m LEFT JOIN computerdefaultprinter AS p
ON p.ComputerGUID = m.ComputerGUID AND p.PrinterGUID = m.PrinterGUID
WHERE m.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'

See the demo.
Results:

> PrinterGUID                          | isDefaultPrinter
> :----------------------------------- | ---------------:
> 5549f63f-e02f-4685-a976-96b50c299bed |                1
> 957b7233-e590-4e7d-aed6-aee0573fc3a8 |                0
> 5106f1f7-068f-463f-9b76-7cc0ba017184 |                0

Join two tables and using computed columns

1 Answers