Case when SQL (guess!)

[johnvai]

Dear friends:

I have the following case that I am requiring the convenient SQL to access the data.(It's been more than 3 days working)
I am using Sybase 5.5.5, I have tried many many SQL statements that sometimes run right, sometimes wrong (which is very weird!).

my sql statement is:

select ph.tim,u.name,pd.quan,p.descript,p.modifier,pp.portid,ps. printertype,ps.connectedtostation,ps.comport,pd.uniqueid,p.modifier
from products p,station_printer_port sp,product_port pp,printers ps, posdetail pd,posheader ph,userse u 
where ph.transact=pd.transact and ph.userid=u.id and pd.prodnum=p.prodnum and p.prodnum*=pp.prodnum  and pp.portid*=sp.portid and 
sp.printerid=ps.id and ps.connectedtostation=1 and pd.transact=135 order by pd.uniqueid asc

the database schema is found at:

http://server2.uploadit.org/files/johnvai-SQLschema.jpg

it may happen that some products do not have data in product_port table, which i would like to show their portid as null, even the remaining required fields as null if they don't exist.

Example
the result that I want to see is when the product does not have any entry in the Printer_port table I want the remaining fields to be null as shown in:

http://server2.uploadit.org/files/johnvai-SQLData.jpg

Thank you for your help.

 

[lupins46]

This forum is for MS_Access.
There is a separate forum for Sybase questions.

 

[lespaul]

it may happen that some products do not have data in product_port table, which i would like to show their portid as null, even the remaining required fields as null if they don't exist.

then you need a left or right join. You are currently creating a cartesian join by defining your select statement as:

FROM table1, table2, table3

check out Understanding SQL Joins for a better understanding of what you need to do.

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design