Support MySQL data integrity?

[blackhead]

Hi

Should you help me please?

I have one problem with select query. I made three tables as shown below. I need to do something like nested select. But it is not supported. How should I maintain the gain from database build with respect to data integrity?

A
--------------------
id int (pk)
name char

B
----------------------
id int (pk)
name char

C
--------------------
id int (pk)
a int ref a
b int ref b


The query I'd like to use is:

SELECT * FROM a WHERE NOT id IN ( SELECT a FROM c WHERE b = x )

Where x means value perviously selected from table B.

I'm unable to write the query which should solve this. Should you help me PLEASE?

Than you very much
seeya

George Blackhead

 

[petersJazz]

The statement "SELECT * FROM a WHERE NOT id IN ( SELECT a FROM c WHERE b = x )" dont make sense, can you explain in english what you want. With example like this:

table A
id name
1 peter
2 john
3 philip

table B
id name
1 dylan
2 clinton

table C
id refA refB
1 1 1
2 2 2

 

[rycamor]

I notice you want to do a subquery, and that you seem to want foreign key referential integrity with your C table having columns referring to A and B.

If you want data integrity with full foreign key constraints, and subqueries, MySQL is not the database for you. Try PostgreSQL, Sybase, etc...

Yes, MySQL does have some beginning support for foreign keys, but subqueries are not even in beta yet.

If you really have to use MySQL, I suppose it would be possible to do this with some very complicated JOIN query, or by selecting into a temporary table, and then querying the temp table, etc... But the easiest to do it would be by nesting queries inside your programming environment, making your subquery first, building a set of values, and then Selecting from your main query using the set of values as a filter. Either way, it's a pretty kludgy workaround. -------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[blackhead]

Rycamor: Thank you man... But It is impossible to do it with JOIN! I spend a lot of time with JOIN without ANY success... But the TEMP tables should do it right! Thank you.

Peters Jazz:

Well...
The "x " in my query should be 1 for Dylan from table B. So I want to get 2 & 3 (John & Philip ) from table A. Becuase of table C has no row with "refB"=1 for these "refsA".

Does it make a sense now???
Thank you men for your messages..