Help on MySQL Statements

[infinite_root]

Hi I had a problem, hope you can help me.

Here's my database info:

Database name: sms
Table1: Visa
Field: Visa (there are many types of visa entry such as V001, V002, etc.)

Table2: Vessel
Field1: VictorVsl
Field2: Onboard

Here's what I would like to do:

I would like my query to provide search results of "all personnel" with or without "visa", which were "onboard" on a vessel named "VictorVsl" to be listed on the list.

HOw can I do that?

I am using MySQL 4.0.20-1.

Thanks and more power.

[sde]

i don't understand what field your table/field your personell is in.

could you describe your database a little better? if it is field 1 and field2, then maybe:

select distinct Field2 from Vessel where Field1='VictorVsl';

that will work if field 2 is the names of peronnel, .. but i'm not sure i understand your db structure.

[veedee]

Is there any correlationship beeween table1 and table2 ?
___________________
free ebook | free ebook download

[Dagger-ACS]

I need help with the correct syntax::
Here is the db Structure:

Table Visitor
Visitor_ID <PK>
FirstName
LastName

Table Attendance
Attendance_ID
Visitor_ID <FK>
Date

I need to know the correct Select statement to return DISTINCT(CONCAT(FirstName, ',' LastName)) AS Name

Where it would return rows from the visitor table where they had no row in the attendance table for a given date. In other words I want to list any visitor who was not there on a specific date. I hope this is clear enough for you guys. My communication skillz are poor...

TIA

Dag

[teknomage1]

SELECT Visitor.FirstName Visitor.LastName FROM Visitor, Attendance WHERE Visitor.ID = Attendance.Visitor_ID AND Attendance.Date = 2004-10-12;

Would get all the visitors who DID attend on a given date. I'm not sure if you can negate that.

[technobard]

Well, you might try this approach:

First, find all of the distinct visitor_id values that did attend on a given date.
select distinct visitor_id from attendance
where date = '2004-10-12'

Then select what you want from the VISITOR table where the visitor_id is not in the first list.
select distinct .... from visitor
where visitor_id not in (select distinct visitor_id from attendance
where date = '2004-10-12')